Table of Contents
To generate unique identifiers in Google Sheets, you can use the RAND function to generate a random number, and then combine it with other functions such as CONCATENATE or TEXT to create a unique identifier. Alternatively, you can use the UNIQUE formula to automatically remove duplicates from a list of values and create a unique identifier for each entry. Another option is to use the ROW function to assign a unique number to each row, which can then be combined with other information to create a unique identifier.
Often you may want to generate unique identifiers for rows in Google Sheets.
Fortunately this is easy to do and the following example shows how.
Example: Generate Unique Identifiers in Google Sheets
Suppose we have the following list of basketball team names in Google Sheets:
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 this example, we will 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.
=A2&"-"&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 Google Sheets: