Sort and number a list

joe77 · Mar 15th, 2002, 06:18 PM

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.

Anne Troy · Mar 15th, 2002, 07:14 PM

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

Anonymosity · Mar 15th, 2002, 07:51 PM

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)

Chris Davison · Mar 16th, 2002, 02:47 AM

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

Aladin Akyurek · Mar 16th, 2002, 03:11 AM

Quote:

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

Chris Davison · Mar 16th, 2002, 03:24 AM

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

joe77 · Mar 19th, 2002, 08:07 AM

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.

Chris Davison · Mar 20th, 2002, 03:11 PM

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 ]

Mar 15th, 2002, 07:14 PM	#2
Anne Troy MrExcel MVP Join Date: Feb 2002 Location: Allentown, PA Posts: 2,510	Sure. Just sort by the time column, insert numbers going down the 3rd column, then sort back (by name?). __________________ ~Anne Troy

Mar 16th, 2002, 02:47 AM	#4
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	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 ::

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 15th, 2002, 06:18 PM	#1
joe77 New Member Join Date: Mar 2002 Posts: 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.

Mar 15th, 2002, 07:51 PM	#3
Anonymosity New Member Join Date: Mar 2002 Posts: 1	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)

Mar 16th, 2002, 03:24 AM	#6
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	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....

Mar 19th, 2002, 08:07 AM	#7
joe77 New Member Join Date: Mar 2002 Posts: 9	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.

Mar 20th, 2002, 03:11 PM	#8
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	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 ]