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
ABCD	ABCD0001
ABCD	ABCD0002
ABCD	ABCD0003
ABCD	ABCD0004
EFGH	EFGH0001
IJK	IJK0001
IJK	IJK0002
IJK	IJK0003
IJK	IJK0004
XYZ	XYZ0001
XYZ	XYZ0002

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

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

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

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

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

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

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

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

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

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

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

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=A1&TEXT(COUNTIF(A$1:A1,A1),"0000")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

works an absolute treat thank you very much

 

[Fluff]

You're welcome