I tried looking for an answer for this one but I just couldn't find
it. I have a range of values that I can use and the format is pretty
odd:
* It will always start with A0
* Then there's 3 digits that need to follow the pattern 0, 1, 2,
3...8, 9, A, B, C, D... X, Y, Z and then go back to 0, 1, etc
* So the series will be A00001, A00002, A00003... A00008, A00009,
A0000A, A0000B, A0000C... A0000X, A0000Y, A0000Z, A00010, A00011, etc

I tried different approaches but I haven't been able to figure this
one out. Help!!!

Here is VBA that gives what you want.
Need help with VBA? David McRitchie's site on "getting started" with VBA

http://www.mvps.org/dmcritchie/excel/getstarted.htm


Sub mylist()
mytext = "A0"
mynumber = 0
n = 1
For j = 1 To 10
For k = 1 To 36
If k < 10 Then
mylast = k
ElseIf k < 36 Then
mylast = Chr(k + 55)
Else
mylast = 0
End If
mystring = Application.WorksheetFunction.Text(mynumber, "0000")

mycode = mytext & mystring & mylast

Cells(n, 1) = mycode
n = n + 1
Next k
mynumber = mynumber + 1
Next j

End Sub


best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

wrote in message
ups.com...
I tried looking for an answer for this one but I just couldn't find
it. I have a range of values that I can use and the format is pretty
odd:
* It will always start with A0
* Then there's 3 digits that need to follow the pattern 0, 1, 2,
3...8, 9, A, B, C, D... X, Y, Z and then go back to 0, 1, etc
* So the series will be A00001, A00002, A00003... A00008, A00009,
A0000A, A0000B, A0000C... A0000X, A0000Y, A0000Z, A00010, A00011, etc

I tried different approaches but I haven't been able to figure this
one out. Help!!!

Hi Morrison,

You don't say what happens after you get to A00020 but
this should get you heading in the right direction.

In A1 put '0001
In A2 put '0002
Highlight both cells and drag down to A10
Then in A11 to A36 put the alphabet
A
B
C
etc.

Now highlight A1 to A36 grab the fill handle and drag down
as far as you require.

Then put this in B1, =IF(LEN(A1)=1,"A0000"&A1,"A0"&A1)
enter it, then reselect the cell and double click on the fill handle.

Hopefully that is a good start.
Martin


wrote in message
ups.com...
I tried looking for an answer for this one but I just couldn't find
it. I have a range of values that I can use and the format is pretty
odd:
* It will always start with A0
* Then there's 3 digits that need to follow the pattern 0, 1, 2,
3...8, 9, A, B, C, D... X, Y, Z and then go back to 0, 1, etc
* So the series will be A00001, A00002, A00003... A00008, A00009,
A0000A, A0000B, A0000C... A0000X, A0000Y, A0000Z, A00010, A00011, etc

I tried different approaches but I haven't been able to figure this
one out. Help!!!

This may not be the most efficient solution, but produces the desired
result.

Put your first number in cells A1:F1
A1 = A
B1 = 0
C1 = 0
D1 = 0
E1 = 0
F1 = 0

In G1, use this formula, and copy down to G2:
=A1&B1&C1&D1&E1&F1

Then, use these formulas in row 2:
A2: =A1

B2: =B1

C2: =IF(INT((ROW()-1)/46656)=INT((ROW(C1)-1)/
46656),C1,IF(AND(ISNUMBER(C1),C1<9),--C1+1,IF(C1=9,"A",IF(C1="Z",
0,CHAR(CODE(C1)+1)))))

D2: =IF(INT((ROW()-1)/1296)=INT((ROW(D1)-1)/1296),D1,IF(AND(ISNUMBER(--
D1),D1<9),--D1+1,IF(D1=9,"A",IF(D1="Z",0,CHAR(CODE(D1)+1)))))

E2: =IF(INT((ROW()-1)/36)=INT((ROW(E1)-1)/36),E1,IF(AND(ISNUMBER(--
E1),E1<9),--E1+1,IF(E1=9,"A",IF(E1="Z",0,CHAR(CODE(E1)+1)))))

F2: =IF(AND(ISNUMBER(--F1),F1<9),--F1+1,IF(F1=9,"A",IF(F1="Z",
0,CHAR(CODE(F1)+1))))

Now, highlight A2:G2, and copy down as far as you need.

In Excel 2003 and earlier, the highest number you can get to is
A01EKE. In Excel 2007 you can get considerably further, but it will
take some time to calculate. 200,000 rows take about 40 seconds to
calculate. If you do use Excel 2007, I would recommend doing it in
portions, because all cells are dependent on each other and the
recalculation chain gets to be huge. So, extend it 100,000 rows at a
time and you'll be ok. Turn off autosave, too.


On Sep 15, 1:14 pm, wrote:
I tried looking for an answer for this one but I just couldn't find
it. I have a range of values that I can use and the format is pretty
odd:
* It will always start with A0
* Then there's 3 digits that need to follow the pattern 0, 1, 2,
3...8, 9, A, B, C, D... X, Y, Z and then go back to 0, 1, etc
* So the series will be A00001, A00002, A00003... A00008, A00009,
A0000A, A0000B, A0000C... A0000X, A0000Y, A0000Z, A00010, A00011, etc

I tried different approaches but I haven't been able to figure this
one out. Help!!!

P.S. In case you're wondering, row 1,048,576 yields the code A0MH33.

On Sep 17, 1:17 pm, iliace wrote:
This may not be the most efficient solution, but produces the desired
result.

Put your first number in cells A1:F1
A1 = A
B1 = 0
C1 = 0
D1 = 0
E1 = 0
F1 = 0

In G1, use this formula, and copy down to G2:
=A1&B1&C1&D1&E1&F1

Then, use these formulas in row 2:
A2: =A1

B2: =B1

C2: =IF(INT((ROW()-1)/46656)=INT((ROW(C1)-1)/
46656),C1,IF(AND(ISNUMBER(C1),C1<9),--C1+1,IF(C1=9,"A",IF(C1="Z",
0,CHAR(CODE(C1)+1)))))

D2: =IF(INT((ROW()-1)/1296)=INT((ROW(D1)-1)/1296),D1,IF(AND(ISNUMBER(--
D1),D1<9),--D1+1,IF(D1=9,"A",IF(D1="Z",0,CHAR(CODE(D1)+1)))))

E2: =IF(INT((ROW()-1)/36)=INT((ROW(E1)-1)/36),E1,IF(AND(ISNUMBER(--
E1),E1<9),--E1+1,IF(E1=9,"A",IF(E1="Z",0,CHAR(CODE(E1)+1)))))

F2: =IF(AND(ISNUMBER(--F1),F1<9),--F1+1,IF(F1=9,"A",IF(F1="Z",
0,CHAR(CODE(F1)+1))))

Now, highlight A2:G2, and copy down as far as you need.

In Excel 2003 and earlier, the highest number you can get to is
A01EKE. In Excel 2007 you can get considerably further, but it will
take some time to calculate. 200,000 rows take about 40 seconds to
calculate. If you do use Excel 2007, I would recommend doing it in
portions, because all cells are dependent on each other and the
recalculation chain gets to be huge. So, extend it 100,000 rows at a
time and you'll be ok. Turn off autosave, too.

On Sep 15, 1:14 pm, wrote:



I tried looking for an answer for this one but I just couldn't find
it. I have a range of values that I can use and the format is pretty
odd:
* It will always start with A0
* Then there's 3 digits that need to follow the pattern 0, 1, 2,
3...8, 9, A, B, C, D... X, Y, Z and then go back to 0, 1, etc
* So the series will be A00001, A00002, A00003... A00008, A00009,
A0000A, A0000B, A0000C... A0000X, A0000Y, A0000Z, A00010, A00011, etc

I tried different approaches but I haven't been able to figure this
one out. Help!!!- Hide quoted text -

- Show quoted text -