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(C2:C,I2:J,2,false)))įor explaining, I have inserted the above two (step 1 and step 2) formulas in cell E2 and F2 respectively.
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(B2:B,G2:H,2,false))) Step 2
#Google uuid generator code#
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.
Step 1Īs you can see, there are two Vlookups combined in the above formula.
Below find the explanation to this formula in 5 steps. There is one more formula (a supporting formula) in cell L2. Multiple Vlookup Combined for Creating Unique Identification Codesįinal Formula: =ArrayFormula(if(A2:A="","000"&ifna(vlookup(B2:B,G2:H,2,false))&ifna(vlookup(C2:C,I2:J,2,false))&L2:L)) In addition to that, I have used a running count formula.īelow is the formula that I have used in example 1 above (to be inserted in cell D2). To create unique IDs as above, I have actually used multiple Vlookups and combined them.
#Google uuid generator how to#
ID Formula and How to Modify It for a Different Lookup Table The last digit represents the occurrence and I’ll come to that later. Let’s see how to decode it with the help of a lookup table. In this example, to explain, I am taking the ID from cell E5 which is 0001121. Creating Unique Readable IDs for Assets Purchased 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. Note: You can add more author names and genres within the lookup table. That means you can read the unique IDs with the help of another table (lookup table) maintained in the same sheet or another sheet.įor example purpose, I have maintained the lookup table within the same sheet. 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. What about the number 321 then and how it is becoming a unique readable ID? In this, the three leading zeros don’t convey any meaning. As per my formula, the generated readable unique ID is 000321 (cell D5). In row # 5, you can see the necessary details of the book to generate the unique item numeric code. Let me first explain one of the ID generated.Īssume I have bought the book ‘Last Man’ authored by Mary Shelly. You can modify my formula as per the instructions provided at the bottom of this post. 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 I have a formula-based approach, not an Apps Script based one, to create unique numeric/alpha-numeric item codes in Google Sheets. 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.
0 kommentar(er)
