alphanumeric sequence ranges

[tealy1980]

Hi all

thanks in advance for answering.

Is there a formula that will give a sequential code to a range of alphas and the sequence resets to 0001 when the alpha changes?

I.E a customer code file would generate the following:

ABCD0001
ABCD0002
ABCD0003
DEFG0001
HIJK0001
HIJK0002
HIJK0003
HIJK0004
LMNO0001
LMNO0002

 

[Fluff]

Something like

Excel 2013/2016
	A	B
1	ABCD	ABCD0001
2	ABCD	ABCD0002
3	ABCD	ABCD0003
4	ABCD	ABCD0004
5	EFGH	EFGH0001
6	IJK	IJK0001
7	IJK	IJK0002
8	IJK	IJK0003
9	IJK	IJK0004
10	XYZ	XYZ0001
11	XYZ	XYZ0002
Sheet2

Cell Formulas
Range	Formula
B1	=A1&TEXT(COUNTIF(A$1:A1,A1),"0000")

 

[tealy1980]

Something like

Excel 2013/2016

	A	B
1	ABCD	ABCD0001
2	ABCD	ABCD0002
3	ABCD	ABCD0003
4	ABCD	ABCD0004
5	EFGH	EFGH0001
6	IJK	IJK0001
7	IJK	IJK0002
8	IJK	IJK0003
9	IJK	IJK0004
10	XYZ	XYZ0001
11	XYZ	XYZ0002

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula B1 =A1&TEXT(COUNTIF(A$1:A1,A1),"0000") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

works an absolute treat thank you very much

 

[Fluff]

You're welcome