The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

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

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

This Unique Number comprises of 4 sections.

Suppose, Number is

1.

2.

3.

4.

Where group is

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

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license!
Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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")
```

and copy it down.
```
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
```

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
VBA

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get this solution by purchasing an Individual license!
Start your 7-day free trial.

Open in new window

Then to use it you will type this into the column that you want showing the results:Open in new window

And you will get a unique number for each group incrementing by 1.