Table of Contents
Generating unique identifiers in Excel can be done using the function “UNIQUEID”, which creates a random and unique 12-character string of numbers and letters. This function can be used in a variety of ways, such as creating unique identification numbers for data entries or creating unique invoice numbers for tracking purposes. By using the UNIQUEID function, users can easily generate a large number of unique identifiers without the risk of duplication, ensuring accurate and efficient data management.
Generate Unique Identifiers in Excel
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.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel: