all lottery combinations 1 - 49

damainman

New Member
Joined
Feb 10, 2008
Messages
16
i need excel to output all lottery combinations. the numbers are from 1-49 from which 6 numbers are drawn. i do realise the number of combinations are 13983816. i want the 6 numbers drawn to be in different cells across 6 different columns. so the 1st 6 numbers are in a1, b1, c1, d1, e1, f1. obviously excel 2003 has 65536 rows and so the formula/vb code should carry on onto a new worksheet or ideally a new workbook. its ideal in a new workbook so not to end up with a single huge 10gig file, but on several worksheets in 1 workbook is fine.

i have been trying to do this myself for a while now with no success. any help would be great. from what i understand; this is a bit of a challenge i know.
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
Try this:

Code:
Sub allLottery()
    Dim x As Long
    Application.ScreenUpdating = False
    x = 1
    x = doTheLott("", x, 1)
End Sub
Function doTheLott(ByVal xStr As String, r As Long, a As Integer) As Long
    Dim xArr As Variant
    xArr = Split(Trim(xStr), " ")
    If UBound(xArr) = 5 Then
        Cells(r, 1).Resize(1, 6).Value = xArr
        r = r + 1
        If r > Rows.Count Then
            Sheets.Add
            r = 1
        End If
    Else
        For i = a To 49
            j = Format(i, "00")
            If InStr(xStr, j) = 0 Then
                r = doTheLott(xStr & j & " ", r, a + 1)
            End If
        Next
    End If
    doTheLott = r
End Function
Run allLottery and then go make a coffee. No idea how long it'll take (or even if it actually works...)
 

damainman

New Member
Joined
Feb 10, 2008
Messages
16
its just for personal non work related purposes. i want to be able to then play about with the combinations and query/analyse the list.

it would be great if someone could give me some help.
 

damainman

New Member
Joined
Feb 10, 2008
Messages
16

ADVERTISEMENT

thanks for that solution....
...i am literrally having to have a tea! lol

i shall let you know what happens!
 

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
Actually, I ran it for a while and there is some repetition. Also, it failed after 17 sheets' worth of numbers! I calculated it'll need at least 200
 

damainman

New Member
Joined
Feb 10, 2008
Messages
16

ADVERTISEMENT

mines still running! lol

some repetition you say? :( any solution?

this will atleast hopefully be the closest iv come to solving this problem!
 

damainman

New Member
Joined
Feb 10, 2008
Messages
16
its still running! 600+ sheets created and counting! think its def stuck in a loop or something as it should not require that many sheets!!
 
Last edited:

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
Unless that's the repetition I was talking about - must be worse than I thought. I'll take another look at this, maybe tonight, poss over the weekend. It's probably worth doing on general principles. Whatever I do it needs to be quick!
 

Weaver

Well-known Member
Joined
Sep 10, 2008
Messages
5,196
I've got a version that saves every time a new sheet is added and 2003 seems to fall over once the file size hits 100mb. Running in 2007 it's still going strong at 173 mb and currently 8 sheets!

****, I wish I hadn't read your post!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,110
Messages
5,599,776
Members
414,337
Latest member
ogoodheart

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top