Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: add number to duplicated to create unique entry

  1. #1
    New Member
    Join Date
    Sep 2008
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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
    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!!

  2. #2
    Board Regular Weaver's Avatar
    Join Date
    Sep 2008
    Posts
    5,196
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #3
    New Member
    Join Date
    Sep 2008
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #4
    Board Regular Weaver's Avatar
    Join Date
    Sep 2008
    Posts
    5,196
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #5
    New Member
    Join Date
    Sep 2008
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #6
    New Member
    Join Date
    Sep 2008
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #7
    Board Regular Weaver's Avatar
    Join Date
    Sep 2008
    Posts
    5,196
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #8
    New Member
    Join Date
    Sep 2008
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #9
    Board Regular Weaver's Avatar
    Join Date
    Sep 2008
    Posts
    5,196
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: add number to duplicated to create unique entry

    No problem! I appreciate the challenge

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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