SOLVED

Excel counting

%3CLINGO-SUB%20id%3D%22lingo-sub-1472039%22%20slang%3D%22en-US%22%3EExcel%20counting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1472039%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20excel%20community%20and%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F156456%22%20target%3D%22_blank%22%3E%40Faraz%20Shaikh%3C%2FA%3E%26nbsp%3B(thank%20you%20very%20much%20for%20helping%20with%20previous%20topic)%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ecould%20you%20please%20help%20with%20next%20question%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%2C%20I%20have%20column%20of%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22DShamanov_0-1592422008748.png%22%20style%3D%22width%3A%2086px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F199312iBC7CF5D27EAE3F6E%2Fimage-dimensions%2F86x260%3Fv%3D1.0%22%20width%3D%2286%22%20height%3D%22260%22%20title%3D%22DShamanov_0-1592422008748.png%22%20alt%3D%22DShamanov_0-1592422008748.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Ewhere%20each%202%20is%20%22LS%22%3C%2FP%3E%3CP%3Eand%3C%2FP%3E%3CP%3E0%20is%20%225.5%201BL%22%20or%20%225.5%202BL%22%20(alternating%20with%20each%20other)%3C%2FP%3E%3CP%3EI%20would%20like%20to%20have%20output%20from%20formula%3A%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E5.5%201BL%201%3CBR%20%2F%3E5.5%20LS%201%3CBR%20%2F%3E5.5%202BL%201%3CBR%20%2F%3E5.5%201BL%202%3CBR%20%2F%3E5.5%20LS%202%3CBR%20%2F%3E5.5%20LS%203%3CBR%20%2F%3E5.5%20LS%204%3CBR%20%2F%3E5.5%202BL%202%3CBR%20%2F%3E5.5%201BL%203%3CBR%20%2F%3E5.5%20LS%205%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3EDenis%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1472039%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1472144%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20counting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1472144%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F701914%22%20target%3D%22_blank%22%3E%40DShamanov%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EC1%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3D%225.5%20%22%26amp%3BIF(A1%3D2%2C%22LS%22%2CCHOOSE(MOD(COUNTIFS(A%241%3AA1%2CA1)%2C2)%2B1%2C%222BL%22%2C%221BL%22))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3ED1%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DCOUNTIFS(C%241%3AC1%2CC1)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1472205%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20counting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1472205%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F701914%22%20target%3D%22_blank%22%3E%40DShamanov%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFind%20below%20solution%20for%20your%20query.%3C%2FP%3E%3CP%3EYou%20need%20to%20construct%20your%20formula%20using%20helper%20columns%20that%20will%20give%20you%20desired%20results.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EFirst%20helper1%20%5BColumn%20B%5D%20formula%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIF(A2%3D2%2C%225.5%20LS%22%2C%225.5%201BL%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ESecond%20helper2%20%5BColumn%20C%5D%20formula%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIF(RIGHT(B2%2C2)%3D%22LS%22%2CB2%2CIF(MOD(COUNTIF(%24B%242%3AB2%2CB2)%2C2)%3D1%2CB2%2C%225.5%202BL%22))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EResult%20%5BColumn%20D%5D%20Formula%3C%2FSTRONG%3E%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DC2%26amp%3B%22%20%22%26amp%3BCOUNTIF(%24C%242%3AC2%2CC2)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EOutput%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%222020-06-17_23-13-59.png%22%20style%3D%22width%3A%20534px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F199329i5F3D04937616E36A%2Fimage-dimensions%2F534x486%3Fv%3D1.0%22%20width%3D%22534%22%20height%3D%22486%22%20title%3D%222020-06-17_23-13-59.png%22%20alt%3D%222020-06-17_23-13-59.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ERegards%2C%20Faraz%20Shaikh%20%7C%20MCT%2C%20MIE%2C%20MOS%20Master%2C%20Excel%20Expert%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CEM%3E%3CFONT%20color%3D%22%23808080%22%3EIf%20you%20find%20the%20above%20solution%20resolved%20your%20query%20don't%20forget%20mark%20as%26nbsp%3B%3CSPAN%3EOfficial%20Answer%26nbsp%3Bto%20help%20the%20other%20members%20find%20it%20more%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FEM%3E%3C%2FP%3E%3CP%3E%E2%80%83%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New 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
Highlighted
Best Response confirmed by DShamanov (New 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)

 

Highlighted

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