How to generate unique batch code which stays the same after adding and deleting new rows

[bsnow]

Hi all

I'm trying to come up with a formula or VBA code that will allow me to generate a unique new batch code once the 'Product' (in column A) has been selected and today's date has automatically populated (column B).

I already have the formula for populate today's date once a product is selected, however I need a formula that will generate a unique new 6 digit batch code in column D based on the date: MMYY followed by a sequential 2 digit next available number (i.e. 01, 02 etc.)

Example: the 1st batch made on April 1st 2022 would be 042201, next batch would be 042202, and the last 2 digits would revert back to 01 when the month changes.

Once the batch code is generated, it cannot change in that row when I then subsequent new row or delete rows.

Thanks in advance guys

 

[BSALV]

Map1
	A	B	C	D	E	F	G
1	product	DOM	Generated	Batchcode		next batchnumber	220393
2				220392
3				211298
4				220312
5
Blad1

Cell Formulas
Range	Formula
G1	G1	=MAX(TEXT(TODAY(),"jjmm")*100,MAX((LEFT(Tabel1[Batchcode],4)=TEXT(TODAY(),"jjmm"))*Tabel1[Batchcode]))+1

 

[bsnow]

Hi BSALV

Thanks for your help on this. I have replicated your formula and tweaked the way the date shows, but I'm struggling to show the batch code in column D.

Batch Generation.xlsm
	D
2
Sheet1

 

[bsnow]

Batch Generation.xlsm
	A	B	C	D	E	F	G	H
1	Product	DOM	Generated	Batch		next batch number	220301
2	QQQ	30-Mar-22	MW
3
4
5
6
7
Sheet1

Cell Formulas
Range	Formula
G1	G1	=MAX(TEXT(TODAY(),"YMM")*100,MAX((LEFT(Table1[Batch],4)=TEXT(TODAY(),"YMM"))*Table1[Batch]))+1
B2	B2	=IF(A2<>"",IF(B2<>"",B2,NOW()),"")

Cells with Data Validation
Cell	Allow	Criteria
A2	List	=Sheet2!$A$1:$A$5
C2	List	=Sheet2!$B$1:$B$3

 

[BSALV]

i can't give you a formula in D2, otherwise it creates a circular reference.
You have to enter that number, as a constant, manually or with the help of VBA (by a button, doubleclicking, an event, ...).

 

[bsnow]

Gotcha - thank you, that's been really helpful!