In Excel, how to add serial number to only unique values in a column and let the duplicate values have same serial number as the original one?

Question

I am trying to add a serial number to the rows in an excel sheet. I have multiple duplicate values in "ID" column and I am hiding the duplicate values first and then trying to add the serial number only for the unique values. I want all the duplicate values to have same serial number as the original value.

My sheet looks like this now :

It looks like this after hiding the duplicates:

What I am trying to achieve looks like this:

There are more than 50K rows and I am hoping that there is some way to do this in excel without manually filling in the numbers in the "No." column. Any help would be greatly appreciated!

Answer 1

This can be done with a simple formula:

=IF(B2=B3,A2,A2+1)

First put the value "1" in cell "A2". Then put the mentioned value below and continue dragging and dropping.

Your result will look as follows:

Answer 2

Add Sequential Serial Numbers

• In cell A2 (adjust the last row):

=XMATCH(B2:B13,UNIQUE(B2:B13))