Generating an alphanumeric sequence based on given data & serialization

[murexcel]

Hi,

I have a project wherein I need to generate an alphanumeric sequence based on certain values. An example is below:

AU1VT11201M (START) AU1VT1120120M (END) AU1CB1120121M (START) AU1CB1120240M (END)​

AU1 = LOCATION
VT OR CB = Product code
1120 = MONTH & YEAR
1-240 = SERIES
M = SUPPLIER INITIALS

I need to create such series for almost 22 locations. Is there a way I can create a data entry form wherein I can feed these details & it will generate the all the data in a single column.

Also note that 1-120 & 121-240 the product code changes. Rest of the fields are constant apart from location.

Will appreciate any help! Thanks.

 

[Eric W]

Try:

Book1
	A	B	C	D	E	F	G	H
1	Location	Month/Year	Product Code	Start	End	Initials		List
2	AU1	1120	VT	1	120	M		AU1VT11201M
3			CB	121	240			AU1VT11202M
4								AU1VT11203M
120								AU1VT1120119M
121								AU1VT1120120M
122								AU1CB1120121M
123								AU1CB1120122M
240								AU1CB1120239M
241								AU1CB1120240M
242
Sheet8

Cell Formulas
Range	Formula
H240:H242,H120:H123,H2:H4	H2	=IF(ROWS($H$2:$H2)>$E$3,"",$A$2&INDEX($C$2:$C$3,MATCH(ROWS($H$2:$H2),$D$2:$D$3))&$B$2&ROWS($H$2:$H2)&$F$2)

 

[murexcel]

It works perfectly. Thanks a lot for your help.