SOLVED
Home

Possible to do this with a single formula?

%3CLINGO-SUB%20id%3D%22lingo-sub-1117251%22%20slang%3D%22en-US%22%3EPossible%20to%20do%20this%20with%20a%20single%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1117251%22%20slang%3D%22en-US%22%3E%3CP%3EI've%20got%20a%20table%20with%20multiple%20lines%20of%20repeated%20words.%20For%20example%3A%3C%2FP%3E%3CP%3E%3CFONT%20face%3D%22courier%20new%2Ccourier%22%20size%3D%221%202%203%204%205%206%207%22%3Eaaaaaaaa%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20face%3D%22courier%20new%2Ccourier%22%20size%3D%221%202%203%204%205%206%207%22%3Eaaaaaaaa%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20face%3D%22courier%20new%2Ccourier%22%20size%3D%221%202%203%204%205%206%207%22%3Eaaaaaaaa%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20face%3D%22courier%20new%2Ccourier%22%20size%3D%221%202%203%204%205%206%207%22%3Ebbbbbbb%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20face%3D%22courier%20new%2Ccourier%22%20size%3D%221%202%203%204%205%206%207%22%3Ebbbbbbb%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20face%3D%22courier%20new%2Ccourier%22%20size%3D%221%202%203%204%205%206%207%22%3Eccccccccc%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20face%3D%22courier%20new%2Ccourier%22%20size%3D%221%202%203%204%205%206%207%22%3Eddddddd%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20face%3D%22courier%20new%2Ccourier%22%20size%3D%221%202%203%204%205%206%207%22%3Eddddddd%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20face%3D%22courier%20new%2Ccourier%22%20size%3D%221%202%203%204%205%206%207%22%3Eddddddd%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20face%3D%22courier%20new%2Ccourier%22%20size%3D%221%202%203%204%205%206%207%22%3Eddddddd%3C%2FFONT%3E%3C%2FP%3E%3CP%3E....%3C%2FP%3E%3CP%3EWhat%20I'd%20like%20to%20get%20is%20a%201%20in%20the%20column%20next%20to%20the%20a's%2C%20a%202%20by%20all%20the%20b's%2C%20a%203%20by%20all%20the%20c's%2C%204%20by%20d's%2C%20etc.%3C%2FP%3E%3CP%3ESo%20it%20would%20look%20like%20this%3A%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%222%22%3E%26nbsp%3B%3CU%3ECol%20A%26nbsp%3B%3C%2FU%3E%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%3CU%3ECol%20B%3C%2FU%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%222%22%3Eaaaaaaaa%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B1%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%222%22%3Eaaaaaaaa%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B1%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%222%22%3Eaaaaaaaa%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B1%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%222%22%3Ebbbbbbb%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B2%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%222%22%3Ebbbbbbb%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B2%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%222%22%3Eccccccccc%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B3%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%222%22%3Eddddddd%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B4%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%222%22%3Eddddddd%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B4%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%222%22%3Eddddddd%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B4%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%222%22%3Eddddddd%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B4%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20single%20formula%20that%20I%20can%20place%20in%20column%20B%26nbsp%3B%20to%20accomplish%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1117251%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-1117271%22%20slang%3D%22en-US%22%3ERe%3A%20Possible%20to%20do%20this%20with%20a%20single%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1117271%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F524681%22%20target%3D%22_blank%22%3E%40PI314%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20such%20data%20scheme%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20154px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F166072i9F1D42BED2F3C97A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ein%20B2%20it%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIFNA(INDEX(%24B%241%3A%24B1%2CMATCH(%24A2%2C%24A%241%3A%24A1%2C0))%2CMAX(%24B%241%3A%24B1)%2B1)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eor%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIFERROR(LOOKUP(2%2C1%2F(%24A%241%3AA1%3DA2)%2C%24C%241%3AC1)%2CMAX(%24C%241%3AC1)%2B1)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eor%20like%26nbsp%3Band%20drag%20it%20down%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1117300%22%20slang%3D%22en-US%22%3ERe%3A%20Possible%20to%20do%20this%20with%20a%20single%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1117300%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%26nbsp%3B%20Works%20like%20a%20charm%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1117963%22%20slang%3D%22en-US%22%3ERe%3A%20Possible%20to%20do%20this%20with%20a%20single%20formula%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1117963%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F524681%22%20target%3D%22_blank%22%3E%40PI314%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
PI314
New Contributor

I've got a table with multiple lines of repeated words. For example:

aaaaaaaa

aaaaaaaa

aaaaaaaa

bbbbbbb

bbbbbbb

ccccccccc

ddddddd

ddddddd

ddddddd

ddddddd

....

What I'd like to get is a 1 in the column next to the a's, a 2 by all the b's, a 3 by all the c's, 4 by d's, etc.

So it would look like this:

 Col A          Col B

aaaaaaaa       1

aaaaaaaa       1

aaaaaaaa       1

bbbbbbb       2

bbbbbbb       2

ccccccccc       3

ddddddd       4

ddddddd       4

ddddddd       4

ddddddd       4

 

Is there a single formula that I can place in column B  to accomplish this?

 

3 Replies
Highlighted

@PI314 

For such data scheme

image.png

in B2 it could be

=IFNA(INDEX($B$1:$B1,MATCH($A2,$A$1:$A1,0)),MAX($B$1:$B1)+1)

or

=IFERROR(LOOKUP(2,1/($A$1:A1=A2),$C$1:C1),MAX($C$1:C1)+1)

or like and drag it down

Highlighted
Solution

@Sergei Baklan 

Thank you!  Works like a charm

Highlighted

@PI314 , you are welcome