generating rank-ordered list
 

I have an alphabetical list of names and scores associated with them.
I would like to generate a list with these names in order of their
scores, but I can't seem to find how this is possible. I recently
found use of the "lookup" function, but it requires that the
"Lookup_vector" have data already sorted. Is there some way to take
care of this?

generating rank-ordered list
 

Are there any tie scores or are tie scores even a possibility?

Joe - 100
Sue - 110
Tom - 100

Biff

wrote in message
oups.com...
I have an alphabetical list of names and scores associated with them.
I would like to generate a list with these names in order of their
scores, but I can't seem to find how this is possible. I recently
found use of the "lookup" function, but it requires that the
"Lookup_vector" have data already sorted. Is there some way to take
care of this?

generating rank-ordered list
 

yes, but they are very unlikely

Biff wrote:
Are there any tie scores or are tie scores even a possibility?

Joe - 100
Sue - 110
Tom - 100

Biff

wrote in message
oups.com...
I have an alphabetical list of names and scores associated with them.
I would like to generate a list with these names in order of their
scores, but I can't seem to find how this is possible. I recently
found use of the "lookup" function, but it requires that the
"Lookup_vector" have data already sorted. Is there some way to take
care of this?

generating rank-ordered list
 

Ok, here's a few ways to do it:

Assume names are in A1:A10, scores in B1:B10.

If you want to take the chance that no ties will occur:

Enter this formula in, say, C1:

=INDEX(A$1:A$10,MATCH(LARGE(B$1:B$10,ROWS($1:1)),B $1:B$10,0))

Copy down to C10.

If you want to account for possible ties:

One way is to use a helper column to rank the scores:

Enter this formula in C1 to rank the scores. Copy down to C10:

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

Then, to get the names in ranked order, say, in D1:

=INDEX(A$1:A$10,MATCH(SMALL(C$1:C$10,ROWS($1:1)),C $1:C$10,0))

Copy down to D10.

If you want to account for possible ties and not use the helper rank column:

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER) in C1:

=INDEX(A$1:A$10,MATCH(LARGE(B$1:B$10-ROW(B$1:B$10)/10^10,ROWS($1:1)),B$1:B$10-ROW(B$1:B$10)/10^10,0))

Copy down to C10.

Biff

wrote in message
ups.com...
yes, but they are very unlikely

Biff wrote:
Are there any tie scores or are tie scores even a possibility?

Joe - 100
Sue - 110
Tom - 100

Biff

wrote in message
oups.com...
I have an alphabetical list of names and scores associated with them.
I would like to generate a list with these names in order of their
scores, but I can't seem to find how this is possible. I recently
found use of the "lookup" function, but it requires that the
"Lookup_vector" have data already sorted. Is there some way to take
care of this?

generating rank-ordered list
 

Hi Jeffrey,

Say your names are in column D and scores are in column E.

Highlight both columns of data
Goto DataSort
Select sort by column E from the pulldown box
Check descending
Click OK

Any ties will retain their alphabetical order.

HTH
Martin