Table of Contents
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.
=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.