+ Reply to Thread
Results 1 to 18 of 18

Unique Identifiers That Don't Change?

  1. 07-14-2021, 07:32 PM #1
    Keyb0
    Keyb0 is offline
    Registered User
    Join Date
    09-15-2020
    Location
    Vancouver, Canada
    MS-Off Ver
    Office 365
    Posts
    5

    Unique Identifiers That Don't Change?

    Hello there!

    I'm having a real tough time figuring out how to create a unique identifier for content in a table (hours spent watching YouTube and browsing articles for an answer...) Basically, I'm looking for the output in column C to be CB-YEAR-0000 (so CB-2021-0001 for the first project of 2021).
    • The number could be random, or it could increase by 1 with each new entry, doesn't matter as long as we can guarantee there are no duplicates
    • As this will be a living table that constantly has new additions, the formula used must also create a unique ID when a new row is inserted
    • I will need to refer back to these unique identifiers months or years down the line, so they cannot change (ie: no RANDBETWEEN)

    I thought about using the CODE and LEFT functions to base the numeric component off the Project Name in column A, but this causes trouble when two projects start with the same word.

    Almost every effort I've made has ended up with a circular formula. The sad workaround is to have each project number be one higher than the row above, or match its own row number, but this means I can't insert rows without changing every one of the Unique IDs – and I don't trust my colleagues to follow my instructions if I tell them "only add rows at the bottom!!"

    Any suggestions would be very welcome - thanks in advance for trying this one out!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 07-14-2021, 07:58 PM #2
    wk9128
    wk9128 is offline
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2502 and WPS V2024(12.1.0.18543)
    Posts
    3,866

    Re: Unique Identifiers That Don't Change?

    Give it a try, I have put the code in the attachment file

    HTML Code: 
    Attached Files Attached Files
    Last edited by wk9128; 07-15-2021 at 05:58 AM.
    Reply With QuoteRegister To Reply
  3. 07-15-2021, 03:49 AM #3
    kvsrinivasamurthy
    kvsrinivasamurthy is offline
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,710

    Re: Unique Identifiers That Don't Change?

    Try this In C2

    =IF(A3="","","CB-"&YEAR(B3)&"-"&TEXT(ROW($A1),"00000"))
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.
    Reply With QuoteRegister To Reply
  4. 07-15-2021, 04:04 AM #4
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Unique Identifiers That Don't Change?

    This one restarts at 1 each year:

    =IF(A3="","","CB-"&YEAR(B3)&"-"&TEXT(SUMPRODUCT(--(YEAR(B$3:B3)=YEAR(B3))),"0000"))
    Attached Files Attached Files
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
    Reply With QuoteRegister To Reply
  5. 07-15-2021, 04:08 AM #5
    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 (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,387

    Re: Unique Identifiers That Don't Change?

    but this means I can't insert rows without changing every one of the Unique IDs
    I think this may well scupper the solutions already offered. It may be that you have to go down the VBA route for this.
    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. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    NB:     as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  6. 07-15-2021, 04:10 AM #6
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Unique Identifiers That Don't Change?

    Ah yes. I never understood the compulsion to insert rows...
    Reply With QuoteRegister To Reply
  7. 07-15-2021, 06:00 AM #7
    wk9128
    wk9128 is offline
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2502 and WPS V2024(12.1.0.18543)
    Posts
    3,866

    Re: Unique Identifiers That Don't Change?

    Update post #2 VBA code

    Cell C3 my formula as
    =IF(OR(A3="",B3=""),"","CB-"&YEAR(B3)&"-"&"000"&IF(B3="","",COUNT(B$3:B3)))


    VBA write like this , What am i wrong ?
    "=IF(OR(RC1="""",RC2=""""),"""",""CB-""&YEAR(RC2)&""-""&""000""&IF(RC2="""","""",COUNT(R3C2:RC2)))"
    Last edited by wk9128; 07-15-2021 at 06:12 AM.
    Reply With QuoteRegister To Reply
  8. 07-15-2021, 06:13 AM #8
    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 (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,387

    Re: Unique Identifiers That Don't Change?

    @wk9128

    Please DON'T do this! It makes the thread very difficult to follow.

    If you wish to change something, then make the change in a NEW post to the thread and leave the original (code in this case) alone.
    Reply With QuoteRegister To Reply
  9. 07-15-2021, 06:18 AM #9
    wk9128
    wk9128 is offline
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2502 and WPS V2024(12.1.0.18543)
    Posts
    3,866

    Re: Unique Identifiers That Don't Change?

    I also considered that POST#1 needs to insert ROW, which will cause the formula to break, so I wrote the code
    Reply With QuoteRegister To Reply
  10. 07-15-2021, 06:19 AM #10
    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 (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,387

    Re: Unique Identifiers That Don't Change?

    Did you understand my point? Please send me a private message if you need clarification, but you must NOT post-edit your posts in this way again, please.
    Reply With QuoteRegister To Reply
  11. 07-15-2021, 06:30 AM #11
    kvsrinivasamurthy
    kvsrinivasamurthy is offline
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,710

    Re: Unique Identifiers That Don't Change?

    Try this worksheet event.
    Please Login or Register  to view this content.
    Worksheetevent

    How to use the code
    Right click on Sheet tab --> view code
    Visual Basic (VB) window opens.
    Paste the code
    Close the VB window.
    Attached Files Attached Files
    Last edited by kvsrinivasamurthy; 07-15-2021 at 07:43 AM.
    Reply With QuoteRegister To Reply
  12. 07-15-2021, 12:42 PM #12
    Keyb0
    Keyb0 is offline
    Registered User
    Join Date
    09-15-2020
    Location
    Vancouver, Canada
    MS-Off Ver
    Office 365
    Posts
    5

    Re: Unique Identifiers That Don't Change?

    Quote Originally Posted by AliGW View Post
    I think this may well scupper the solutions already offered. It may be that you have to go down the VBA route for this.
    I feared as much...

    Quote Originally Posted by kvsrinivasamurthy View Post
    Try this worksheet event.
    Unfortunately this can result in identical values. For example, if the value CB-2021-0005 already exists, and a user inserts a new row between the 4th and 5th row of the table with the year 2021, that value will also show CB-2021-0005. Thank you for your effort!
    Reply With QuoteRegister To Reply
  13. 07-16-2021, 01:32 AM #13
    kvsrinivasamurthy
    kvsrinivasamurthy is offline
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,710

    Re: Unique Identifiers That Don't Change?

    Pl see file in post#11 once again. When the row is inserted and date entered in in B column C column gives new number not the same number as you have told.
    Last edited by kvsrinivasamurthy; 07-16-2021 at 01:58 AM.
    Reply With QuoteRegister To Reply
  14. 07-16-2021, 02:22 AM #14
    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 (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,387

    Re: Unique Identifiers That Don't Change?

    It doesn't work without a date, though:

    Excel 2016 (Windows) 32 bit
    A
    B
    C
    4
    		 nnn CB-1900-00002
    5
    		 AAA CB-1900-00002
    Sheet: Sheet1

    If there HAS to be a date, this is not a problem, but just thought I'd flag it.
    Reply With QuoteRegister To Reply
  15. 07-16-2021, 04:22 AM #15
    kvsrinivasamurthy
    kvsrinivasamurthy is offline
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,710

    Re: Unique Identifiers That Don't Change?

    Date is required because the year of date is to be included in the ID number.
    Reply With QuoteRegister To Reply
  16. 07-16-2021, 04:24 AM #16
    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 (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,387

    Re: Unique Identifiers That Don't Change?

    Perhaps you should add dates to the sample workbook, then???
    Reply With QuoteRegister To Reply
  17. 07-16-2021, 11:33 AM #17
    kvsrinivasamurthy
    kvsrinivasamurthy is offline
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,710

    Re: Unique Identifiers That Don't Change?

    Ref Post#1
    I'm looking for the output in column C to be CB-YEAR-0000 (so CB-2021-0001 for the first project of 2021).
    Op's condition.
    Reply With QuoteRegister To Reply
  18. 07-16-2021, 11:37 AM #18
    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 (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,387

    Re: Unique Identifiers That Don't Change?

    I know! In your sample workbook posted earlier there is one date - 14 May in the top row. From the OP’s requirement surely there should be dates in ALL rows? That’s the anomaly, for me.

    My point earlier was based on this workbook: if you add a row in the middle without a date, it fails. Why is there only one date in your workbook? What am I missing here?
    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. [SOLVED] Need unique identifiers based on list?
    By Katie620 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-17-2017, 02:33 PM
  2. using unique identifiers while performing function
    By txdude311 in forum Excel General
    Replies: 1
    Last Post: 07-26-2016, 06:17 PM
  3. Identify inconsistent unique identifiers
    By Brawnystaff in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-04-2016, 12:27 AM
  4. Index/Match - Retrieve data across unique sheets with unique column identifiers
    By eddievont in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-27-2014, 02:26 AM
  5. Sorting based on unique identifiers
    By excel_beginner2 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-04-2014, 10:09 AM
  6. How to merge different spreadsheets with no unique identifiers
    By newbieexcelgirl in forum Excel General
    Replies: 2
    Last Post: 01-17-2013, 11:42 AM
  7. Excel 2007 : adding values with unique identifiers
    By AntiC in forum Excel General
    Replies: 3
    Last Post: 01-27-2012, 10:52 AM

Tags for this Thread

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