Excel Creating Unique Identifiers
Imagine that we have a list of names such as:
And we want to give each one a unique identifier:
So that the first name Bob has the identifier 1, and the next Mark has the identifier 2. This can be achieved by the following process.
We assign Bob (i.e the first person in the list) with an identifier 1:
And then we insert the following formulae in the next cell below this and copy down:
=IF(ISNA(MATCH(B7,B6:$B$6,0)),MAX(C6:$C$6)+1,VLOOKUP(B7,B6:$C$6,2,FALSE))
So:
The formulae works by looking for an occurrence of the name to date. If it doesn’t exist then it finds the maximum value of the identifier to date and adds one to give a new identifier. If a name does exist then a lookup is done to find the identifier for that name.
If more names are added at the bottom or in the middle and the formulae copies down, it still gives a distinct identifier.
Get answers right away at our AE Excel Support Forums!
Are you sure your equation is correct? Shouldn’t the absolute cells be at the beginning of each of the arrays?
Thus, the correct equation:
=IF(ISNA(MATCH(B7,$B$6:B6,0)),MAX($C$6:C6)+1,VLOOKUP(B7,$B$6:C6,2,FALSE))
@jason
either formula will return the same result. In the original it is pinning the top right of the range, yours will pin the top left.
If you try it then look at the formulas you’ve dragged down, you’ll notice a change in the absolute ranges.
Jason
I assume this only works on 2007, I’m using Excel 2003 & can’t seem to get it to work.
This is great, thanks for your help!
Hello,
I am trying to find a very similar formula except using 2 columns of data, e.g.:
Invoice Number Vendor
I want add a unique identifier where the Invoice number is the same however the Vendor is different and if both are the same then use the same identifier.
I tried to use the formula and add a second column and it does not seem to work. Please help
Thank You
Hi,
I tried using this formula to generate unique IDs for a list of 400,000 names which has almost 16,000 unique names. But as soon as I reached 30,000 the excel either stops responding or urges to stop the program saying it cannot handle too long formula.
Can you please help me out of such a challenging task.