Often you may want to generate unique identifiers for values in a dataset in Excel.
The following example shows exactly how to do so.
Example: Generate Unique Identifiers in Excel
Suppose we have the following list of basketball team names in Excel:
Suppose we would like to create a unique identifier value for each team.
To do so, we can choose the first unique identifier to be any value we’d like.
For example, we could use the value 1 for the first team:
Next, we can type the following formula into cell B3:
=IF(ISNA(MATCH(A3,A2:$A$2,0)),MAX(B2:$B$2)+1,VLOOKUP(A3,A2:$B$2,2,FALSE))
We can then click and drag this formula down to each remaining cell in column B:
Column B now contains a unique ID value for each team.
For example, we can see:
- Each team with the name “Mavs” has an ID value of 1
- Each team with the name “Lakers” has an ID value of 2
- Each team with the name “Hawks” has an ID value of 3
And so on.
If you would instead like to be able to differentiate between the players on the same team, you could type the following formula into cell B2 to generate a unique ID that contains the first three letters of the team name along with a numeric value to indicate the occurrence of the team:
=LEFT(A2,3)&"-"&COUNTIF($A$2:A2,A2)*1
You can then click and drag this formula down to each remaining cell in column B:
Column B now contains a unique ID for each row.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: How to Count Unique Names
Excel: How to Count Unique Values by Group
Excel: How to Count Unique Values Based on Multiple Criteria
