Table of Contents
Generating unique identifiers in Google Sheets can be done by using the built-in function “UNIQUEID()”. This function generates a random 32-character string that serves as a unique identifier for each cell it is applied to. This ensures that the generated identifiers will not be duplicated within the same sheet. Additionally, the use of this function can be combined with other functions such as “ARRAYFORMULA()” to automatically generate unique identifiers for a range of cells. This feature is useful for organizing and managing data in a spreadsheet, as well as for creating unique IDs for various purposes.
Generate Unique Identifiers in Google Sheets
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: