Results 1 to 12 of 12
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I would like to perform the following sorting: I have 30 columns, divided into 15 "groups" of 2 columns each. In the first row/column of each group, I have a name. What I would like to do is sort the 15 groups alphabetically. That is columns 1 and 2 would always be sorted as one, columns 3 and 4 as one, etc., based on the name in row one of column 1, row 1 of column 3, row 1 of column 5, etc.
    Any ideas?
    Thanks in advance.


  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    What version of Excel are you using? Most recent versions want to sort the whole worksheet, but you can specify the range that you want to sort as well. If that get's too complext for you, try inserting a blank column between column 2 and 3, between 4 and 5, etc. Then when you click in the left column of a pair, Excel will default to sorting just the two columns.
    Wendell

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by WendellB View Post
    What version of Excel are you using? Most recent versions want to sort the whole worksheet, but you can specify the range that you want to sort as well. If that get's too complext for you, try inserting a blank column between column 2 and 3, between 4 and 5, etc. Then when you click in the left column of a pair, Excel will default to sorting just the two columns.
    I have 07, and I am unclear as to your proposed solution.
    Sorry

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    The Sort capabilities are on the Home Ribbon near the right hand end.
    1. Select the first two columns by clicking in the column headers
    2. Click the sort button on the ribbon and choose smallest to largest or largest to smallest
    3. That will sort the first two columns
    4. Now select columns 3 and 4
    5. Click Sort again and choose the order you want.
    6. Columns 3 and 4 are now2 sorted
    7. Continue this process until all 15 column pairs have been sorted
    Wendell

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by WendellB View Post
    The Sort capabilities are on the Home Ribbon near the right hand end.
    1. Select the first two columns by clicking in the column headers
    2. Click the sort button on the ribbon and choose smallest to largest or largest to smallest
    3. That will sort the first two columns
    4. Now select columns 3 and 4
    5. Click Sort again and choose the order you want.
    6. Columns 3 and 4 are now2 sorted
    7. Continue this process until all 15 column pairs have been sorted
    Here is an example of "before" and "After" of what I want. The before in on Sheet1, and after on Sheet2


    Attached Files Attached Files

  6. #6
    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
    I don't follow the logic in the "after" based on what you ask. Why is the E Section deleted? Why are sections B and D duplicated?

    Based on the logic you describe and the setup (ignoring your after) I would:
    Insert a new row2
    Then in A2:
    =A1
    In B2:
    =IF(ISBLANK(B1),A2&"Z",B1)
    Copy B2 to C2:J2
    Copy Row2
    Paste - special -values in row 2
    Data - sort
    [options ] Sort left to right[ok]
    Sort on row 2
    Delete row 2

    Steve

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Quote Originally Posted by jlkirk View Post
    Here is an example of "before" and "After" of what I want. The before in on Sheet1, and after on Sheet2
    You really need to be more explicit on what you are trying to do if you expect to get useful assistance from Loungers. Excel by default sorts by rows, and since you didn't specify that you wanted to sort by columns, I assumed that was what you wanted. Furthermore, as Steve points out, your example appears to be nonsense, as you have one more set in Sheet 2 than you did in Sheet 1, and one of the Sheet 1 group (E) is gone. Frankly, if you only have 15 groups to do, it would be easiest to simply cut and paste the group of cells, as it appears you really want to sort it based on the name in the row 1 cell. Sorting by column in Excel won't do what you want unless you put the same label in both cells, and put a 1 (or someother character) after the name in the second cell so the columns in each group retain their relative position.
    Wendell

  8. #8
    New Lounger
    Join Date
    Jan 2010
    Location
    Lilydale, Victoria, Australia
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I don't think you can do what you want with your current setup. You need to associate your pairs of columns somehow. An easy way would be to add a row at the top and merge the pairs of new cells in that row at the top of each pair of columns. Then add the names you want to sort by in the merged cells.

  9. #9
    New Lounger
    Join Date
    Jan 2010
    Location
    Lilydale, Victoria, Australia
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts
    OK, I thought that would work, but I just tried it and Excel won't sort it (using 2003, btw).
    It does work if you just add a row at the top and, without merging, add the names-to-sort-by to each pair of new cells, like: Bill Bill Fred Fred Jan Jan and so on. Then sort by that row.

  10. #10
    New Lounger
    Join Date
    May 2010
    Location
    Christchurch New Zealand
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Name each blank column with a suffix "1"
    Copy the complete table and paste "transpose" into a new spreadsheet and sort in the normal way
    copy this "worksheet" and paste "transpose" back into the original sheet

  11. #11
    New Lounger
    Join Date
    May 2010
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Exclamation

    Hmmm... All this sorting using Excel's Sort tool seems like hard work to me, and may be difficult to remember the "how to" for future usage. I'd like to propose a different strategy - using some of Excel's formulae! I've come up with a solution, which you can find in the attachment. I think some of the "work" could be done better with the use of array formulae, but it works! Data in, sorted immediately, no need to run the sorter!! I'd be interested in seeing other solutions which don't use Excel's sort tool.

    RAK
    [attachment=88813:Sorting RAK.xlsm]
    Attached Files Attached Files

  12. #12
    New Lounger
    Join Date
    Jul 2010
    Location
    Australia
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I came up with an alternate way to do it using code (2003)
    It makes some assumptions about the data (ie only ever 5 double columns, and 11 rows each, but they could really be automated)
    Forgive the messy code, I'm no expert, but it seems to work ok
    It was fun to play with on a rainy Sunday arvo anyway
    Attached Files Attached Files

Posting Permissions

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