How to rank without duplicates

Answer

To rank without duplicates in Excel, you can assign unique ranks to values even if some of them are identical. Excel has built-in functions for ranking, but duplicates can cause multiple items to receive the same rank. To avoid this, you can use a formula that adjusts ranks dynamically.

1. Using the RANK Function with a Tiebreaker

The RANK function assigns the same rank to identical numbers. To make ranks unique, you can add a small adjustment using the COUNTIF function.

Steps:

1. Suppose your data is in column A, starting from A2.

2. In cell B2, enter the following formula:

   =RANK(A2,$A$2:$A$10,0)+COUNTIF($A$2:A2,A2)-1

3. Drag the formula down for all rows.

Explanation:

• RANK(A2,$A$2:$A$10,0) ranks the value in A2 in descending order.

• COUNTIF($A$2:A2,A2)-1 adds a small increment to each duplicate, ensuring unique ranks.

Example:

Here, two values of 90 are given ranks 2 and 3 instead of both being 2.

2. Using the RANK.EQ Function with Adjustment

RANK.EQ works similarly to RANK but is newer and more reliable in modern Excel versions. The formula to remove duplicates is:

=RANK.EQ(A2,$A$2:$A$10,0)+COUNTIF($A$2:A2,A2)-1

The logic mirrors the previous method. RANK.EQ assigns the same rank to duplicates, and the COUNTIF adjustment ensures unique ranking.

3. Ranking in Ascending Order

To rank smaller numbers higher, change the 0 in the formula to 1: 

=RANK(A2,$A$2:$A$10,1)+COUNTIF($A$2:A2,A2)-1

This ranks the lowest number as 1 and adjusts duplicates similarly.

4. Using Helper Columns for Large Datasets

For large datasets, you can create a helper column to calculate adjusted values for unique ranking:

1. In column B, calculate a tiny adjustment: 

   =A2 + ROW()/100000

2. Rank the adjusted values normally: 

   =RANK(B2,$B$2:$B$100,0)

This method ensures no two ranks are ever the same because each row has a unique fractional adjustment.

Notes and Tips

• Ensure the $ signs in formulas lock the ranges when dragging down.

• For descending ranks, use 0 in RANK or RANK.EQ; for ascending ranks, use 1.

• This method works on Windows and Mac versions of Excel identically.

This technique guarantees unique rankings, which is critical when sorting leaderboard scores, sales numbers, or any metric requiring clear order.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report