Thanks:  0
Likes:  0

# Thread: Sort and number a list

1. 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. Sure. Just sort by the time column, insert numbers going down the 3rd column, then sort back (by name?).

3. 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)

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

5. On 2002-03-16 01:47, Chris Davison wrote:

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

6. 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. 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. yeppers,

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

#### Posting Permissions

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