Adding a unique number to duplicate cells in Excel

1
votes

I have a single column A with 7000 rows. There are some duplicates among the data, like:

  1. john-smith
  2. pattie-walk
  3. john-doe
  4. john-smith
  5. anna-hey

I need to append each duplicate entry with number, so john-smith should look like john-smith-01. If multiple john-smith exists it should keep adding numbers as -02,-03,-04 and so on for each duplicate entry. But also, the unique cells should be copied as they were.

  1. john-smith-01
  2. pattie-walk
  3. john-doe
  4. john-smith-02
  5. anna-hey

Any help with the formula is appreciated.

2
excelduplicates

2 Answers

1
votes

If you can live withouth the "." after the number, use this code. Origninally i have this going from cell A5 down to some thousands. This checks if something is in cell C5 and counts all entries up to that point. So this is ready for drag :)

=IF($C5<>"",COUNTA($C$5:$C5),"")

Edit: And if nothing is in the cell, there is no number shown.

1
votes

This is the solution

=IF(B1<>1;CONCATENATE(A1;"-";B1);A1)

Worked my way. Thanks Simon for the idea you gave me.