MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Generating unique ID's

Posted by Cliff on September 18, 2001 8:29 AM

I have data in an Excel97 spreadsheet that will be converted to an Access database. One of the things I need to do before converting is to generate a unique ID for each record according to a specific format so it matches data other databases.

Specifically, each ID must be eight characters in length, and will consist of the first four letters of the last name, the first initial of the first name, a single-digit numerical code, and a double-digit number sequential number to differentiate between ID's that have the same first six characters. The ID's need to be padded when last names are less than four characters and/or first names are missing. Additionally, apostrophes need to be stripped.

For example:
Barrett, Marty A. >> barrm001
Barrett, Marty B. >> barrm002

I know how to do do the first six characters to meet all my requirements, but I don't know how to easily ensure that the ID's remain unique when I add the last two digits. The data is already entered, so I'm going to use formulas. How can I be sure that when I drag the formulas down that barrrett, Marty A. will hav 01 appended while Barrett, Mart B. will have 02 added? Any suggestions on the easiest way to handle this? I'm dealing with thousands of records and I absolutely cannot change the ID format.


Posted by Mark W. on September 18, 2001 8:43 AM

Assuming that your names are in column A beginning
on row 1 you can insert new columns B and C. Enter
the formula, =LOWER(LEFT(A1,4)&MID(A1,FIND(", ",A1)+2,1)),
into B1 and copy down. Enter the formula,
=B1&TEXT(COUNTIF($B$1:B1,B1),"000"), into cell C1
and copy down.

Posted by Cliff on September 18, 2001 12:12 PM

That works, thank you very much.