Jan 19 2020 01:57 PM
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?
Jan 19 2020 02:46 PM
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
Jan 19 2020 04:43 PM
SolutionThank you! Works like a charm :)
Jan 19 2020 04:43 PM
Solution