Assign next sequential number in a non-sequential list

[fangfacekitty]

I am creating a list of all assigned document numbers for the company. The document numbers are assigned sequentially based on the components of the numbering system (Region-Dept-Document Type-XX). I want to automatically assign the document number to all new documents added to the list to prevent the duplication that will happen if I leave it to people to assign the number manually.

It's easy to concatenate the first 3 sections of the document number; where I am stumped is how to generate the next specific sequential number for the new document.
Example: Rows 2:7 already exist. My users want new forms in Europe and North America for the IT department, which should auto assign the document numbers EU-IT-F-02 (cell D8) and NA-IT-F-01 (cell D9) because this is the 2nd form created for Europe IT but the 1st form created for North America IT.

I've thought about using COUNTIF and adding 1 to the result to get the next sequential number but currently I have 200 unique combinations of the first 3 segments (region-dept-doc type), so this really doesn't seem workable.

Any ideas are greatly appreciated!

 

[fangfacekitty]

Here is the table, not sure if the image is visible:

Column1	A	B	C	D	E
1	Region	Dept	Doc Type	Number	Title
2	AS	HR	P	AS-HR-P-01	JKL
3	CORP	HR	P	CORP-HR-P-01	ABC
4	CORP	HR	P	CORP-HR-P-02	PQR
5	EU	IT	F	EU-IT-F-01	MNO
6	NA	FIN	I	NA-FIN-I-01	DEF
7	NA	IT	P	NA-IT-P-01	GHI
8	EU	IT	F	EU-IT-F-XX	STU
9	NA	IT	F	NA-IT-F-YY	XYZ

 

[jtakw]

Hi,

This is one way, once the number reaches 99, it'll go to 100 and continue.

00 HTML Conversions.xlsm
	A	B	C	D	E
1	Region	Dept	Doc Type	Number	Title
2	AS	HR	P	AS-HR-P-01	JKL
3	CORP	HR	P	CORP-HR-P-01	ABC
4	CORP	HR	P	CORP-HR-P-02	PQR
5	EU	IT	F	EU-IT-F-01	MNO
6	NA	FIN	I	NA-FIN-I-01	DEF
7	NA	IT	P	NA-IT-P-01	GHI
8	EU	IT	F	EU-IT-F-02	STU
9	NA	IT	F	NA-IT-F-01	XYZ
Sheet595

Cell Formulas
Range	Formula
D2:D9	D2	=A2&"-"&B2&"-"&C2&"-"&TEXT(COUNTIF(D$1:D1,A2&"-"&B2&"-"&C2&"-"&"*")+1,"00")

Formula copied down.

 

[fangfacekitty]

Perfect! Thanks so much.

 

[jtakw]

You're welcome, thanks for the feedback.

 

[nitmani]

Hi jtakw, i have asimilar requirement. However, I could find the formula you mentioned that copied below. Can you please repost it?

 

[Peter_SSs]

Welcome to the MrExcel board!

When the forum was upgraded recently, some posts have not rendered correctly under the new software. I have fixed the post above so you should be able to get that formula now.