Hello
I have a list of names and against which I have a to generate a 8 character alphabet series.
Total rows - 634283
Format - 0000000A, 0000000B......0000000Z, 000000AA.......000000ZZ, 00000AAA ..... 00000ZZZ, .......... ZZZZZZZZ
I tried with some formula but they are taking too much memory and crashing the excel.
Please help
Senz
Can you attach a small sample workbook showing what your data and output looks like?
Go Advanced -> Manage Attachments -> Upload
Attachment attached
Can you post what you have tried that crashes Excel?
I was trying to put the formula but the site do not allow me and says You are not allowed to post any kinds of links, images or videos until you post a few times.
The formula attached
try this one
this is first formula:
=REPT("0",8-(TRUNC((ROW(1:1)-1)/26)+1))&REPT(CHAR(MOD(ROW(1:1)-1,26)+65),TRUNC((ROW(1:1)-1)/26)+1)
then copy down
Try this and copy down;
Formula:
Please Login or Register to view this content.
After ZZ it is repeating alphabet
000000AA
000000AA
000000AA
000000AA
000000AA
000000AA
000000AA
000000AA
000000AA
000000AA
000000AA
Also as the alphabets increase in sequence the 0s should become less, for example 0000000A, 000000AA, 00000AAA
I have more than 600000 rows, thus the values will look like AAB, AAC and so on
Does it have to be formula? Maybe you can use macro instead?
I've written down something like below. It is not optimize, not very nice but generates string between 000A to ZZZZ.
I hope you will be able to modifying it to your needs:
Sub generatedata()
Dim i1 As Integer
Dim i2 As Integer
Dim i3 As Integer
Dim i4 As Integer
Dim i5 As Integer
Dim i6 As Integer
Dim i7 As Integer
Dim i8 As Integer
Dim iRow As Long
Dim iValue As String
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
iRow = 1
For i1 = 48 To 90
If (i1 > 48 And i1 < 65) Then i1 = 65
For i2 = 48 To 90
If (i2 > 48 And i2 < 65) Then i2 = 65
For i3 = 48 To 90
If (i3 > 48 And i3 < 65) Then i3 = 65
For i4 = 65 To 90
iValue = Chr(i1) & Chr(i2) & Chr(i3) & Chr(i4)
Range("B" & iRow).Value = Trim(iValue)
iRow = iRow + 1
if (Range("A"&iRow).value = "") then ' to check, maybe list has ended
exit sub
end if
Next i4
Next i3
Next i2
Next i1
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
Be aware that with these 4 loops it takes a while and generates 511758 rows.
Last edited by KOKOSEK; 01-22-2019 at 08:48 AM. Reason: check is it enough
Happy with my answer * Add Reputation.
If You are happy with solution, please use Thread tools and mark thread as SOLVED.
Please try
Formula:Please Login or Register to view this content.
Post 7 and 8 formulae both break down after a while. This MIGHT be OK....
=RIGHT("0000000"&LEFT(ADDRESS(1,ROWS($1:1),4),LEN(ADDRESS(1,ROWS($1:1),4))-1),8)
It is certainly OK to 5000 rows...
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Bo_Ry... most unusal... a really horrible-looking formula coming from you....
Glenn, I did try ADDRESS but it only works until XFD, row 16384
OP said he needs 600,000 rows so the formula gets longer and longer but work until row 1m
I guess I didnt have the patience to copy paste it down that far!!
I use fill series in column A and column B is just double click down.
Fill Series.jpg
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks