Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
An instant sorting problem
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
An instant sorting problem
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: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 "Chris" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
An instant sorting problem
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! "Chris" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
live instant updaate | Excel Worksheet Functions | |||
Add Instant Totals use what feature? | Excel Worksheet Functions | |||
Hyperlinks Not Instant In 2007 ?? | New Users to Excel | |||
An instant sorting problem | Excel Worksheet Functions | |||
Instant messaging | Excel Programming |