Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I increment letters of the alphabet for a database
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I increment letters of the alphabet for a database
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 -- Gary''s Student - gsnu200740 "gretchen" wrote: 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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I increment letters of the alphabet for a database
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. gretchen wrote: 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! -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I increment letters of the alphabet for a database
Thanks! You are a lifesaver
"Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200740 "gretchen" wrote: 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! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I increment letters of the alphabet for a database
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 -- HTH :) Dana DeLouis Windows XP & Excel 2007 "gretchen" wrote in message ... 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! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
how do I increment letters of the alphabet for a database
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. On Aug 29, 9:24 pm, gretchen wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Solver - Assign values to the letters of the alphabet | Excel Discussion (Misc queries) | |||
Solver - Assign values to Letters of the Alphabet | Excel Discussion (Misc queries) | |||
Solver - Assign values to Letters of the Alphabet | Excel Discussion (Misc queries) | |||
associate alphabet letters with numbers? | New Users to Excel | |||
how to assign a value to the alphabet in order to add up letters | Excel Discussion (Misc queries) |