An instant sorting problem

[Chris]

Dear all,

I have a list of data in 2 columns: Column A stores 1000 names of
students and Column B stores the scores of the corresponding students. If I
want to make a sorted list of data in Columns C and D with descending
scores, what formulas should I use in columns C and D? It should be noted
that some students may get the same score. The order of these students in
the sorted list is not important as long as they are in adjacent rows.

Best Regards,
Chris

 

[Guest]

If you want to do this with standard functions (no VBA) it is possible if you
use a few intermediate columns(which you could hide or move to another sheet,
if you need to).

With your data in columns A and B, here would be formulas for columns C, D
and E (the intermediate calculations) and F and G with the results. Use
these formulas in the first row and copy them down:

C1: This gives the rank of the student's score
=RANK(B1,B:B,0)
D1: This accounts for possible 'ties' in rank
=C1+COUNTIF($C$1:C1,C1)-1
E1: This finds the row number for the student whose rank matches the current
row number
=MATCH(ROW(D1),D,0)
F1: This looks up the student from the row found in column E
=OFFSET($A$1,E1-1,0)
G1: This looks up the corresponding score
=OFFSET($A$1,E1-1,1)

K Dales

 

[Guest]

Wouldn't it be easier to select both columns and do a normal descending order
sort by column "B"? This will sort the existing list exactly how you want!