Forum Discussion
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)))
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're very much welcome!
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))- 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.