Forum Discussion

Nattiemick's avatar
Nattiemick
Copper Contributor
Dec 13, 2021

Creating a unique serial number in a database

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
Formulas and Functions

2 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Dec 13, 2021

    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.

  • tauqeeracma's avatar
    tauqeeracma
    Iron Contributor
    Dec 13, 2021

    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

Resources