Duplicate Sort by Numbers

[markman2]

Hello fellow excelers, I am facing an issue that i can't get around easily. I have data and i would like to number duplicates next to the data so that i can append the cells that are duplicates easily. The data i have is in the following cell format -

Red
Red
Red
Green
Green
Green
Green
Blue
Yellow
Yellow

What i would like is to output serial next to the cells based on the cell uniqueness as in -

Red 1
Red 2
Red 3
Green 1
Green 2
Green 3
Green 4
Blue 1
Yellow 1
Yellow 2

Kindly let me know if there's something to be done here. Thanks, Mark.

 

[Matty]

Assuming your data is in A1:A10, try:

=COUNTIF(A$1:A1,A1)

In B1, and copy down.

Matty

 

[markman2]

MIND BLOWN!
thank you, great technique! so flawless

To demonstrate what i was up to - i was using =COUNTIF(E:E,E2)>1, then >2 then >3 then >4 in different rows, and getting True/False, then changing the True to that number... i love excel!

 

[Matty]

MIND BLOWN!
thank you, great technique! so flawless

To demonstrate what i was up to - i was using =COUNTIF(E:E,E2)>1, then >2 then >3 then >4 in different rows, and getting True/False, then changing the True to that number... i love excel!

Glad to help. Keep EXCELing...

Matty

 

[susjoe3]

Assuming your data is in column A, try these formulas in columns B and C respectively:

=IF((A3=A2),B2+1,1) =A3&" "&B3