Instead of creating lifeless unique IDs using sequential numbers, some alphabets extracted from the items and adding leading zeros, we can generate unique readable IDs in Google Sheets.

Here I have a formula-based approach, not an Apps Script based one, to create unique numeric/alpha-numeric item codes in Google Sheets.

Then how to generate readable IDs or product/item codes in Google Sheets using a formula(s)?

Examples of Creating Unique Readable IDs in Google Docs Sheets

Here are just two examples. You can modify my formula as per the instructions provided at the bottom of this post. So that it can possibly suit your needs.

1. Creating Unique IDs for the Books Bought

Let me first explain one of the ID generated.

Assume I have bought the book ‘Last Man’ authored by Mary Shelly. I want to generate an ID for this book.

In row # 5, you can see the necessary details of the book to generate the unique item numeric code. As per my formula, the generated readable unique ID is 000321 (cell D5).

In this, the three leading zeros don’t convey any meaning. It’s just for styling.

What about the number 321 then and how it is becoming a unique readable ID?

It’s like this. The number 3 represents the author code, 2 represents the genre and 1 represents the number of occurrences of the book from the same author under the same genre.

That means you can read the unique IDs with the help of another table (lookup table) maintained in the same sheet or another sheet.

For example purpose, I have maintained the lookup table within the same sheet. Here it is.

Note: You can add more author names and genres within the lookup table. The formula in column D (I mean the array formula in cell D2 which I am going to provide later in this tutorial) will consider the newly added records while generating the codes.

2. Creating Unique Readable IDs for Assets Purchased

In this example, to explain, I am taking the ID from cell E5 which is 0001121. Let’s see how to decode it with the help of a lookup table.

The last digit represents the occurrence and I’ll come to that later.

ID Formula and How to Modify It for a Different Lookup Table

To create unique IDs as above, I have actually used multiple Vlookups and combined them. In addition to that, I have used a running count formula.

Below is the formula that I have used in example 1 above (to be inserted in cell D2).

Multiple Vlookup Combined for Creating Unique Identification Codes

Final Formula:

=ArrayFormula(if(A2:A="",,"000"&ifna(vlookup(B2:B,G2:H,2,false))&ifna(vlookup(C2:C,I2:J,2,false))&L2:L))

There is one more formula (a supporting formula) in cell L2. I will come to that later. Below find the explanation to this formula in 5 steps.

Step 1

As you can see, there are two Vlookups combined in the above formula.

The first Vlookup searches the B2:B author names in G2:G and returns the corresponding code from the second column in the range G2:H.

=ArrayFormula(ifna(vlookup(B2:B,G2:H,2,false)))

Step 2

Similarly, the second Vlookup formula searches the genres (C2:C) of the books in I2:I and returns the value from the second column in the range I2:J.

=ArrayFormula(ifna(vlookup(C2:C,I2:J,2,false)))

For explaining, I have inserted the above two (step 1 and step 2) formulas in cell E2 and F2 respectively.

Step 3

Using the & sign, I have combined the two Vlookups as below and the result would be as marked in column D in the above image.

=ArrayFormula(ifna(vlookup(B2:B,G2:H,2,false)&ifna(vlookup(C2:C,I2:J,2,false))))

Step 4

In this step, we can prefix three (or as many numbers as you want) zeros to the unique readable IDs that we are generating. This step is optional.

Just insert this formula after the =ArrayFormula( in the above formula to add three zeros at the beginning of the generated numeric codes.

if(A2:A="",,"000"

Now the formula will look as below.

=ArrayFormula(if(A2:A="",,"000"&ifna(vlookup(B2:B,G2:H,2,false)&ifna(vlookup(C2:C,I2:J,2,false)))))

Adding leading zeros as above are optional as without using a formula, I mean by formatting the result column itself, we can add leading zeros.

If you prefer this formatting method, here is the tutorial – How to Add Leading Zeros in Google Sheets.

Step 5

This is the final step which is for avoiding duplicate IDs.

How to find if there are multiple books from the same author in the same genre?

Here we need to use running count formula in a helper column. Later, if you wish, you can skip using the helper column.

In my formula (final formula, not the formula under step 1 to 4) above that generates unique readable IDs in Google Sheets, you can see the range L2:L combined in the last part. The cell L2 in that range contains the below-running count formula.

Running Count:

=ARRAYFORMULA(if(len(C2:C),(COUNTIFS(B2:B&C2:C,B2:B&C2:C,ROW(C2:C),"<="&ROW(C2:C))),))

For this formula explanation, refer to my earlier guide titled – Running Count in Google Sheets.

The books of the author Mary Shelly repeats thrice in the horror genre, right?

In column L, the running count returns sequential numbers for these books. Adding these numbers to the step 4 formula will make the IDs unique.

Without these running count numbers, the IDs of the said books will be duplicates (00033). It makes the IDs 000331, 000332, and 000333.

Just add the range L2:L (running count) with the step 4 formula (or the L2 formula itself to avoid the use of an extra column L).

Unique Readable ID Formula Using the L2:L Range (Helper Column):

=ArrayFormula(if(A2:A="",,"000"&ifna(vlookup(B2:B,G2:H,2,false))&ifna(vlookup(C2:C,I2:J,2,false))&L2:L))

Unique Readable ID formula Using the L2 Formula (Without Helper Column):

=ArrayFormula(if(A2:A="",,"000"&ifna(vlookup(B2:B,G2:H,2,false))&ifna(vlookup(C2:C,I2:J,2,false))&if(len(C2:C),(COUNTIFS(B2:B&C2:C,B2:B&C2:C,ROW(C2:C),"<="&ROW(C2:C))),)))

In my example 2, i.e. creating unique readable IDs for assets purchased, I have used the same formula above but with some minor modifications. Find that details under the below subtitle.

How to Modify the Readable ID Generating Formula to Suit My Data?

Formula 1 in Cell E2:

=ArrayFormula(if(A2:A="",,"000"&ifna(vlookup(B2:B,H2:I,2,false))&ifna(vlookup(C2:C,J2:K,2,false))&ifna(vlookup(D2:D,L2:M,2,false))&O2:O))

The Running Count Formula 2 in Cell O2 (In the first example the similar formula was in cell L2):

=ARRAYFORMULA(if(len(C2:C),(COUNTIFS(B2:B&C2:C&D2:D,B2:B&C2:C&D2:D,ROW(C2:C),"<="&ROW(C2:C))),))

The first change is in the running count formula. In the first example, it was B2:B&C2:C and this time there is one more column. So it is like B2:B&C2:C&D2:D.

Note: Within the ROW formula (see the formula above), you can use any column reference. It is C2:C in my formulas in first and second examples. But it can be A2:A too. It just returns sequential numbers and that is what we want.

Now to the changes in the E2 formula (in first example this formula was in cell D2) which is the formula that generates unique readable IDs.

Earlier there were only two sub-tables (G2:H and I2:J) in the lookup table G2:J. Now in this example, there are three sub-tables in table H2:M2 they are H2:I, J2:K, and L2:M.

So there must be three Vlookup formulas instead of the two in the earlier example.

That’s all. This way we can create unique readable numeric ID codes in Google Sheets.

How to Create Unique Alpha-Numeric Codes in Google Sheets?

Without much effort, we can make our formulas to return unique alpha-numeric codes! Simply change the numbers in the lookup table to the alphabets.

Yes! There are no changes in the formula.

Example Sheet