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

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

VBAMicrosoft OfficeMicrosoft Excel

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.

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 .

But once assigned the number should not change....

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.

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.

Thank you for your support.

You are welcome. Glad to help.

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.