Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: sort (2000)

  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    sort (2000)

    As I understand it, Excel will only let you sort data down to three (3) levels). Is there anything out there that will expand that option to, say, 9-12 levels?
    Thanks,
    Jeff

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sort (2000)

    lets say you want to sort on column A, C, F, B (in that order)
    create a new column with this formula:

    =A2&C2&F2&B2

    Copy the formula down to match the length.
    Now sort on this column.

    Unluckily you can only sort ascending or descending for all columns together now.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: sort (2000)

    What if I want to sort the rows?

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sort (2000)

    Same:

    =A2&A4&A6&A1
    (copied to the right)
    sorts by row 2,4,6,1
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: sort (2000)

    Thanks, Jan. Any limits on the number I want to sort?

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sort (2000)

    - Your patience in typing the formula <g>
    - The max number of characters in a cell (so depends on content of your cells), I tested it until I reached 20000 characters.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: sort (2000)

    Jan,
    I just tried your suggestion. Unfortunately, I am afraid it doesn't do what I wanted. What I want to do is, for example, sort a number of rows by Column 1 first, then by Column 2, then Column 3, and so on, up to, say, 9 to 12 columns. What your suggestion does is put all of the values of the columns into one cell, and I can't then manipulate this result.
    Thanks,
    Jeff

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: sort (2000)

    Jeff,
    I think you can do this with mulitple sorts but you need to do it starting with the least important - i.e. if you want to sort by 12 columns, sort on columns 10-12 first, then 7-9, then 4-6 then finally 1-3. If you only wanted to sort on 4 columns, sort on column 4 first, then 1-3. Obviously you can adapt this for however many columns you want.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: sort (2000)

    How do you want to "manipulate this result?" Concatenating the values into one cell was done just so you could sort on that column. Once the sort is done, you can use all the original cells to do whatever you want.
    Legare Coleman

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: sort (2000)

    Legare,
    I'm not sure I understand what you are saying-sorry!
    jeff

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

    Re: sort (2000)

    You said you can't "manipulate this result." Can you tell us what you mean by that? I don't understand what you can't manipulate or why you would want to manipulate the result.
    Legare Coleman

  12. #12
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: sort (2000)

    Maybe it was a bad choice of words. What I was trying to say was when all of the data is in one cell from all of the columns I have sorted, I can't then go and use that data that is in the cell I have entered the suggested formula. Understand?

  13. #13
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: sort (2000)

    ... Have you tried creating a pivot table. You could then right click on the field, go to advanced, and indicate that the data should be sorted. This may give you some more flexibility.
    Regards,

    Gary
    (It's been a while!)

  14. #14
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: sort (2000)

    Gary,
    If you could walk me through an example, I will try it. When I tried to use pivot tables before, I couldn't get them to work.
    Jeff

  15. #15
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: sort (2000)

    Any chance you could post a portion of your dataset and an explanation of the sort you are trying to do so we are both on the same page.

    Please note that I may not be back online until Monday.
    Regards,

    Gary
    (It's been a while!)

Page 1 of 2 12 LastLast

Posting Permissions

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