Sequential Numbering

[coloradoprincess78]

Hello Magical Excel Guru's!

I am trying to determine if there is a way that I can use either a formula or vba to auto assign sequential numbering based on text.
Basically we have the united states split into 5 zones. There are multiple states in each zone. Each customer will be assign a site number that combines a pre-fix, zone number, state abbreviation and a 4 digit number. Each state can have up to 9999 numbers.

I would like to house this data in one worksheet, rather than 52 (for each state) and have the number portion automatically assign the next sequential digit available for that particular state.
Good grief, I hope that makes sense.

Here is my example below. The columns in red are formula driven to pull data from another worksheet. I am looking for a formula in the last column that can auto assign a number.
My boss wants me to start each number at 0100 for each state. Column A is a concatenate formula from E,F,G and H. (E is hidden because it is a static text for the moment)



Excel 2010

	A	B	C	D	F	G	H
CON2-AZ0100	ABC	Phoenix	Arizona
CON3-NE0100	XYZ	Omaha	Nebraska
CON3-NE0101	JKL	Wrightsville	Nebraska
CON2-AZ0117	EFG	Peoria	Arizona

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFE699"]Site[/TD]
[TD="bgcolor: #A9D08E"]Customer[/TD]
[TD="bgcolor: #A9D08E"]City[/TD]
[TD="bgcolor: #A9D08E"]State[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF0000]#FF0000[/URL] , align: center"]Zone[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF0000]#FF0000[/URL] , align: center"]State Abbreviation[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FF0000]#FF0000[/URL] , align: center"]Number[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]AZ[/TD]
[TD="align: center"]0100[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]NE[/TD]
[TD="align: center"]0100[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]NE[/TD]
[TD="align: center"]0101[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]AZ[/TD]
[TD="align: center"]0117[/TD]

</tbody>

Zone 1 (2)​

Sorry if this is confusing! Thank you in advance for your help!

 

[Scott T]

Try, this should start at 100 and then add one each time the state is the same.

=COUNTIF($G$2:G2,G2)+99

 

[coloradoprincess78]

Thank you so much Scott!
Worked perfectly! - I haven't needed to use the countif function in the past, but perhaps should have spent a little more time on this before asking for help.

I sincerely appreciate your time