Creating a unique serial number in a database

[Nattiemick]

So I'm hoping someone can help.

I have 2 employees entering data into a database.

What I am looking to do is, when they type information into a row it creates a serial number starting with their initials.

When that employee types in another row it will create another serial number again starting with their initials but having the next number in sequence.

Example:

Employee 1: Joe Bloggs (JB)
Employee 2: Steve Smith (SS)

Row 1: Joe Bloggs, serial number JB01
Row 2: Steve Smith, serial number SS01
Row 3: Joe Bloggs, serial number JB02
Row 4: Joe Bloggs, serial number JB03
Row 5: Steve Smith, serial number SS02

Is there a way to automate this?

I initially was planning on having a cell that generates the initials & then a cell that creates the number, then combine the two but I cannot work out have to have two running sequences in the same sheet dependant on who's name is selected.

Any help would be appreciated

[#2]

Hi @Nattiemick 

You may use the below formula to generate a unique serial number against each employee.

=LEFT(C2)&MID(C2,FIND(" ",C2,1)+1,1)&TEXT(ROW(C2),"0###")

Please refer to the attached file for more clarity.

Please let me know if it helps you.

Thanks

Tauqeer

[#3]

@Nattiemick 

Let's say the names begin in A2.

In B2, the initials:    =IF(A2="","",LEFT(A2)&MID(A2,FIND(" ",A2)+1,1))

In C2, the sequence number:    =IF(A2="","",COUNTIF(B$1:B2,B2))

In D2, the serial number:    =IF(A2="","",B2&TEXT(C2,"00"))

Select B2:D2 and fill down as far as you want.

See the attached sample workbook.