Avatar of D Patel
D Patel
Flag for India asked on

Excel : Auto Unique Number Generation

Hi Experts,

I need to generate unique number as per the following format...

This Unique Number comprises of 4 sections.
Suppose, Number is "E17100007" (which is unique)

1. E is first character from Column B
2. 17 is 2 digit Year from Date Column (C)
3. 10 is 2 digit Month from Date Column (C)
4. 0007 is four digit number which will grow by 1 within its group. (Consider that this number does not exceed 9999 limit)

Where group is 'E1710' and number will be get incremented but if group changes then it will start from 0001.

When I use this formula it reset all the numbers falls after it.

It should not be actually.

It should first find out the maximum and and then increment. All the previously generated numbers should intact.

How to achieve the task? (If it is VBA than it will be better)

Sample file is attached?

Regards,
D Patel
UniqueNumber.xlsx
VBAMicrosoft OfficeMicrosoft Excel

Avatar of undefined
Last Comment
Mike in IT

8/22/2022 - Mon
Mike in IT

If you want a VBA answer then you could use a User Defined Function (UDF) like this:
Function UniqueNumber(D As Date, Cat As String)
    Dim Begin As Variant
    Begin = Left(Cat, 1) & Right(Year(D), 2) & Format(Month(D), "00")
    Dim Formula()
    Dim Counter As Integer
    Counter = 1
    Formula = ActiveSheet.Range("E2:E" & ActiveCell.Row - 1).Value
    For i = 1 To UBound(Formula)
        If Begin = Left(Formula(i, 1), 5) Then
            Counter = Counter + 1
        End If
    Next i
    UniqueNumber = Begin & Format(Counter, "0000")
End Function

Open in new window

Then to use it you will type this into the column that you want showing the results:
=UniqueNumber(C2,B2)

Open in new window

And you will get a unique number for each group incrementing by 1.
SOLUTION
Subodh Tiwari (Neeraj)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
D Patel

ASKER
@Subodh Tiwari (Neeraj) :

insert-record.JPG
What if I insert record in middle of the list?

The formula will renumber all the list which falls after it.

after-insert.JPG
Here I want the new number should be inserted.

desired.JPG
D Patel

ASKER
@ Mike in IT :

notgenerating.JPG
Number not generating (showing all groups as "0001")

please suggest if anything remain from my side.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Subodh Tiwari (Neeraj)

Why would you insert rows in between? But if want to insert the rows all you need is to copy the formula from the row above to down the rows that will update the unique numbers in the rows below .
D Patel

ASKER
But once assigned the number should not change....
Subodh Tiwari (Neeraj)

I don't think that's possible as once you insert rows in between and fill the new rows, the formulas below will be automatically updated.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Mike in IT

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
D Patel

ASKER
Thank you for your support.
Mike in IT

You are welcome. Glad to help.