Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to Assign a Serial Number to Duplicate or Unique Values in Excel?
Let us assume we have collected the data from a public source and you want to add the ID to each result. To do that, first we need to know how many unique items are present in the sheet. This tutorial will help you understand how you can assign a serial number to duplicate or unique values in Excel. Here we will have two parts. The first part is about assigning serial numbers to unique values, and the next part is about assigning serial numbers to duplicate values.
Assigning a Serial Number to Duplicate Values in Excel
Here we will first use the COUNTIF formula and then fill in all the results to get our final output. Let us see a simple process to assign serial numbers to duplicate values.
Step 1
Let us assume we have an Excel sheet where the data is similar to the data shown in the below image.
To assign the serial number to duplicates, click an empty cell; in our case, it will be B2, and enter the formula =COUNTIF($A$2:A2,A2) in the formula box, then click on Enter to get the first result as shown in the below image.
Step 2
We have assigned one to the first value. Now to fill the serial drag down using the autofill handle.
Assign a Serial Number to Unique Values in Excel
Here we will first use the formula to get our final result. Let us see a simple process to assign a serial number to unique values in Excel.
Step 1
Let us consider the same data that we used in the above example.
To assign serial numbers to unique values, click on an empty cell, in our case cell B2, and enter the formula =IF(COUNTIF(A$2:A2,A2)=1,MAX(B$1:B1)+1,VLOOKUP(A2,A$1:B1,2,0)) in the formula box. Click on Enter to get the first result as shown in the below image.
Step 2
We can successfully assign number to first value. Then drag down from the first result till all the results are printed, and our final output will look similar to the below image.
Conclusion
In this tutorial, we used a simple example to demonstrate how we can assign a serial number to duplicate or unique values in Excel to highlight a particular set of data.
19K+ Views
