How to use several COUNTA functions to create unique Identification Numbers

[llomax83]

I'm attempting to create a unique ID number for each entry in a long column of data.

The data set has 96 unique groups-8 main categories with 12 sub categories.
Each entry in the data set needs to have a unique identification number (ID).
In my mock ups, this number is a six digit number. This # is highlighted below.
The first number in the ID to come from the main category (1-8).
The next two numbers in the ID would come from the sub category (01-12).
The remaining three numbers are a unique value between 001-999.

I would like Excel to count each individual entry starting at 1 for each unique group.
Each time a new entry is made, Excel should look at the categories and decide which of 96 unique groups the entry belongs to, and assign it a number between 001 and 999 in this group.
I've tried to accomplish this with COUNTA and IF functions, but with 96 different counters running I haven't been successful.

Any suggestions for an elegant solution?

[gak67]

Try this formula:

Please Login or Register  to view this content.

[BoredWorker]

opps. too late...
change to other non related things,

if the column E and G is not necessary,

column E can eliminate, by using gak67 formula,

column G can remove by change the column F formula to
=IF($B16="","",TEXT(COUNTA($B$16:$B16),"000"))

[llomax83]

Exactly what I was looking for... thanks so much!