Sort Data Using Formula To Find Top X

**mahmoud-lee** · 06-02-2013, 05:47 AM

I own a list of students,Iwant to be arranged depending on the total Top

**Rick Rothstein** · 06-02-2013, 05:54 AM

I do not understand the logic behind the "ordered" list you say you want. The names are not in any alphabetic order nor is the list order by the numerical values in either the Total or Ranking column. Can you explain what determines the order you show for the "I want this list"?

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

**mahmoud-lee** · 06-02-2013, 06:02 AM

I want to arrange the names depending on the total from high to low
To became look like the first list

**Rick Rothstein** · 06-02-2013, 06:33 AM

Originally Posted by mahmoud-lee

I want to arrange the names depending on the total from high to low
To became look like the first list

Oh, you showed the list backwards from what I was expecting. Okay, your out-of-order list is in cells A17:C29... Select that range, then click Data/Sort in your XL2003 worksheet, select "Ranking" from the first drop-down box (labeled "Sort by") and select the option button labeled "Ascending" next to it... make sure the option button on the bottom labeled "Header row" is selected (it should be by default) and then click the OK button.

**mahmoud-lee** · 06-02-2013, 01:45 PM

I know very well how sorted data, but I want a solution by formula

**Admin** · 06-02-2013, 02:23 PM

Hi

Please find attached.

**Excel Fox** · 06-02-2013, 02:24 PM

Use B3=LARGE($B$18:$B$29,ROWS(B$18:B18)) and drag down to get the data sorted by the 'total' column, and

Use A3=INDEX($A$18:$A$29,SMALL(IF(B3=$B$18:$B$29,ROW(I NDIRECT("1:"&ROWS($B$18:$B$29))),""),COUNTIF($B$3: $B3,B3))) as an array formula to get the corresponding names.

Your third column was anyway a rank, so I just used the ranking formula