Apr 17 2019 07:30 PM
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 :)
Apr 17 2019 10:26 PM
Apr 18 2019 02:04 PM
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
Apr 21 2019 07:42 PM
Hi @Twifoo does my response make sense?
Apr 21 2019 08:52 PM
Apr 22 2019 10:00 PM
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)))
Apr 23 2019 04:20 PM
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 #
Apr 23 2019 05:18 PM
Apr 23 2019 05:36 PM
@Twifooplease see attached
Apr 23 2019 10:36 PM - edited Apr 23 2019 11:48 PM
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))