• 545K Members
• 5,493 Online
• 650K Conversations
SOLVED

New Contributor

# Possible to do this with a single formula?

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

# Re: Possible to do this with a single formula?

For such data scheme

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

# Re: Possible to do this with a single formula?

Thank you!  Works like a charm

Highlighted

# Re: Possible to do this with a single formula?

@PI314 , you are welcome