In this Excel tutorial, you will learn how to assign serial number and unique number to duplicate values.
In the left-side table of the below image, serial number is assigned to duplicate values.
Two formulas are shown for assigning a serial numbers to duplicate values.
In the right side table of the below image, unique number is assigned to duplicate values.
Assign serial number to duplicate values in Excel
Two formula methods are shown to assign serial number to duplicate values.
Each method has some advantages and disadvantages.
Method 1 – Using Countif
One can use the Countif function to get the serial numbers for duplicate values in Excel.
Cell B2 formula
=COUNTIF($A$2:A2,A2)
It is important to make 1st cell in a countif criteria range absolute and 2nd cell relative.
In the below image you can see “Cat” word is repeated 3 times and the 1st Cat value is assigned a serial number 1, 2nd Cat value 2, and 3rd Cat value 3.
Advantages:-
This is a simple and straightforward method.
No sorting is required.
Good for the small data set.
Disadvantages:-
This method is not good on the very large data set.
Calculation time is more for large data set as compared to 2nd method.
Deletion or paste special will take more time when used on large data set.
Method 2 – Using Row and Match function
To use this method you must sort your duplicate list values in a specific order (Ascending or Descending).
Use this method you want to assign serial number to large number of duplicate values.
Cell B2 formula
=ROW()-MATCH(A2,$A$2:$A$11,0)
Advantages :-
Very fast and efficient as compared to method 1.
Deletion and paste special takes less time for large data set.
Disadvantages:-
Values must be sorted in a specific order to use this formula.
Learn about Match Function
To clearly see the speed difference between the two methods download the workbook
Assign unique number to duplicate values
Two different formulas are show to assign unique number to duplicate values.
Method 1 – Using Match function
You can use the Match function to assign a unique number to duplicate values in Excel.
Cell B2 formula
=MATCH(A2,$A$2:$A$11,0)
In the below image can see list have 4 distinct values (“Cat”,”B4″,”Hat”,”Mat”).
In the list, each distinct value is assigned with a unique number.
For example, Cat is repeated 3 times, assigned a number 1, and B4 repeated 4 times assigned a number 2.
Advantages:-
Fast and Easy method.
Disadvantages:-
On changing sorting order assigned unique number value changes for each distinct value.
To prevent this you must paste special as values after applying the formula.
Follow 2nd method if you do not want to change unique number values after sorting.
Method 2 – Using Vlookup
To use this method 1st build a reference table.
Building reference table
- Copy-paste duplicate values in a range
- Select pasted range then press ALT A M keys in series to remove duplicates. Alternatively, go to the Data tab then select the Remove Duplicates option.
- Adjacent to this column write serial number
After building the reference table you can use Vlookup to assign a unique number to duplicate values.
Cell B2 formula
=VLOOKUP(A2,$D$1:$E$5,2,FALSE)
Do you know How to make Vlookup formula fast?
Advantages:-
Assigned unique number for a value does not changes after changing sort order.
Disadvantages:-
Not a straightforward method. You have to first build a reference table of distinct values with respective serial numbers.
Assign serial number to multiple column duplicate value or duplicate record
In the below image you can see duplicate records like ABC & XYZ.
Here you will see how to give the serial number and Unique number to multiple columns duplicate value or to duplicate records.
To get serial number or unique number for duplicate records 1st add a column having concatenated values of multiple columns.
Cell A2 formula
=CONCATENATE(B2,C2)
Use this concatenated values column in the formula for assigning serial number and unique number.
Cell D2 formula for assigning serial number to duplicate records
=COUNTIF($A$2:A2,A2)
Cell E2 formula for assigning unique number to duplicate records
=MATCH(A2,$A$2:$A$9,0)
Suggested Readings:-
How to Count Unique and Distinct Values in Excel
How to prevent duplicate entries in Excel? | Allow only unique values entries in Excel