1

I'm trying to generate a unique ID number for a items in a table in excel.

For example: 1.2.4 For each column the number is uniquely numbered based on the text. For example in the table below would be adding another Fruit entry and it being assigned the primary ID of 1.

Then the secondary ID would need to be a unique ID based on the primary. For example adding a row at the bottom as a type of Banana would be assigned: 1 for the secondary ID.

Then the teritary ID is based on the unique number under the secondary key.

And then finnally just constrcuting them all together which is just

=PrimaryID&"."&"SecondaryID"&"."&TeritaryID

I ended up getting a bit lost after finding the following formula online to get unique items:

=IF(COUNTIF(A$2:A2,A2)=1,MAX(B$1:B1)+1,VLOOKUP(A2,A$1:B1,2,0))

Edit: This formula matches the items and gives them a unique ID number Check the example below.

The next step where I need help is making it work with a format of x.x.x and have it make it unique only depending on the parent key making it nested.

 

╔════════╦════╗
║  Item  ║ ID ║
╠════════╬════╣
║ Test 1 ║  1 ║
║ Test 2 ║  2 ║
║ Nice   ║  3 ║
║ Test 2 ║  2 ║
║ Test 3 ║  4 ║
║ Test 4 ║  5 ║
║ dsa    ║  6 ║
║ Nice   ║  3 ║
║ Test 1 ║  1 ║
║ A      ║  7 ║
╚════════╩════╝

Table example below:


╔════════════╦══════════════╦══════════════╦═══════════════╦═════════════╦═══════════════╦════════╗
║ Primary ID ║ Primary Name ║ Secondary ID ║ Secondary Key ║ Tertiary ID ║ Teritary Name ║ Gen ID ║
╠════════════╬══════════════╬══════════════╬═══════════════╬═════════════╬═══════════════╬════════╣
║          1 ║ Fruit        ║            1 ║ Banana        ║           1 ║ Cavendish     ║ 1.1.1  ║
║          1 ║ Fruit        ║            1 ║ Banana        ║           2 ║ Lacatan       ║ 1.1.2  ║
║          2 ║ Veg          ║            1 ║ Potato        ║           1 ║ Maris Piper   ║ 2.1.1  ║
║          1 ║ Fruit        ║            2 ║ Apple         ║           1 ║ Pink Lady     ║ 1.2.1  ║
║          1 ║ Fruit        ║            2 ║ Apple         ║           2 ║ Gala          ║ 1.2.2  ║
║          1 ║ Fruit        ║            2 ║ Apple         ║           3 ║ Granny Smith  ║ 1.2.3  ║
║          3 ║ Meat         ║            1 ║ Chicken       ║           1 ║ Sussex        ║ 3.1.1  ║
║          2 ║ Veg          ║            1 ║ Potato        ║           2 ║ Yukon Gold    ║ 2.1.2  ║
║          1 ║ Fruit        ║            3 ║ Grape         ║           1 ║ Cardinal      ║ 1.3.1  ║
╚════════════╩══════════════╩══════════════╩═══════════════╩═════════════╩═══════════════╩════════╝

  • Please edit your question to explain what, exactly, you want that last formula to do. Including an example would make it clearer. – Blackwood Aug 16 '18 at 16:19
  • 1
    @Blackwood I put an explanation on that formula and how it's like a stepping stone to what I want to generate. – Jonathan Nakandala Aug 16 '18 at 16:29
  • Using the formula that you have mentioned, which ID have you been able to generate successfully? Have you accomplished the Primary ID column? What about the Secondary ID column? Is that where you are stuck? – Bharat Anand Aug 17 '18 at 5:14
0

See below a slightly different approach to get to the Gen ID.

I had to tweak the format of your excel table to the below format. enter image description here

Essentially what I have done is as below: 1. Re-arranged the Primary, Secondary and Tertiary Names in the first three columns.

  1. Then added another column (OrigSort) to maintain the original sort order of the entries in the far right (assuming the order might be important to you, and we will be changing this in the next step)

  2. Sorted the entire table on the first three columns in the ascending order.

  3. Once we reach this point, it is very easy to setup the formulaes for the IDs for each of the Keys as below:

    Col D: Primary ID

    =IF(COUNTIF(A$2:A2,A2)=1,MAX(D$1:D1)+1,VLOOKUP(A2,A$1:D1,4,0))

    Col E: Secondary ID

    =IF((A2=A1),IF(B2=B1,E1,E1+1),1)

    Col F: Tertiary ID

    =IF((A2=A1)*(B2=B1),IF(C2=C1,F1,F1+1),1)

  4. Now, use the Concatenate operation to get the Gen ID in Col G

  5. Finally, (optional step) restore the sort order back using the OrigSort column.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.