add number to duplicated to create unique entry

HarleyBigDog

New Member
Joined
Sep 11, 2008
Messages
5
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
JohnAdams
JohnAdams2
JohnAdams3
JohnAdams4

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.

Thank you in advance for your help!!
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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))
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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!!
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
It works GREAT!!! Thank you for your assistance in this!! You have saved hours of headaches!! I really appreciate it!
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top