Numbering duplicate values fulfilling two conditions

[jsto7380]

Dear community


I'm looking for a way to number an item in a way that corresponds to associated conditions. In the example below I would like to achieve that the item "a" (id 1 visit 3, rows 4 and 5) becomes a1 and a2, and the same (a1 and a2) corresponding to id 2, visit 4 (rows 12 and 13).


id visit item

1 1 a

1 2 a

1 3 a

1 3 a

1 3 b

1 4 a

1 5 a

2 1 a

2 2 a

2 3 a

2 4 a

2 4 a

2 4 b

2 5 a

2 6 a


I imagine a formula is required with COUNTIF(E$2:E...,E...) but I'm not sure how to start counting by id and visit.


Many thanks in advance,

 

[Luke M]

I believe this is what you are looking for:

=IF(SUMPRODUCT(--($A$2:$A$20&$B$2:$B$20&$C$2:$C$20=A2&B2&C2))>1,C2&SUMPRODUCT(--($A$2:$A2&$B$2:$B2&$C$2:C2=A2&B2&C2)),C2)

 

[NARAYANK991]

Hi ,


I think you are right ; you can try these as alternatives :


=IF(A2=A3,IF(B2=B3,C2&COUNTIFS($C$2:C2,C2,$A$2:A2,A2,$B$2:B2,B2),C2),C2)


=IF(AND(A2=A3,B2=B3),C2&COUNTIFS($C$2:C2,C2,$A$2:A2,A2,$B$2:B2,B2),C2)


Narayan

 

[jsto7380]

Thank you! Worked perfectly