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
​

 

[mrmmickle1]

This may be of use. It won't get you all the way to your goal but it demonstrates how to skip the letters you mentioned.

[COLOR=#0000ff]Sub [/COLOR]AToZMissingSome()


 b = 1
[COLOR=#0000ff] For [/COLOR]x = 65 [COLOR=#0000ff]To [/COLOR]90 [COLOR=#008000]'These Character Numbers Represent  A -Z[/COLOR]
    [COLOR=#0000ff]If [/COLOR]x = 66 [COLOR=#0000ff]Or[/COLOR] x = 68 [COLOR=#0000ff]Or[/COLOR] x = 73 [COLOR=#0000ff]Or[/COLOR] x = 79 [COLOR=#0000ff]Or[/COLOR] x = 83 [COLOR=#0000ff]Or[/COLOR] x = 90 [COLOR=#0000ff]Then [/COLOR]  [COLOR=#008000]'If Character Number is B,D,I,O,S or Z Skip[/COLOR]
[COLOR=#008000]    'Skip[/COLOR]
    Else
    Cells(b, 1) = Chr(x) [COLOR=#008000]'Enter Letter into Cell[/COLOR]
    b = b + 1
[COLOR=#0000ff]    End If[/COLOR]
[COLOR=#0000ff] Next [/COLOR]x
[COLOR=#0000ff]             [/COLOR]
[COLOR=#0000ff]End Sub[/COLOR]

It results in:

Excel 2013
	A
1	A
2	C
3	E
4	F
5	G
6	H
7	J
8	K
9	L
10	M
11	N
12	P
13	Q
14	R
15	T
16	U
17	V
18	W
19	X
20	Y
Sheet1

You can probably just increment through the numbers in a loop as well. Shouldn't be too difficult.....

 

[damian1080]

This may be of use. It won't get you all the way to your goal but it demonstrates how to skip the letters you mentioned.

[COLOR=#0000ff]Sub [/COLOR]AToZMissingSome()


 b = 1
[COLOR=#0000ff] For [/COLOR]x = 65 [COLOR=#0000ff]To [/COLOR]90 [COLOR=#008000]'These Character Numbers Represent  A -Z[/COLOR]
    [COLOR=#0000ff]If [/COLOR]x = 66 [COLOR=#0000ff]Or[/COLOR] x = 68 [COLOR=#0000ff]Or[/COLOR] x = 73 [COLOR=#0000ff]Or[/COLOR] x = 79 [COLOR=#0000ff]Or[/COLOR] x = 83 [COLOR=#0000ff]Or[/COLOR] x = 90 [COLOR=#0000ff]Then [/COLOR]  [COLOR=#008000]'If Character Number is B,D,I,O,S or Z Skip[/COLOR]
[COLOR=#008000]    'Skip[/COLOR]
    Else
    Cells(b, 1) = Chr(x) [COLOR=#008000]'Enter Letter into Cell[/COLOR]
    b = b + 1
[COLOR=#0000ff]    End If[/COLOR]
[COLOR=#0000ff] Next [/COLOR]x

[COLOR=#0000ff]End Sub[/COLOR]

It results in:
Excel 2013

	A
A
C
E
F
G
H
J
K
L
M
N
P
Q
R
T
U
V
W
X
Y

<colgroup><col style="width: 25pxpx"><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]

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

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

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

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

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

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

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

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

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

</tbody>

Sheet1​

You can probably just increment through the numbers in a loop as well. Shouldn't be too difficult.....

Many thanks for your help Mrmmickle1. Is there anyone who can help with creating a formula to sequentially produce the alphanumeric characters stating from 00AA00 and 0AAA00. Thanks,

yours gratefully

Damian1080

 

[oldbrewer]

Series 1 - 2 letters in any of first 4 positions, 00AA00 until YY0000

is A00Y00 allowed?

assuming it is first character can be any of 32, likewise char's 2,3,4

so you will have 32 to power 4 permutations

do you want them all NOW or just to add a new one when needed

do you want to start 00AA00, 00AB00 etc

 

[damian1080]

Series 1 - 2 letters in any of first 4 positions, 00AA00 until YY0000

is A00Y00 allowed?

assuming it is first character can be any of 32, likewise char's 2,3,4

so you will have 32 to power 4 permutations

do you want them all NOW or just to add a new one when needed

do you want to start 00AA00, 00AB00 etc

Hi Oldbrewer, thanks for your assistance. In response to your questions A00Y00 is allowed. I would like the formula which can give them all to me now. I also need them to be able to be sorted in Excel so I can define which ones to use next. Hope this makes sense. I think it starts 00AA00, 00AA01.......00AA99. 00AC00(B is not allowed).

Thanks

Damian 1080

 

[oldbrewer]

it is 100,000,000 numbers - you cannot need them all now

 

[damian1080]

it is 100,000,000 numbers - you cannot need them all now

Hi Oldbrewer, good point, this quantity will last the next 40 years. I suppose I want a macro that will give me the next x lot of numbers, so e.g, x = 1000, would give me the next 1000 numbers.

Hope you can help

Thanks

Damian1080

 

[oldbrewer]

first number is 00AA00 OR POSSIBLY 01AA01 (YOUR CHOICE - ASSUME 01 OPTION)

Is the second number 01AA02 or 02AA01 or 01AB01 - again it is your choice

 

[damian1080]

first number is 00AA00 OR POSSIBLY 01AA01 (YOUR CHOICE - ASSUME 01 OPTION)

Is the second number 01AA02 or 02AA01 or 01AB01 - again it is your choice

Thanks again for your help. I would like if possible 00AA00, followed by 00AA01 and it to be sortable in order by Excel.

Cheers

Damian1080

 

[oldbrewer]

I can produce any number of them now I know your rules - it might get complicated because of the way excel sorts text

eg the text numbers 1 to 11 sort as 1,10,11,2,3,4,5,6,7,8,9

I will make a few and test the code then give you it