Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Generate a unique ID of specific format

  1. #1
    Join Date
    23rd April 2012
    Posts
    7

    Generate a unique ID of specific format

    Good day all,

    I need to generateuniqueID references in a Non Conformance register & require this ref. to be relative to a particular cell e.g. If a cell in a data validated column has "ApexFab" the first number generated would be "AF-001", if "LewisSteel" the first number would be "LS-001" & if subsequently down the column "ApexFab" is used again this would create "AF-002", any assistance with this would be greatly appreciated, thank you all & have a great day

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    8th April 2012
    Posts
    33

    Re: How to generate a unique ID

    Here's the most efficient way I can think of doing it without using VBA to parse the text to find the capitalised letters.

    You mentioned the reference text is from a validation list, so create a helper column next to the validation source with the following array formula (you could do it next to every item in your data but array formulas are slow and processor intensive if you have lots of them). The formula assumes the first letter of the reference string is one you want and then includes the next capitalised letter as well. You can then use COUNTIF to find out how many occurrences of the same reference text have appeared previously and use CONCATENATE and TEXT to format the two values as you require into your unique ID column.

    The array formula is: {=LEFT(E2,1)&MID(E2,SMALL(FIND(0,SUBSTITUTE(E2,CHAR(ROW(INDIRECT("65:90"))),0)&0),2),1)}
    (enter this with control,shift,enter and do not type in the curly braces)

    The formula to reference this in your output column should be the following, which can be copied down as far as needed: =CONCATENATE(VLOOKUP(B2,refName,2,FALSE),"-",TEXT(COUNTIF($B$2:B2,B2),"000"))
    (where B2 is the first text cell; you will also need to change the first range cell in the COUNTIF depending on where your data starts, I've named the data validation area "refName" for clarity)

    Here's an example file which hopefully makes more sense. I hope it helps.

    UniqueID.xlsx

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    23rd April 2012
    Posts
    7

    Re: How to generate a unique ID

    That more than helped...worked like a charm! Can't thank you enough!

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    23rd April 2012
    Posts
    7

    Re: How to generate a unique ID

    Hi again _hl,

    The only problem with the CONCATENATE formula is it does not ignore blanks, when referenced down the column it returns "#NA" as the value...can the formula be adapted to ignore blanks? Thank you again for your assistance _hl,

    Regards Marco

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    8th April 2012
    Posts
    33

    Re: How to generate a unique ID

    Try wrapping the formula in an IFERROR and returning an empty string (or whatever else you want):

    =IFERROR(CONCATENATE(VLOOKUP(B16,refName,2,FALSE),"-",TEXT(COUNTIF($B$2:B16,B16),"000")),"")

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    23rd April 2012
    Posts
    7

    Re: How to generate a unique ID

    Perfect! Thanks again _hl

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Generate a list of unique values
    By Sissyfoo in forum EXCEL HELP
    Replies: 10
    Last Post: February 28th, 2011, 04:41
  2. Add & Generate Unique Numbers In Column
    By peterski95 in forum EXCEL HELP
    Replies: 3
    Last Post: March 7th, 2009, 04:05
  3. Generate Unique Random Names
    By drltr6 in forum EXCEL HELP
    Replies: 3
    Last Post: February 20th, 2007, 08:20
  4. Generate Unique Log in Passwords
    By tochybaja in forum EXCEL HELP
    Replies: 5
    Last Post: August 28th, 2006, 16:45
  5. Generate Unique ID
    By cmmaloney in forum EXCEL HELP
    Replies: 4
    Last Post: June 17th, 2006, 03:05

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
  •  
porno