SOLVED

Excel counting

Copper Contributor

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:

DShamanov_0-1592422008748.png

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

2 Replies
best response confirmed by DShamanov (Copper Contributor)
Solution

@DShamanov 

C1

="5.5 "&IF(A1=2,"LS",CHOOSE(MOD(COUNTIFS(A$1:A1,A1),2)+1,"2BL","1BL"))

D1

=COUNTIFS(C$1:C1,C1)

 

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

2020-06-17_23-13-59.png

 

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

1 best response

Accepted Solutions
best response confirmed by DShamanov (Copper Contributor)
Solution

@DShamanov 

C1

="5.5 "&IF(A1=2,"LS",CHOOSE(MOD(COUNTIFS(A$1:A1,A1),2)+1,"2BL","1BL"))

D1

=COUNTIFS(C$1:C1,C1)

 

View solution in original post