Find Top 10 Unique Names by Score in Google Sheets

Published on

In several scenarios, you might need to find the top 10 unique names by the score in Google Sheets. This is commonly used in sports rankings, gaming leaderboards, and academic competitions where multiple attempts exist. The goal is to ensure that each participant appears only once, with their highest score considered.

You can achieve this using either a combination of SORT and SORTN or the QUERY function.

How to Find the Top 10 Unique Names by Score in Google Sheets

Formula Option 1: Using SORT and SORTN

=SORTN(SORT(SORTN(SORT(A2:B, 2, FALSE), 9^9, 2, 1, TRUE), 2, FALSE), 10, 0, 2, FALSE)

Formula Option 2: Using QUERY

=QUERY(A2:B, "select Col1, max(Col2) group by Col1 order by max(Col2) desc limit 10 label max(Col2)''")

When using these formulas, replace A2:B with your actual data range, where column A contains names and column B contains scores.

Google Sheets Example: Find Top 10 Unique Names by Score

Sample Data

Sample data of player names and scores for top 10 unique names by score

In the sample data, column A contains player names, and some players appear multiple times. In such cases, only their highest score should be considered.

Applying the Formula

To display the top 10 unique players by score, enter one of the formulas above in D1 (if columns D and E are empty). The results will populate in D1:E10.

Example: Find top 10 unique names by score in Google Sheets

How the Formula Works

Below is an explanation of how both formulas work. They are simple to understand—let’s dive in!

SORT + SORTN Method

  1. SORT(A2:B, 2, FALSE): Sorts the scores in descending order (highest to lowest).
  2. SORTN(..., 9^9, 2, 1, TRUE): Selects all unique names, keeping only their highest score.
  3. SORT(..., 2, FALSE): Ensures the results remain sorted by score.
  4. SORTN(..., 10, 0, 2, FALSE): Extracts the Top 10 scores from the sorted, unique-player list.

QUERY Method

  1. group by Col1: Groups rows by player names (removes duplicates).
  2. max(Col2): Extracts each player’s highest score.
  3. order by max(Col2) desc limit 10: Sorts scores in descending order and selects the top 10.
  • As a side note, between the two functions, I prefer the powerful SORT and SORTN combo over QUERY, as it performs better. QUERY processes structured queries similar to SQL, which can slow performance when working with very large datasets or applying multiple queries to the same dataset.

How to Find the Top 5 Unique Names by Score

If you need the top 5 instead of the top 10:

  • In SORTN, replace 10 with 5.
  • In QUERY, replace limit 10 with limit 5.

Example: =SORTN(SORT(SORTN(SORT(A2:B, 2, FALSE), 9^9, 2, 1, TRUE), 2, FALSE), 5, 0, 2, FALSE)

That’s it! You can now easily find the top unique names based on score in Google Sheets.

Top 10 Unique Names by Score + Additional Names if Tie in Nth Highest

One advantage of using the SORT + SORTN combination to find the top N unique names in Google Sheets is that it can dynamically include additional names if there’s a tie at the nth score.

To achieve this, simply replace tie mode 0 with 1 in the outer SORTN, as shown below:

=SORTN(SORT(SORTN(SORT(A2:B, 2, FALSE), 9^9, 2, 1, TRUE), 2, FALSE), 10, 1, 2, FALSE)

FAQ: Common Questions About Ranking in Google Sheets

How do I rank without duplicate names in Google Sheets?

Use SORTN or QUERY to remove duplicates and keep only the highest score.

Can I find the top 10 without using QUERY?

Yes! Use SORT and SORTN combo to get the top 10 unique names by score.

How do I assign ranks after finding the top 10?

You can use the RANK.EQ or RANK.AVG functions alongside the formulas above. For example, after clearing F1:F, enter the following formula in cell F1.

=ArrayFormula(IFNA(RANK.EQ(E1:E, E1:E)))

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Expert Prashanth KV is a Diamond Product Expert in Google Sheets, officially recognized by Google for his contributions to the Docs Editors Help Community and featured in the Google Product Experts Directory. Explore his blog to learn advanced formulas, automation tips, and problem-solving techniques to elevate your spreadsheet skills.

Mode of Comma-Separated Numbers in Excel (Dynamic Array)

There is no dedicated function in Excel to directly find the mode of comma-separated...

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

Count Consecutive Workday Absences in Google Sheets

This tutorial offers a powerful formula-based solution to count consecutive workday absences in Google...

More like this

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

Count Consecutive Workday Absences in Google Sheets

This tutorial offers a powerful formula-based solution to count consecutive workday absences in Google...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.