How to 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:

Google Sheets unique identifier

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 team name along with a numeric value to indicate the occurrence of the team:

=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:

How to Generate Random Dates in Google Sheets
How to Autofill Letters of the Alphabet in Google Sheets
How to Generate a Normal Distribution in Google Sheets

Leave a Reply

Your email address will not be published. Required fields are marked *