Sort and number a list

joe77

New Member
Joined
Mar 14, 2002
Messages
9
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.
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Sure. Just sort by the time column, insert numbers going down the 3rd column, then sort back (by name?).
 
Upvote 0
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)
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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....

:)
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top