Thanks:  0
Likes:  0

1. ## add number to duplicated to create unique entry

I have looked over the forums for information on how to do this, and can not find anything. I have used conditional formatting to identify duplicates in my spreadsheet. I have a unique user ID, which combines the first and last name fields into one. However, in 10,000 there are a number of John Smiths, Joe Adams, etc. With smaller lists, we search manually, and then add a 1, 2, 3, etc to each duplicate to create a unique value for the User ID.

My question is:

Is there a way to check for duplicates, and then automatically add numbers to the duplicate values to make them unique? If so, how can this be done?

So that you will have this:

JohnSmith
JohnSmith2
JohnSmith3
JaneDoe
JaneDoe2

I think I have done a good job in illustrating what I would like to be able to do, but if there is still something that is unclear, I will post whatever clarification is needed.

2. ## Re: add number to duplicated to create unique entry

If your data starts in cell A1, paste this formula into cell B1 and copy down.

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

If you don't want first or single occurances to have a number, try this slightly more complicated version

=A1&IF(COUNTIF(A\$1:A1,A1)=1,"",COUNTIF(A\$1:A1,A1))

3. ## Re: add number to duplicated to create unique entry

I will give this a try! Thank you so much for the quick reply. I am also guessing that I won't need to use my Conditional Formatting with this, as it seems to already be included?

Thanks again!

4. ## Re: add number to duplicated to create unique entry

Hope it works ok.

I dunno about the conditional formatting - I'm not a big believer in using colour to indicate items in excel, mostly because none of the built in functions are able to refer to a cell's colour.

5. ## Re: add number to duplicated to create unique entry

I think this worked beautifully, in my test it did anyway. going to run it against the BIG ONE now, and will let you know. Thanks again for your assist on this. WOW what a big help!!

6. ## Re: add number to duplicated to create unique entry

OK, now they are asking that the numbering start at 1 and not 2. How can I achieve that with the code above? If it is not easy, that is fine, just wanted to check.

Thanks again

7. ## Re: add number to duplicated to create unique entry

Check my first post. The first (simpler) formula should do what you need. Unless you don't want to put numbers on unique entries, in which case, try

=A1&IF(COUNTIF(A\$1:A1,A1)=1,"",COUNTIF(A\$1:A1,A1)-1)

8. ## Re: add number to duplicated to create unique entry

It works GREAT!!! Thank you for your assistance in this!! You have saved hours of headaches!! I really appreciate it!

9. ## Re: add number to duplicated to create unique entry

No problem! I appreciate the challenge

## User Tag List

#### Posting Permissions

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