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
LVL 7
D PatelD Patel, Software EngineerAsked:
Who is Participating?
 
Mike in ITIT System AdministratorCommented:
Here you can try this
Function UniqueNumber(D As Date, Cat As String) As String
    Dim Begin As Variant
    Begin = Left(Cat, 1) & Right(Year(D), 2) & Format(Month(D), "00")
    If ActiveCell.Row = 2 Then
        UniqueNumber = Begin & "0001"
        Exit Function
    End If
    Dim Formula()
    Dim Counter As Integer
    Counter = 1
    Formula = ActiveSheet.Range("E2:E" & ActiveCell.Row).Value
    For i = 1 To UBound(Formula) - 1
        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


It does work for me, though I have it in column G, not D or E.

As for being able to insert rows in the middle of your data and having the formulas not recalculate, that is not possible. Every time you insert data they will all recalculate and update all the numbers below. If you don't want them to change then once you have your numbers then you will have to do a copy/paste values so that it is just a value and not a formula. Then you can do an insert, but my code will not take into account anything below it for generating the unique numbers.
0
 
Mike in ITIT System AdministratorCommented:
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.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may try something like this....

Either in C2 or in D2
=LEFT(B2,1)&RIGHT(YEAR(C2),2)&TEXT(MONTH(C2),"00")&TEXT(SUMPRODUCT(--((LEFT(B$2:B2,1)&RIGHT(YEAR(C$2:C2),2)&TEXT(MONTH(C$2:C2),"00"))=LEFT(B2,1)&RIGHT(YEAR(C2),2)&TEXT(MONTH(C2),"00"))),"0000")

Open in new window

and copy it down.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
D PatelD Patel, Software EngineerAuthor Commented:
@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
0
 
D PatelD Patel, Software EngineerAuthor Commented:
@ Mike in IT :

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

please suggest if anything remain from my side.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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 .
0
 
D PatelD Patel, Software EngineerAuthor Commented:
But once assigned the number should not change....
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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.
1
 
D PatelD Patel, Software EngineerAuthor Commented:
Thank you for your support.
0
 
Mike in ITIT System AdministratorCommented:
You are welcome. Glad to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.