Forum Discussion
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 ...
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.
- 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))