+ Reply to Thread
Results 1 to 10 of 10

Give Duplicate Alphanumeric Values a Unique Identifier

  1. 01-16-2018, 07:31 PM #1
    papgar
    papgar is offline
    Registered User
    Join Date
    01-21-2011
    Location
    Dallas, Texas
    MS-Off Ver
    Excel 2007
    Posts
    30

    Give Duplicate Alphanumeric Values a Unique Identifier

    I have a set of data with 302,000 rows that has one column that contains contract numbers, which can be alphanumeric and limited to 20 characters. I am importing this data into another system and cannot have duplicates. I have identified the duplicates, which total 1,276 out of the total of 302,000. I need a way to add a unique character at the end of each duplicate, which begins with the letter D, for duplicate, followed by 1 for the first duplicate, 2 for the second, etc.. I am attaching an example workbook of the data and what I want it to reflect for the duplicate values as an example. I would appreciate any help/advice anyone has.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 01-16-2018, 08:50 PM #2
    kersplash
    kersplash is offline
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Give Duplicate Alphanumeric Values a Unique Identifier

    I've added a new column with the formula.

    Not exactly what you wanted, but may be acceptable.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  3. 01-17-2018, 02:02 AM #3
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,830

    Re: Give Duplicate Alphanumeric Values a Unique Identifier

    Kersplash - sorry for off-topic interjection:

    Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

    I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

    Thanks again for all your hard work here!
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  4. 01-17-2018, 02:17 AM #4
    humdingaling
    humdingaling is offline
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Give Duplicate Alphanumeric Values a Unique Identifier

    Put formula in C10 and fill down
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by humdingaling; 01-17-2018 at 02:18 AM. Reason: file attached
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
    Reply With QuoteRegister To Reply
  5. 01-17-2018, 02:21 AM #5
    kersplash
    kersplash is offline
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Give Duplicate Alphanumeric Values a Unique Identifier

    Noted, it was difficult to explain how I did this one, but I do normally try and do this.
    Cheers
    Last edited by AliGW; 01-17-2018 at 02:22 AM. Reason: Unnecessary quotation removed.
    Reply With QuoteRegister To Reply
  6. 01-17-2018, 02:25 AM #6
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,830

    Re: Give Duplicate Alphanumeric Values a Unique Identifier

    If you get the ForumTools add-in for Excel, you can do this:

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    E
    F
    8
    		 Original
    9
    		 DATA Formula RESULT
    10
    2105217
    2105217
    2105217
    TRUE
    11
    2105217
    		 2105217D1
    2105217D1
    TRUE
    12
    2105217
    		 2105217D2
    2105217D2
    TRUE
    13
    812183101
    812183101
    812183101
    TRUE
    14
    812183101
    		 812183101D1
    812183101D1
    TRUE
    15
    8121178101
    8121178101
    8121178101
    TRUE
    16
    8121178101
    		 8121178101D1
    8121178101D1
    TRUE
    17
    8121178101
    		 8121178101D2
    8121178101D2
    TRUE
    18
    8121178101
    		 8121178101D3
    8121178101D3
    TRUE
    19
    20
    		 ALPHANUMBERIC
    21
    		 LIMITED TO 20 CHARACTERS
    Sheet: Sheet1

    Excel 2016 (Windows) 32 bit
    C
    10
    =IF(COUNTIF($A$10:A10,A10)>1,A10&"D"&COUNTIF($A$10:A10,A10)-1,A10)
    Sheet: Sheet1

    Link here: http://excelmatters.com/excel-forums/ (penultimate paragraph of the blog entry).
    Reply With QuoteRegister To Reply
  7. 01-17-2018, 03:35 AM #7
    kersplash
    kersplash is offline
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Give Duplicate Alphanumeric Values a Unique Identifier

    Thanks AliGW
    Reply With QuoteRegister To Reply
  8. 01-17-2018, 03:18 PM #8
    papgar
    papgar is offline
    Registered User
    Join Date
    01-21-2011
    Location
    Dallas, Texas
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Give Duplicate Alphanumeric Values a Unique Identifier

    I am not sure I understand your reply, as I posted the following in addition to the workbook: "I have a set of data with 302,000 rows that has one column that contains contract numbers, which can be alphanumeric and limited to 20 characters. I am importing this data into another system and cannot have duplicates. I have identified the duplicates, which total 1,276 out of the total of 302,000. I need a way to add a unique character at the end of each duplicate, which begins with the letter D, for duplicate, followed by 1 for the first duplicate, 2 for the second, etc.. I am attaching an example workbook of the data and what I want it to reflect for the duplicate values as an example. I would appreciate any help/advice anyone has."
    Reply With QuoteRegister To Reply
  9. 01-17-2018, 03:25 PM #9
    papgar
    papgar is offline
    Registered User
    Join Date
    01-21-2011
    Location
    Dallas, Texas
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Give Duplicate Alphanumeric Values a Unique Identifier

    Ali, I believe I misunderstood your response to my thread, as I now see where you were not responding to me. sorry about that!
    Reply With QuoteRegister To Reply
  10. 01-18-2018, 10:45 AM #10
    papgar
    papgar is offline
    Registered User
    Join Date
    01-21-2011
    Location
    Dallas, Texas
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Give Duplicate Alphanumeric Values a Unique Identifier

    Ali, thank you this worked great.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Create ID through values in Table + unique identifier (VBA)
    By sqrcbc in forum Excel General
    Replies: 2
    Last Post: 09-12-2017, 06:45 AM
  2. Counting if a unique identifier corresponds to another unique identifier more than once
    By alekos7 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-16-2017, 07:53 PM
  3. Need a formula to create a unique identifier for Duplicate names
    By asharma9 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-21-2015, 04:37 PM
  4. Merging Rows on a Unique Identifier while Keeping Unique Data and Adding Values Together
    By CPAIT in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-29-2015, 04:08 PM
  5. Assign a unique random alphanumeric value to a column of random alphanumeric values
    By bardo321 in forum Excel General
    Replies: 2
    Last Post: 11-06-2014, 05:07 PM
  6. Formula for making text in column A and D give a unique identifier in column B
    By steveb34 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-16-2014, 05:13 PM
  7. [SOLVED] add increment identifier to unique values
    By hluk in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-19-2013, 04:44 PM

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1