seperating rows

i have an excel worksheet that is formatted as such

(number) (date) (conpany name) (street address) (city) (state)
(agent) (agent's name) (agent's address)
(officer) (officer's name) (officer's address)
blank line
(number) (date) (conpany name) (street address) (city) (state)
(agent) (agent's name) (agent's address)
(officer) (officer's name) (officer's address)
blank line
and so on...

sometimes there are more than one agent or officer, none of the
information can be sorted accurately by column. i want to maintain the
data between the blank lines. it must stay together, however, i want to
be able to sort by cities. is there any way to make excel sort the data
between the blank lines, and pick out words that i want?

0
8/31/2005 6:48:51 PM
excel 39879 articles. 2 followers. Follow

2 Replies
596 Views

Similar Articles

[PageSpeed] 33

Since sort needs each group on 1-line, move the agent/officer data upto
their prior number line, sort, then move the data back to seperate lines.
I'd manipulate a copy of the sheet just-n-case.

<johnmclaren_99@yahoo.com> wrote in message
news:1125514131.243696.203310@f14g2000cwb.googlegroups.com...
> i have an excel worksheet that is formatted as such
>
> (number) (date) (conpany name) (street address) (city) (state)
> (agent) (agent's name) (agent's address)
> (officer) (officer's name) (officer's address)
> blank line
> (number) (date) (conpany name) (street address) (city) (state)
> (agent) (agent's name) (agent's address)
> (officer) (officer's name) (officer's address)
> blank line
> and so on...
>
> sometimes there are more than one agent or officer, none of the
> information can be sorted accurately by column. i want to maintain the
> data between the blank lines. it must stay together, however, i want to
> be able to sort by cities. is there any way to make excel sort the data
> between the blank lines, and pick out words that i want?
>


0
Jefgorbach (42)
8/31/2005 9:06:07 PM
Hi John,
Not clear if each item is on a row,  if several items are on a row
you have to be able to separate them by some means.

Have a look at  later portions of the following which are
involved with getting data into separate cels on the same row.
   http://www.mvps.org/dmcritchie/excel/snakecols.htm

If you really can't then you need to at least separate out the city
and then you assign cell in that all following rows same value
until a new city is found.

 ---
HTH,
David McRitchie, Microsoft MVP - Excel    [site changed  Nov. 2001]
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm

<johnmclaren_99@yahoo.com> wrote in message news:1125514131.243696.203310@f14g2000cwb.googlegroups.com...
> i have an excel worksheet that is formatted as such
>
> (number) (date) (conpany name) (street address) (city) (state)
> (agent) (agent's name) (agent's address)
> (officer) (officer's name) (officer's address)
> blank line
> (number) (date) (conpany name) (street address) (city) (state)
> (agent) (agent's name) (agent's address)
> (officer) (officer's name) (officer's address)
> blank line
> and so on...
>
> sometimes there are more than one agent or officer, none of the
> information can be sorted accurately by column. i want to maintain the
> data between the blank lines. it must stay together, however, i want to
> be able to sort by cities. is there any way to make excel sort the data
> between the blank lines, and pick out words that i want?
>


0
9/1/2005 12:45:44 AM
Reply:

Similar Artilces:

Blank cells, hide row
Hide i have a piece of code thanks to some newsgroup people that i have changed a little to suit my purpose. Basically i want it to look at ColumnB between rows 32 and 79 inclusive and if it fines blank cells in columnB hide the entire row. This is what i have and it gives me a compile error Loop without Do. I don't know if the rest will run because i can't get past the error. Your help is appreciated Murray Sub ActivateNextBlankDown() ApplicationScreenUpdating = False ActiveCell.Offset(32, 2).Select Do While IsEmpty(ActiveCell) If IsEmpty(ActiveCell) Then Se...

Help with seperating data?
Hello I was wondering if anyone knows a way of seperating chunks of data into specific lines. i.e: THIS: href="3353-12American Baby href="1721-6American Cheerleader href="10a8-12American City & County href="2928-24American Conservative href="1072-6American Cowboy href="6035-6American Handgunner href="3343-6American Heritage href="2498-4American Heritage of Invention & Technology BECOMES THIS: href="3353-12American Baby href="1721-6American Cheerleader href="10a8-12American City & County href="2928-24American Conse...

Appending a row to List box row source
I would like to append a row to a list box row source that uses a query as it's source. I can think of a way to do this using a union query - but would like to know if there are other options. If the RowSource is a query, then your only option is to add the record to the source of the query, either by adding it to the underlying table, or creating a UNION query that joins the new data to the existing data. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "JimP" <jpockmire@houston.rr.com> wrote in message news:464db013$0$4...

Delete rows based on a cell value in the row
I'd like to delete rows from a spreadsheet based on the value of a cell in a row. This is a very large spreadsheet (60,000+ lines). I want to delete rows based on a value (in this case all parts which are purchased- vs mfd) without destroying the spreadsheet structure. In this case I have several bills of material where I want to list all the assemblies, but not the purchased parts. It seems it should be possible, but I'm not having any luck with the filter method. Thanks, Dave sort?? or use a macro to delete, from the bottom up, if the cell contains your text -- Don Gu...

Need help copying to column to rows
I need help copying an entire column of information into multiple rows without doing so manually considering that I have over 4000 addresses that need to be arranged into rows. For example: Column A 1. Smith & Sons 2. 223 5th AVE, New York, NY 11234 3. Phone: (212) 758-0718 4. 5. Joe's Auto 6. 556 60th STREET, Brooklyn, NY 11220 7. Phone: (718) 745-0778 I need this to come out like this: ColumnA Column B Column C 1. Smith & Sons 223 5th AVE, New York, NY 11234 Phone: (212) 758-0718 2. Joe's Aut...

Formatting Pivot Table Item Row Totals
Hello, Is there a way to automatically have the item row totals appear in bold while the detail remains unbold? For example, if I have a Pivot Table that displays the following data: State City Total Tennessee Nashville 500 Chattanooga 300 Memphis 200 Knoxville 100 Tennessee Total 1,100 I would want the number 1,100 to appear in bold while the other four numbers (the detail) are not ...

Macro for deleting rows and serialising the remaing rows
Macro for deleting rows and serialising the remaing rows ==================================================== I have a spread sheet It's a ToDo List The structure is like this Column A Sl No Column B Task Column C Person Column D Completed To start with I keep entering the tasks in one stretch and take a print out. The first Column (A) will be a serialized. I wil be using this print out for few days. After two/three days, I would like to open the spread sheet and enter fresh tasks For all completed tasks I put x in Col D. What I currently require is a macro to 1.. R...

Masking the sent from field with a seperate domain?
We have Exchange 2k3 and AD setup. We use the pop3 connector to download email from various seperately hosted domains and route the email to the correct client. Our users have noticed that any email they send shows the recipient the local domain in the sent from field. We need to mask it to say that it come from a different domain... for example one of the domains that we use to download email from using the pop3 connector. Is this possible? Any help would be greatly appreciated. Jackie Jackie wrote: > We have Exchange 2k3 and AD setup. We use the pop3 connector to > down...

not enough rows
Excel is limited to 65,536 rows. What is the work around to make this limit higher? There is no workaround except using multiple sheets -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "mc" <anonymous@discussions.microsoft.com> wrote in message news:300bc01c46da1$617ed200$a301280a@phx.gbl... > Excel is limited to 65,536 rows. What is the work around > to make this limit higher? "mc" <anonymous@discussions.microsoft.com> wrote in message news:300bc01c46da1$617ed200$a3012...

create rows within an Excel cell
Can rows be used within a cell? No. You can create multiple lines in a cell by changing it's format to: Format | Cells and click on Alingment. Click in the box for wrap. Or, you can force a new line at a specific point by pressing Alt+Enter while you are typing in the cell. tj "Tony G" wrote: > Can rows be used within a cell? Hi ALT+ENTER -- Regards Frank Kabel Frankfurt, Germany "Tony G" <TonyG@discussions.microsoft.com> schrieb im Newsbeitrag news:DA13DF1C-F8C7-4260-8328-C47622E6B3B4@microsoft.com... > Can rows be used within a cell? * The for...

Seperators
Hi I hope that someone can help? I have been sent a work book where several columns have data seperations between words as follows: "UK Pounds" The data appears to have a square box in between the "UK" and the "Pounds" Please can you advise a quick way to change the format in all columns in the worksheet Saved from a previous post: Chip Pearson has a very nice addin that will help determine what that character(s) is: http://www.cpearson.com/excel/CellView.htm Since you do see a box, then you can either fix it via a helper cell or a macro: =substitute(a...

Repeat Rows Limitations
Hi, I am having problems with the "Repeat Rows" feature when printing a worksheet. It is kind of hard to describe my goal so I've posted a screenshot at http://home.earthlink.net/~redbird77/images/rows.gif. I am making an order form that has many different sections - "Trail Mixes", "Nuts", Fruit" etc. At the top of each page I would like to repeat the rows for the date to be written in. That row also contains the name of a section. My problem is that if I choose to repeat the top 4 rows or so, the first section on every page will say "Trail Mix...

How does data go to the next row automatically in table?
How do I format a table so that data entered will automatically go to the next row, instead of being clumped into one row? I'm using a table in Word Hit the tab key??? -- Mary Sauer MS MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "kam shergill" <kam shergill@discussions.microsoft.com> wrote in message news:19CCDCB9-F5F1-445E-A1E9-ECC83D939479@microsoft.com... > How do I format a table so that data entered will automatically go to the > next row, instead of being clumped into one row? I'm using a table in Word kam sh...

Header Row Command Unavailable
I want to set a table header row to repeat across pages in Word 2007 but the command is greyed out and unavailable (this is after I've selected the header row). The same thing happens if I only place the cursor in the header row rather than selecting it. How do I get Word to let me set the header row to repeat across pages? Make sure the table is not wrapped. It is very easy to cause a table to be wrapped by inadvertently nudging the table handle. On the Table tab of Table Properties, choose "None" for "Text wrapping." -- Suzanne S. Barnhill Micros...

Printing 1st row in every page..
Dear All, In Microsoft Excel 2000 a user may freeze the first row/pane (Window menu/Freeze panes) so that it's always viewed regardless where he is in the spreadsheet. Is there something similar so that the first row becomes the printing header row so when printing a 24 pages spreadsheet every page beggins with that first row? Thanking you in advance for your help, Carlos. Hi Carlos, File<Page Setup>Sheet>Rows to repeat at top -- Kind regards, Niek Otten "Carlos" <carlos777mart@hotmail.com> wrote in message news:%23kJyI3JWGHA.4768@TK2MSFTNGP05.phx.gbl......

have 3 pieces of data in a cell seperated by a comma. How can I isolate the middle piece of data?
I've imported a large file of data that is basically three columns of data each seperated by a comma. (I could not figure out how to import them into a new spreadsheet so that each piece of data was automatically put into it's own column - but that is a different question) How can I do a "search replace" that will delete all of the data up to the firts comma? Then I would want to delete the data after the second comma in order to isolate the middle portion of data? Here is an example of the data: "10302003-12445","17.99","xyz21" Is there a be...

Simple link to Worksheet rows problem
I have a multiple page or sheet spreadsheet where I am pulling data into one sheet from another. Everything works fine except on one link where I am pulling text from some 30 colums however when it pulls the data (text) all I get is one entry pretty much from the center of the entire formula. Formula from the report sheet, =('Installed Software'!A2:A96) On the installed software sheet there is a list, one row each, of software titles but the formula above seems to bring over only something from near the middle of that list. If you want the range to do something then =sum(yourfomul...

Seperate words in a field
I have data that has information in a field that I need to separate. Example: Jimmy Ray (2004) (director) (producer) Jenny Rich (2000) (Actress) From this example I need to separate the items in parentheses from the name and enter them all in different columns. Is there a query or other possible way to do this? Thanks for any help given. As I see it this would be a 2-step process.. First you would separate all words: Divide text across cells Select the range of cells that contains the text values. The range can be any number of rows tall, but no more than one...

Columns & Rows have all flipped
It appears that my whole excel screen has reversed !!! The Row number are down the right hand side instead of the left, and cell A1 is now in the far right hand side of the sheet and it all works backwards !!! What have I done ? How do I get it back to normal ? Hi Neil, from the menu choose: tools, options, International tab, and then select Default directio "right to left". Cheers TGHCOG -- Message posted from http://www.ExcelForum.com OOPS sorry Neil I should have said LEFT TO RIGH -- Message posted from http://www.ExcelForum.com ...

Row Height dependent upon PC's font scale
I made a label template that only puts the text on the label if the row heights are correct. However, when the template is used by other people, the text and labels don't line up right. The row height changes scale dependent on each PC's font scale specified on its desktop. Is there any way to specify row height absolutely, instead of only being able to adjust it in pixel increments? Thanks. ...

Fold/unfold rows in a worksheet
I've got an Excel file which comes with some nice icons on the righthand side to fold/unfold hidden rows. Does anybody know how is it possible to get this "+" and "-" icons. The file does not seem to include any macros. I can not post the .xls file because it contains confidential data, but you can see a snapshot of the relevant part here http://juannavarroperez.googlepages.com/excelshot.PNG Thanks for any help. Data>Group and outline -- Kind regards, Niek Otten Microsoft MVP - Excel <juannavarroperez@gmail.com> wrote in message news:1161694343.363059.2...

How do I reference one cell in a table by using both the row and .
If I have a table with column headings and row headings that are different and I want to find out the information for a particular cell based on what is in those headings, how would I do that. i.e. if I had columns marked 1-10 and rows labled 2005 - 2010 how would I find the information of the cell that matched column 6 and row 2008? One way: Assuming the column/row you want to look up is in K1/K2, respectively, =VLOOKUP(K2,A1:J6,MATCH(K1,A1:J1,FALSE),FALSE) or, if you named your table "table": =VLOOKUP(K2,table,MATCH(K1,OFFSET(table,,,1,),FALSE),FALSE) In artic...

seperate accounts to seperate folders?
Hi I just had a quick question... I am currently using Outlook 2003, and I have 2 pop3 email accounts se up in my outlook.....one of them is my personal company email and th other is the general comapny email address (which i am responsible fo checking). What i am trying to find out how to do is, is it possibl to have the general company email messages go directly to a seperat folder besides the inbox? I still want my personal company email t come to the Inbox folder, but i want the general company email to go t a folder called "SCS." Any help would be greatly appreciated! Thank...

Calculate cell row and column
I have a case where I have a pivot table one ROW designator with about 20 values. I also have about 10 values. What I want to do is have the data listed as ROW 1 Data value 1 Data value 2 Data value 3 instead of Row 1 Data value1 Data value2 Data value3 Row 2 Data value1 etc. Is there a way to do this that isn't tedious? Thanks in advance, Barb Reinhardt Barb, Drag the Data Value button to the top of the columns. HTH, Bernie MS Excel MVP "Barb R." <BarbR@discussions.microsoft.com> wrote in message news:13A97919-634E-4BA8-8BE1-A1...

Automatically change row height?
Hello. The user types text stuff in A1. If there is no text, the row height for A2 is zero/minimal. If there is text, A2's row height autofits to that text. Some users enter more text than others, so A2's height has to grow or shrink depending on what is in A1. The formula in A2 is IF (A1="","",A1&", "&F16) Is this possible without VBA? If not, does anyone have any good code for this? Thanks! VR/Lost Private Sub worksheet_change(ByVal target As Range) Set target =3D Rows("2:2") If Range("A1").Value <> "" Th...