# Creating ID based on numerous cell content

Occasional Contributor

# Creating ID based on numerous cell content

Hiya,

Does anyone know how to generate a running ID (being 1, 2, 3 etc) based on column information?

I need ID1 to read 1 for the first Shot # read according to the date (01/04/2019). Then 2 for the next unique shot # read. Every shot # on any given date will have the same ID.

This then needs to apply to ID2 but based on the Product adjacent to the shot/date.

The below table is how I would like to ID's to read, I just need the ID's to generate themselves rather than be populated by manual input...

 Date ID1 Shot # ID2 Product 1/04/2019 1 ABC 1 100200 1/04/2019 1 ABC 1 100200 1/04/2019 2 DEF 1 100200 1/04/2019 2 DEF 2 300400 1/04/2019 1 ABC 2 200100 2/04/2019 1 ABC 1 200200 2/04/2019 2 GHI 1 200200 2/04/2019 2 GHI 2 400500 2/04/2019 3 DEF 1 200200 2/04/2019 1 ABC 2 300400

Thank you :)

@Twifoo

12 Replies

# Re: Creating ID based on numerous cell content

It seems that ID2 for Product 200100 on 1/04/2019 and Product 300400 on 2/04/2019 should be 3 instead of 2. Please clarify.

# Re: Creating ID based on numerous cell content

Hi, ID2 is unique to ID1 & the date. So 200100 is the second product adjacent to ABC on the 01/04/2019 as is 300400 on the 02/04/2018

# Re: Creating ID based on numerous cell content

Hi @Twifoo does my response make sense?

# Re: Creating ID based on numerous cell content

I will study the logic of the needed formulas, especially ID2.

# Re: Creating ID based on numerous cell content

Thank you!! @Twifoo

# Re: Creating ID based on numerous cell content

In the attached file, the formula for ID1 in B2, copied down rows, is:

=IF(\$A2<>\$A1,1,
IFERROR(LOOKUP(2,1/((\$A\$1:\$A1=\$A2)*(\$C\$1:\$C1=\$C2)),\$B\$1:\$B1),
LOOKUP(2,1/(\$A\$1:\$A1=\$A2),\$B\$1:\$B1)+1))

Conversely, the formula for ID2 in D2, copied down rows, is:

=IF(\$A2<>\$A1,1,
IFERROR(LOOKUP(2,1/((\$A\$1:\$A1=\$A2)*(\$C\$1:\$C1=\$C2)*(\$E\$1:\$E1=\$E2)),\$D\$1:\$D1),
IFERROR(LOOKUP(2,1/((\$A\$1:\$A1=\$A2)*(\$C\$1:\$C1=C2)),\$D\$1:\$D1)+1,1)))

# Re: Creating ID based on numerous cell content

Hi @Twifoo

This is great thank you. ID2 works perfectly, however I found an issue with ID1.

Please refer to the attached file as an example - If the shots aren't in order, as the orange highlighted cells are, ID1 doesn't flow numerically. As you can see in the yellow highlighted cells the third shot listed (MER0342) on the 04/01/2019 has been recognised as ID #2 where it should be recognised as ID #3 ... Does this make sense?

I have provided another example using the 5th & 6th highlighting how the order changes the sequence of ID #

# Re: Creating ID based on numerous cell content

You have to manually fill in Columns G and I so that I can understand the logic that will return your desired results. Thereafter, attach your sample file with those columns completely filled in.

# Re: Creating ID based on numerous cell content

Although the dates are not entered chronologically, the formulas must still conform to the same logic. Thus, ID1 for Shots TES0416 and MER0342 must be 3 and 4, respectively, because they are the 3rd and 4th unique Shots on 1/4/2019.

In the attached file, I simply entered 1 in B2 and D2. The formula in B3, copied down rows, is:

=IF(\$A3<>\$A2,1,
IFERROR(LOOKUP(2,1/((\$A\$2:\$A2=\$A3)*(\$C\$2:\$C2=\$C3)),\$B\$2:\$B2),
MAX(INDEX((\$A\$2:\$A2=\$A3)*\$B\$2:\$B2,0))+1))

Conversely, the formula in D3, copied down rows, is:

=IF(\$A3<>\$A2,1,
IFERROR(LOOKUP(2,1/((\$A\$2:\$A2=\$A3)*(\$C\$2:\$C2=\$C3)*(\$E\$2:\$E2=\$E3)),\$D\$2:\$D2),
MAX(INDEX((\$A\$2:\$A2=\$A3)*(\$C\$2:\$C2=C3)*\$D\$2:\$D2,0))+1))

# Re: Creating ID based on numerous cell content

Thank you @Twifoo !

# Re: Creating ID based on numerous cell content

You're very much welcome!