Top 10 ranking that without duplicate names is possible in Google Sheets. Unlike many of you may think, there is a function to find top 10 rankers without repeating names.
I have a list maintained in Google Sheets with the name of peoples who have played a game. Each person has allowed trying the game multiple times. So obviously the names are there on the sheet multiple times as well as the scores.
How to find the top 10 scorers without duplicates in Google Sheets?
To extract the top 10 Rank holders without duplicate names, the recommended function in Google Sheets is Query or Sortn. I’ll give you both the solutions. You may believe it or not SORTN is better than Query in this case.
Similar: Flexible Array Formula to Rank Without Duplicates in Google Sheets (In this tutorial the duplicate ranks considered, not the names).
Ranking without Duplicate Names in Google Sheets
If there are no duplicate names the best way to get the top 10 rankers is sorting the range in descending order. The score column must be sorted in descending order. Then you can use
But if you only want unique names in the top ten ranking, then the sorting alone won’t work. You may need to use the Unique function together with Sort. Sortn acts as a combination of Unique+Sort.
Top 10 Ranking without Duplicate Names Using SORTN in Google Doc Sheets
Sample Data to find Unique Top 10 Rank Holders:
Formula:
=array_constrain(sort(sortn(sort(A2:B,2,0),9^9,2,1,0),2,0),10,2)I am skipping the formula explanation except for the SORT. To understand the use of SORTN please do check my below tutorial.
Must Check: SORTN Tie Modes in Google Sheets – The Four Tiebreakers.
To sort the score in descending order, I have used the inner SORT. So the names with top ranks will be on the top.
The SORTN + Inner SORT formula itself will return the top (not 10) unique rank names without any repetition. But the output will be sorted based on the name.
That’s why I have used the outer SORT to sort the scores in descending order.
The role of the function Array_Constrain is to limit the total number of rows to 10.
Query to Extract Top Ten Ranks without Duplicate Names
As I have mentioned the SORTN is the flexible formula to find unique names and scores of top ten scorers. I will tell you why? But before that here is the Query equivalent of SORTN in ranking.
=QUERY(A2:B, "select A,max(B) group by A order by max(B) desc limit 10 label max(B)''")I haven’t show you the output of either of the Query or SORTN. Here is that common output.
As I have told you, the SORTN formula has an edge over the Query to perform top 10 ranking without duplicate names. How?
Suppose you have a table with the names of players, their email IDs,
=array_constrain(sort(sortn(sort(A2:C,3,0),9^9,2,1,0),3,0),10,3)What are the changes in this formula compared to the previous Sortn formula?
First see the changes in the sample data and output.
There are three changes in the formula other than the range A2: B to A2: C. What are those changes?
Sort column # 2 changed to 3 in both the (inner and outer) Sort formula. Because in the new data range, the score column number is 3.
Since there are three columns in the output, the Array_Constrain too include 3 in num_cols.
ARRAY_CONSTRAIN(input_range, num_rows, num_cols)That’s all on top 10 ranking that without duplicate names in Google Spreadsheets.
Related Reading:
1. How to Use the RANK.AVG Function in Google Sheets.
2. How to Rank Group Wise in Google Sheets in Sorted or Unsorted Group.
3. How to Find Rank of a Non-Existing Number in an Existing Data Range.
