Jun 17 2020 12:37 PM
Dear excel community and @Faraz Shaikh (thank you very much for helping with previous topic),
could you please help with next question:
For example, I have column of:
where each 2 is "LS"
and
0 is "5.5 1BL" or "5.5 2BL" (alternating with each other)
I would like to have output from formula:
5.5 1BL 1
5.5 LS 1
5.5 2BL 1
5.5 1BL 2
5.5 LS 2
5.5 LS 3
5.5 LS 4
5.5 2BL 2
5.5 1BL 3
5.5 LS 5
Thank you in advance!
Regards,
Denis
Jun 17 2020 12:58 PM
SolutionC1
="5.5 "&IF(A1=2,"LS",CHOOSE(MOD(COUNTIFS(A$1:A1,A1),2)+1,"2BL","1BL"))
D1
=COUNTIFS(C$1:C1,C1)
Jun 17 2020 01:20 PM
Hi @DShamanov,
Find below solution for your query.
You need to construct your formula using helper columns that will give you desired results.
First helper1 [Column B] formula
=IF(A2=2,"5.5 LS","5.5 1BL")
Second helper2 [Column C] formula
=IF(RIGHT(B2,2)="LS",B2,IF(MOD(COUNTIF($B$2:B2,B2),2)=1,B2,"5.5 2BL"))
Result [Column D] Formula
=C2&" "&COUNTIF($C$2:C2,C2)
Output
Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert
If you find the above solution resolved your query don't forget mark as Official Answer to help the other members find it more
Jun 17 2020 12:58 PM
SolutionC1
="5.5 "&IF(A1=2,"LS",CHOOSE(MOD(COUNTIFS(A$1:A1,A1),2)+1,"2BL","1BL"))
D1
=COUNTIFS(C$1:C1,C1)