Generate a unique ID of specific format

[pomatrix]

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

[_lh]

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

[pomatrix]

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

[pomatrix]

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

[_lh]

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")),"")

[pomatrix]

Perfect! Thanks again _hl