1. ## making duplicate cells unique by adding counter

I have a large spreadsheet with two columns. The second column contains a lot of duplicates. I would like to add a counter to each duplicate cell, thus making each unique.

The current contents of the cells in column two are, say:

abcd
abcd
abcd

I would like to end up with:

abcd
abcd-1
abcd-2

If I were a programmer, I would write a macro that loops through the spreadsheet, finds the duplicate cells and adds a counter to each, until all cells were unique. Unfortunately I can't write such code.

I would be very grateful if someone could help me.

Regards
useful

2. ## Vba

U,

This code will add the counter as you described to the duplicates.

Hope you find it useful, Useful.

Maud

Counter1.png Counter2.png
Code:
```Public Sub AddCounter()
LastRow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row  'FIND LAST ROW
counter = 1
For I = 2 To LastRow  'CYCLE THROUGH ALL VALUES
base = Cells(I, 2).Value  'GET BASE VALUE
For K = I + 1 To LastRow  'CYCLE THROUGH REMAINING VALUES
compare = Cells(K, 2).Value  'GET VALUE TO COMPARE TO BASE
If base = compare Then  'IF MATCH THEN...
Cells(K, 2).Value = compare & "-" & counter  'ADD THE COUNTER
counter = counter + 1  'INCREMENT COUNTER
End If
Next K
counter = 1  'RESET COUNTER
Next I
End Sub```

3. ## The Following User Says Thank You to Maudibe For This Useful Post:

useful (2013-07-16)

4. Hi Maud

Thank you for the prompt response and the code. You are wonderful, writing code that doesn't even require the column sorted to have all the duplicates together in sequence.

Thank you for enabling me to be useful.

Regards
useful

5. Useful,

You were right on the money with the logic to do it!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•