Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Jul 2004
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    column sorting (office 2000)

    Is there a way of automatically sorting an array three columns wide by "x" long, by a particular column(Vertically)?
    I have tried a few add ins but cannot get them to work. One is "xnumbers".
    I am using windows 98 and office 2000.
    Thank you.

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: column sorting (office 2000)

    Hi Stroop
    Is it critical that the array remains an array?
    You could copy the array and paste values, and then sort the new pasted list!!!
    Don't know if you tried that yet!
    Regards,
    Rudi

  3. #3
    New Lounger
    Join Date
    Jul 2004
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: column sorting (office 2000)

    Hi RudiS,

    I have tried that. Still no go.
    At the moment I am doing the process manually. Copying and pasting values and then sorting data by column. This works fine, but I am hoping that it can be done automatically(even if I have to copy and paste, and then let the function be automatic). Time is of the essence in what I am trying to achieve.
    Attached is an example.
    Column A is a number attached to the value of the second column B(line by line).
    The third column C is the ranking of the numbers in column B, from highest to lowest.
    What is needed is to sort column C from 1 to 6 in descending order.

    Thanks.

    Regards,

    Neil

  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: column sorting (office 2000)

    Maybe I am missing something.

    I selected A3:C8
    Data - sort
    Sort by: column C
    Ascending
    <OK>

    And got the solution you asked about.

    Steve

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: column sorting (office 2000)

    Neil,
    I cannot locate a solution spreadsheet that I had that used a formula to keep a list sorted by rank. If I find it i will pass it on to you! It contained a very similar setup to yours which I'm sure will solve your query!
    Other that that, this can be automated by a macro that will copy your array, paste as values and sort the appropriate column. Let me know if you need an example macro!!?
    Regards,
    Rudi

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: column sorting (office 2000)

    Hi again,
    The zip contains an add in that will install a range of functions. One of the functions is called VSORT. It is an array function that will sort your array in asc or desc order based on a column you specify.
    Unzip the addin and double click it. Then open excel. In your paste function list (Click on Fx button) you will have a new category called MOREFUNC.
    To use it:
    Select a range next to your list the same size as your list. Click FX and select VSORT. In the first argument specify the co-ordinates of your list. In the second arg, specify the co-ord. of the column to sort. Third arg specify 0 for desc or 1 for asc sort.

    EG In your attachment, select cells E3 to G8. Type =VSORT(E3:G8,G3:G8,0) and press Ctrl + Shift + Enter to create an array function. Your list ill recreate in the sorted order specified.

    Try it out!
    Regards,
    Rudi

  7. #7
    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: column sorting (office 2000)

    The answers to <post#=257175>post 257175</post#> have some formulaic ways to sort that does not require VB

    Steve

  8. #8
    New Lounger
    Join Date
    Jul 2004
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: column sorting (office 2000)

    Hi Rudi,
    The VSort function has solved my problem.
    Thank you very much!
    And thanks to everyone else who had an interest in solving my problem.

    Regards,

    Neil

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: column sorting (office 2000)

    Stroop,

    I'm glad to hear the VSort solved the problem! If you see fit, have a look at the sample sheet, as it shows a different approach in sorting data. It actually keeps the table automatically sorted based on functions like "RANK" and "VLOOKUP"! See if this might be an easier approach to your earlier query!??
    Regards,
    Rudi

  10. #10
    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: column sorting (office 2000)

    If I ever "de-duplicate" values, I prefer to use the row() instead of the rand() function. It puts the lower rows first and is consistent: Using rand() will change the order, using row() will keep it the same.

    Steve

  11. #11
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: column sorting (office 2000)

    Some very good advice Steve.
    This seems to be one of those cases where the "obvious" was not so obvious; or where the solution is so easy!? I somehow wonder how this eluded me? I can agree that it would work better!
    Regards,
    Rudi

  12. #12
    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: column sorting (office 2000)

    Things are always obvious after you see the answer <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Steve

Posting Permissions

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