Rank Without Duplicates in Google Sheets

Using the RANK function in a range to find the rank of numbers returns duplicate ranks if any number is repeated. So how can you rank without duplicates in Google Sheets?

We can consider rank without duplicates from two perspectives. One approach only considers the first occurrence of values, while the other increments the rank of any duplicates by 1.

Scenario #1: Incrementing the Rank of Duplicates by 1

Please see the example screenshot below.

Rank without Duplicates in Google Sheets

In cell B2, I have the regular RANK array formula, i.e., =ArrayFormula(IFNA(RANK(A2:A, A2:A, 0))), which returns the rank of the values in A2:A. I have another array formula in cell C2, which returns the rank without duplicates by incrementing the rank of duplicates by 1.

Since the number in cell A2 repeats twice (you can see that the number is the same in cell A8), the RANK formula in column B returns the same rank in cells B2 and B8.

However, the formula in column C intelligently returns unique ranks.

Formula and Explanation

Here is my formula in cell C2 that returns the rank without duplicates:

=ArrayFormula(LET(
   data, A2:A, 
   seq, SEQUENCE(ROWS(data)), 
   vZA, SORT(HSTACK(seq, data), 2, 0), 
   seqvZA, HSTACK(seq, vZA), 
   IF(NOT(A2:A), ,CHOOSECOLS(SORT(seqvZA, 2, 1), 1))
))

The above formula ranks the greatest value as rank #1. If you want the least value to rank #1, replace 0 (highlighted above) with 1.

Note: You can find another approach to solve this. Please check out my tutorial: How to Rank without Ties in Google Sheets.

Formula Breakdown:

Let’s start with the LET function, which we’ve used to assign names to the value expressions and then return the result of the formula expression.

LET(name1, value_expression1, [name2, …], [value_expression2, …], formula_expression)

As per the formula:

  • name1: data
    • value_expression1: A2:A
  • name2: seq
    • value_expression2: SEQUENCE(ROWS(data)) which returns the sequence numbers corresponding to the data to rank.
  • name3: vZA
    • value_expression3: SORT(HSTACK(seq, data), 2, 0)
      It first adds an array of sequence numbers with the rank range (the range to rank) and then sorts the rank range in descending order. The first column will be the sequence column, and the second column will be the data to rank.
  • name4: seqvZA
    • value_expression4: HSTACK(seq, vZA)
      Next, we again add the sequence numbers. So there will be three columns now: a sequence number column and the two-column sorted data from the previous step.
  • formula_expression: IF(NOT(A2:A), ,CHOOSECOLS(SORT(seqvZA, 2, 1), 1))
    This sorts the three-column array from the previous step based on its second column in ascending order. Finally, we choose the first column, which contains the rank without duplicates.

The IF(NOT(A2:A), , ...) section ensures that the formula returns a rank only in non-empty cells so that you can use open data ranges.

Scenario #2: Rank Considering Only First Occurrences

This is another method to rank without duplicates. Here we will consider only the first occurrences of the values in the range.

Formula:

=ArrayFormula(LET(
   data, A2:A, 
   rc, COUNTIFS(data, data, ROW(data), "<="&ROW(data)), 
   lt, IF(rc>1, , data), 
   IFNA(RANK(lt, lt, 0))
))
Rank Keeping First Occurrences of Data Points Only

To rank the least value as #1, replace 0 (highlighted in the formula) with 1.

Formula Explanation

It follows a very simple logic.

The following COUNTIFS formula returns the running count of the data:

COUNTIFS(data, data, ROW(data), "<="&ROW(data)) // named 'rc'

The logical test checks whether the running count is greater than one. If true, it returns blank; otherwise, it returns the values:

IF(rc>1, , data) //named 'lt'

Finally, the RANK function returns the ranks of the data without duplicates:

IFNA(RANK(lt, lt, 0)) //formula expression

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

Time Sequences in Excel by Minute, Hour, or Second Increments

Creating time sequences, whether by hour, minute, or second increments in Excel, can be...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

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.