Help with alphanumeric sequencing - 6 characters

[damian1080]

Hi,

I am relatively inexperienced Excel user and am trying to create a list of 6 digit alphanumeric serial numbers in Excel. I am looking for help to sequence 2 series of alphanumerics with the following rules

Series 1 - 2 letters in any of first 4 positions, 00AA00 until YY0000
Series 2 - 3 letters in any of first 4 positions , 0AAA00 will be used first until AAA000

I would like the series to be sorted in Excel so i can choose numbers between a range.

I would like to also implement the following rules
any digit (0 to 9) is acceptable in any position;
letters I and O are forbidden in any position because they can be confused with digits 1 and 0, respectively;
letters B, D, S, Z are forbidden in any position;
letters are All Caps

Any help on this would be gratefully received

Many Thanks

Damian1080
​

 

[damian1080]

if the rule is there has to be 2 letters with 4 numbers cycling from 0 to 9, one approach could be to accept that the two letters can only have 15 unique positions, so the macro has to put the letters in the following positions

12....13....14....15.....16.......23.....24....25.....26.........34.......35.....36...........45.......46........56

so for each of these 4 positions cycle from 0 to 9 ie 10000 for each AA in position 12, 10000 for each AB in position 12

this is 60 million unique numbers

I need OP go ahead before spending time on this....

Thanks for your ongoing assistance Oldbrewer. To answer your question A00A00 is allowed. Your idea of cycling the positions looks good. I have managed with some assistance to get your initial macro doing what I want it to do. I am planning on developing this further tomorrow. Please can you hold fire until I've tested that the solution that I'm working on gives me what I want. I've basically created a number of worksheets within the spreadsheet which give me each combination using your macro. It needs more work, but I'm hopeful of it working for me. I'll let you know how I get on. Thanks again

Damian1080

 

[damian1080]

Sub Serial_A0A000()
Application.ScreenUpdating = False
Sum = 0
For n1 = Cells(9, 18) To Cells(9, 19)
For n2 = Cells(10, 18) To Cells(10, 19)
For n3 = Cells(11, 18) To Cells(11, 19)
For n4 = Cells(12, 18) To Cells(12, 19)
For n5 = Cells(13, 18) To Cells(13, 19)
For n6 = Cells(14, 18) To Cells(14, 19)
Sum = Sum + 1
If Sum = 1 Then n6 = n6 + Cells(14, 20): n5 = n5 + Cells(13, 20): n4 = n4 + Cells(12, 20): n3 = n3 + Cells(11, 20) - 1: n2 = n2 + Cells(10, 20): n1 = n1 + Cells(9, 20) - 1
Cells(1, 5) = n1: Cells(1, 6) = n2: Cells(1, 7) = n3: Cells(1, 8) = n4: Cells(1, 9) = n5: Cells(1, 10) = n6
Cells(Sum, 1) = Cells(1, 11)
If Sum = Cells(2, 16) Then GoTo 0
Next n6
Next n5
Next n4
Next n3
Next n2
Next n1
0 End Sub

Thanks for your assistance Oldbrewer, I have now got a code that works for me. See above. Because it's been decided that I want letters in specific positions then I've got 1 macro per series required.

Thanks again for your time on this

Damian 1080

 

[mrmmickle1]

Glad you were able to get this sorted out... I started working on a similar method yesterday evening that took the position of the alphanumeric character into account. I called my variables Pos1, Pos2,Pos3.... instead of n1,n2,n3. Thanks for sharing the solution.

 

[oldbrewer]

cant see how AA goes to AB, AC etc

 

[oldbrewer]

1		12	1	2	3	4	5	6		1	A	1
2		13	1	3	2	4	5	6		2	C	A
3		14	1	4	2	3	5	6		3	E
4		15	1	5	2	3	4	6		4	F
5		16	1	6	2	3	4	5		5	G
6		23	2	3	1	4	5	6		6	H			the table to the left calculate where
7		24	2	4	1	3	5	6		7	J			the letters and numbers should be
8		25	2	5	1	3	4	6		8	K
9		26	2	6	1	3	4	5		9	L
10		34	3	4	1	2	5	6		10	M			this macro does it
11		35	3	5	1	2	4	6		11	N
12		36	3	6	1	2	4	5		12	P			we start with AA0000
13		45	4	5	1	2	3	6		13	Q			and finish with 9999ZZ
14		46	4	6	1	2	3	5		14	R
15		56	5	6	1	2	3	4		15	T
										16	U			after ZZ9999 it goes to A0A000
										17	V
										18	W
										19	X
A	A	0	0	0	0					20	Y			warning
A	A	0	0	0	1
A	A	0	0	0	2									to enable you to test it
A	A	0	0	1	0									the loops were curtailed to 2 for letters
A	A	0	0	1	1									and 2 for numbers- the correction is to the right
A	A	0	0	1	2									of the relevant lines
A	A	0	0	2	0
A	A	0	0	2	1								Sum = 19
A	A	0	0	2	2								For j = 1 To 15
A	A	0	1	0	0								l1 = Cells(j, 4)
A	A	0	1	0	1								l2 = Cells(j, 5)
A	A	0	1	0	2								n1 = Cells(j, 6)
A	A	0	1	1	0								n2 = Cells(j, 7)
A	A	0	1	1	1								n3 = Cells(j, 8)
A	A	0	1	1	2								n4 = Cells(j, 9)
A	A	0	1	2	0								For let1 = 1 To 2		20
A	A	0	1	2	1								Cells(1, 13) = let1
A	A	0	1	2	2								r1 = Cells(2, 13)
A	A	0	2	0	0								For let2 = 1 To 2		20
A	A	0	2	0	1								Cells(1, 13) = let2
A	A	0	2	0	2								r2 = Cells(2, 13)
A	A	0	2	1	0								For num1 = o To 2		9
A	A	0	2	1	1								For num2 = 0 To 2		9
A	A	0	2	1	2								For num3 = 0 To 2		9
A	A	0	2	2	0								For num4 = 0 To 2		9
A	A	0	2	2	1								Sum = Sum + 1
A	A	0	2	2	2								Cells(Sum, l1) = r1
A	A	1	0	0	0								Cells(Sum, l2) = r2
A	A	1	0	0	1								Cells(Sum, n1) = num1
A	A	1	0	0	2								Cells(Sum, n2) = num2
A	A	1	0	1	0								Cells(Sum, n3) = num3
A	A	1	0	1	1								Cells(Sum, n4) = num4
A	A	1	0	1	2			these 2 lines put in a delay>>>>>>>>>>>>	For delay = 1 To 7000000
								so that you can see the sequencing >>>>>>>>>>>>>>	Next delay
													Next num4
													Next num3
													Next num2
	I stopped the macro here										Next num1
	but earlier I let it run to completion								Next let2
													Next let1
													Next j
													End Sub

<colgroup><col span="3"><col><col><col span="14"></colgroup><tbody>
</tbody>