Creating ID based on numerous cell content

Copper Contributor

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...

 

DateID1Shot #ID2Product
1/04/20191ABC1100200
1/04/20191ABC1100200
1/04/20192DEF1100200
1/04/20192DEF2300400
1/04/20191ABC2200100
2/04/20191ABC1200200
2/04/20192GHI1200200
2/04/20192GHI2400500
2/04/20193DEF1200200
2/04/20191ABC2300400

 

Thank you :)

 

@Twifoo

@Sergei Baklan 

 

12 Replies
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.

@Twifoo 

 

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

Hi @Twifoo does my response make sense? 

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

Thank you!! @Twifoo 

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)))

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 #

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.

@Twifooplease see attached 

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))

Thank you @Twifoo !

You're very much welcome!