SOLVED

Possible to do this with a single formula?

Copper 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

@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

best response confirmed by PI314 (Copper Contributor)
Solution

@Sergei Baklan 

Thank you!  Works like a charm :)

@PI314 , you are welcome

1 best response

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

@Sergei Baklan 

Thank you!  Works like a charm :)

View solution in original post