Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    May 2001
    Location
    Mount Vernon, Washington, USA
    Posts
    305
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SORT ROWS (2000)

    A co-worker placed Company Name (Row 1); Address (Row 2); City & Zip (Row 3) in an excel worksheet. We want to sort this according to Company Name. It did not work so I tried grouping it and then sorting, but it did not work that way either. Is it possible to sort and keep the rows together?
    lynndelap

  2. #2
    Star Lounger
    Join Date
    Feb 2002
    Location
    Illinois, USA
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SORT ROWS (2000)

    You need to use the Sort Left to Right option from the Data, Sort menu: Click in a cell in the list, then click Data, Sort, Options and select Sort Left to Right. Click OK, select the row you want to sort on (Row 1 in your case) and click OK. If you want to convert your list to have the Company Name, Address, and City & Zip as column headers instead of row headers, select the entire list, the Copy, and, on a new tab, do Paste Special, Transpose--this will convert your list to column-based instead of row-based.

  3. #3
    3 Star Lounger
    Join Date
    May 2001
    Location
    Mount Vernon, Washington, USA
    Posts
    305
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SORT ROWS (2000)

    That did not work. The headings are already at the top of columns with info. underneath in rows.
    Attached Files Attached Files
    lynndelap

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: SORT ROWS (2000)

    I see 3 options depending on Need:
    1) add a column with lists the company name followed by 1,2,3 as a sorting column
    Acme1 Acme
    Acme2 Address
    Acme3 city,state,zip
    Acme4 Space
    Acme5 Space

    2) Include the entire address in 1 cell (use <alt><enter> to add an "incell" linefeed)
    I don't like this since it combines all the elements

    3) the best way is to set up like a database:
    Each record should be in its OWN row
    Each item/field in a separate column (this allows using databasing features: filters, etc)
    Comp Name, address, city, state, zip, attorney, phone, fax, etc
    You can make a separate sheet to "grab" the contents of a row and put it into the right "printing form"

    Steve

  5. #5
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: SORT ROWS (2000)

    Hi Lynne,

    Attached workbook shows a workaround, using formulae to generate a sorted list for you. Setting your data out in a proper database format would be better than relying on this sort of approach, though.

    Your unsorted data goes on Sheet2, just as before, except that you must start each record on every 5th row, starting from row2. Your data didn't follow this rule strictly.
    Sheet3 merges the data from sheet2 and calculates a sort key based on the first 3 characters from each record. If more than one firm have the same three letters, increase the number in the sort key, multiplying all existing key values by 100 for each additional character used.
    Sheet4 sorts the records from sheet3, based on the sort key.
    If you want to include more records, copy the formulae on sheet3 down for as many rows are required. The, on sheet4, change the $10 references in A2 to match the number of records and copy the new formula down/across as far as it's needed.

    Cheers
    Attached Files Attached Files
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SORT ROWS (2000)

    If you want to convert the worksheet to a database format that would be easier to work with, the code in This Message can be modified to do the job. Since you have several columns that need to be transposed, it might take several passes. If you want to do this, and need help, let me know and I will see what I can do. I am traveling at the moment, and accessing the Lounge from a laptop through slow dial-up lines from a hotel, so my response may be a bit slow.
    Legare Coleman

  7. #7
    3 Star Lounger
    Join Date
    May 2001
    Location
    Mount Vernon, Washington, USA
    Posts
    305
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SORT ROWS (2000)

    I used your option 1. and that worked just fine. Thank you.
    lynndelap

  8. #8
    3 Star Lounger
    Join Date
    May 2001
    Location
    Mount Vernon, Washington, USA
    Posts
    305
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SORT ROWS (2000)

    I will take a look at this. Thank you! Can I do labels from this data also?
    lynndelap

  9. #9
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: SORT ROWS (2000)

    Hi there,
    Yes, I believe Sheet 4 now holds your data in a label format, with the name & address of each firm in a single cell.
    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •