2

this may be a little difficult for me to explain. I have 1 column in excel that has duplicate entries i.e. more than one red and more than one blue. I need to compare this column against itself and increment each cell with the same word by 1.

For example if there was a list of 3 reds in the column and 2 blues in the same column it would need to be incremented as red1, red2, red3 and blue1, blue 2. I've been searching but so far have not found anything that works how I need it to.

I have sorted the column and just need to add a number that increments by 1 for the same cell entry.

The column I have is 5000 products (with many random duplicates) and if the products have the same name then they need to be incremented by 1.

Any help would be appreciated. Thank you and I hope this makes sense.

user2853470
  • 21
  • 1
  • 2

1 Answers1

6

You can use COUNTIF using a little trick with the range.

=COUNTIF($A$1:A1, A1)

And if you want to directly concatenate...

=A1&COUNTIF($A$1:A1, A1)

And drag the formula down.

Adjust the range accordingly.

Jerry
  • 70,495
  • 13
  • 100
  • 144