Sort and number a list
Amazing chart utilities from Jon Peltier
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Sort and number a list

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I need to, if possible, have Excel sort and number a list for me. So, if I have the following:

    Name Time Rank
    Joe 18:00
    Pete 19:30
    Bob 17:15

    I would end up with

    Name Time Rank
    Joe 18:00 2
    Pete 19:30 3
    Bob 17:15 1

    Would it be easy to do. or take a bunch of programming? Their are a few different lists I work with, ranging from 10 to 600 names. Thank you in advance for any help.

  2. #2
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,581
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sure. Just sort by the time column, insert numbers going down the 3rd column, then sort back (by name?).
    ~Anne Troy

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Just in case there are some duplicate times, I think it would be better to use the RANK worksheet function.
    Put the following in the first cell of column C (the Rank column) and fill down as far as required.
    Change the range B$1:B$10 in the formula to fit your requirements.

    =RANK(B1,B$1:B$10,1)

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    as an addendum ....

    if you put your =RANK function in a column to the left of the info, you can then use those rankings as the first column in a VLOOKUP

    so somewhere else you can list 1,2,3,4,5 etc down a column, do a lookup on these and return the original info in rank order

    as it's all linked, you will have automatically-sorting info without ever using the sort or any macro to sort
    :: Pharma Z - Family drugstore ::

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,790
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-03-16 01:47, Chris Davison wrote:
    as an addendum ....

    if you put your =RANK function in a column to the left of the info, you can then use those rankings as the first column in a VLOOKUP

    so somewhere else you can list 1,2,3,4,5 etc down a column, do a lookup on these and return the original info in rank order

    as it's all linked, you will have automatically-sorting info without ever using the sort or any macro to sort
    Chris,

    That would be toublesome in case of ties, because the suggested =RANK(B1,B$1:B$10,1) would assign them equal ranks.

    The suggestion will work if the RANK formula is modified as:

    =RANK(B1,B$1:B$10,1)+COUNTIF(B$1:B1,B1)-1

    Aladin


  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    absolutely ! good point

    I've only ever used it in league tables, so usually rank two or more conditions (ie points and goals) to escape duplicate rank values

    I suppose incorporating the row() value would also work....




  7. #7
    New Member
    Join Date
    Mar 2002
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    To expand on this a bit, if I am ranking, and want to make sure that no one ranks exactly the same, is there an elagant way to check if the ranking exists (say number 7), and to auto increment it by 1 if it does? Thank you for all of your help. Saved me a lot of hair pulling.

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    yeppers,

    Aladin's already taken care of it in his post.... and elegantly.....!



    edit......... I say elegantly in so far as it doesn't *need* to check if there's a duplicate ranking : the countif exploits the fact that rank will skip a number after it has dealt with a tie (2 people in 3rd place results in the next placing being 5th) but also deducts "1" from the second tying place after adding one to each tying place. Very nice.

    [ This Message was edited by: Chris Davison on 2002-03-20 14:15 ]

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com