Sort Data Using Formula To Find Top X

[mahmoud-lee]

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

[Rick Rothstein]

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 (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[mahmoud-lee]

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

[Rick Rothstein]

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]

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

[Admin]

Hi

Please find attached.

[Excel Fox]

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

[mahmoud-lee]

Thank you very much Mr. admin
Thank you very much Mr. excel fox
Thank you very much Mr. rick
Excellent solution

[mahmoud-lee]

can you arrange all data (name ,total) by other way ,Away from the array formula

[Excel Fox]

Are you saying you don't want a solution with array formula?

[mahmoud-lee]

yes , mr "excel fox"

[Excel Fox]

Any why would that be?

[mahmoud-lee]

Because array formula cause heavy with large data