Generating unique ID's

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.

Thanks,
Cliff