how do I increment letters of the alphabet for a database

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Howdy,

I use a database that contains an id field that is a 5 letter "code". So
the first item in the database is AAAAA, the second is AAAAB, the third is
AAAAC and so on until item 26 is AAAAZ. Item 27 is AAABA, then 28 is AAABB,
then 29 AAABC and on and on.

Is there a way for excel to increment all the letters up to ZZZZZ in
seperate cells?

Thanks!
 
Run this:

Sub urb()
ii = 1
For i = 65 To 90
v1 = Chr(i)
For j = 65 To 90
v2 = Chr(j)
For k = 65 To 90
v3 = Chr(k)
For l = 65 To 90
v4 = Chr(l)
For m = 65 To 90
v5 = Chr(m)
Cells(ii, "A").Value = v1 & v2 & v3 & v4 & v5
If ii = 65536 Then Exit Sub
ii = ii + 1
Next
Next
Next
Next
Next
End Sub
 
With 5 characters (a-z), you have:
26*26*26*26*26
possible combinations. That's almost 12 million "words".

If you still want this, how would you want the list laid out?

And what version of excel are you using? xl2007 supports 1M rows. xl97-xl2003
only has 64k rows.
 
Thanks! You are a lifesaver

Gary''s Student said:
Run this:

Sub urb()
ii = 1
For i = 65 To 90
v1 = Chr(i)
For j = 65 To 90
v2 = Chr(j)
For k = 65 To 90
v3 = Chr(k)
For l = 65 To 90
v4 = Chr(l)
For m = 65 To 90
v5 = Chr(m)
Cells(ii, "A").Value = v1 & v2 & v3 & v4 & v5
If ii = 65536 Then Exit Sub
ii = ii + 1
Next
Next
Next
Next
Next
End Sub
Click to expand...
 
For something quick-n-dirty, how about a function that Ranks your string,
adds 1, and then unranks it?
For example, if you have "AAAAA" in A1, put this in A2.
=NextSequence(A1)

At some future time, perhaps you can Copy - Paste Special Values.

Function NextSequence(s) As String
Dim UB As Long
Dim k As Long
Dim n As Long
Dim j As Long
Dim v() As Variant
Const b As Long = 26 'Base 26

UB = Len(s)
k = 1
ReDim v(1 To UB)
For j = 1 To UB
v(j) = Asc(Mid$(s, j, 1)) - 65
Next j

For j = UB To 1 Step -1
n = n + (v(j) Mod b) * k
k = k * b
Next j

n = n + 1

For j = UB To 1 Step -1
v(j) = Chr((n Mod b) + 65)
n = n \ b
Next
NextSequence = Join(v, vbNullString)
End Function
 
Use this formula in A1, B1, C1, D1:

=CHAR(CODE("A")+MOD(INT((ROW()-1)/(POWER(26,5-COLUMN()))),26))

Use this formula in E1:

=CHAR(CODE("A")+MOD(ROW()-1,26))

Copy down as many times as needed.

In F1, you could use this formula:

=A1&B1&C1&D1&E1

Then, copy down to create a column with the 5-letter combinations in
ascending order.

Just for kicks, I entered these formulae in Excel 2007 in each
available row. The last one - 1,048,576 - has the value of CHRDV.
If you want to take it from there, replace ROW()-1 with ROW()
+1,048,575 in each formula above. This calculation took about 15
minutes.

By comparison, running a similar expression on nearly 700,000 rows in
Access took only a few seconds. I used a combination of ROUND() and
ABS() to achieve the same result as MOD. You might consider that path
rather than messing with Excel.
 
Back
Top