all lottery combinations 1 - 49

[damainman]

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.

 

[Weaver]

Ran in 2007 and it filled 13 sheets, with 352,328 lines in sheet 14 (took about 90 minutes, 324mb file...!)

13 * 1,048,576 + 352,328 = 13,983,816

So if your maths was right to begin with, I'd say that's job done

Sub allLottery()
    Dim x As Long
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
    x = doTheLott("", 1, 1)
    With Application
        .StatusBar = False
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
    ActiveWorkbook.Save
End Sub

Function doTheLott(ByVal xStr As String, r As Long, a As Integer) As Long
    Dim xArr As Variant, i As Integer
    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
            ActiveWorkbook.Save
            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, i)
            End If
        Next
    End If
    doTheLott = r
End Function

Mind you, I wouldn't try running it on 2003 again...

 

[tusharm]

The ability to analyze nearly 14 million numbers especially when scattered across multiple worksheets / workbooks is close to non-existent! Why generate the numbers when you really won't be able to do anything with them?

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.

 

[Weaver]

The ability to analyze nearly 14 million numbers especially when scattered across multiple worksheets / workbooks is close to non-existent! Why generate the numbers when you really won't be able to do anything with them?

If doing it is so pointless, then so is asking why...

Besides which, it was fun.

 

[Simon Lloyd]

I just laughed out loud reading this, i run that many things at one time that if i had run that code i think the neigbourhood would black out

Yes it's a pointless excercise, no one really has the time to analyse all those entries. Wouldn't you be better off importing all the latest draws and analysing the frequency and mode of the draws?

 

[tusharm]

Then, I am sure you will enjoy calculating pi to a googol decimal places or calculating every prime number less than googol^googol {vbg}

Have you looked at Project Euler problems? You'll probably enjoy them.
Project Euler
http://www.tushar-mehta.com/misc_tutorials/project_euler/

If doing it is so pointless, then so is asking why...

Besides which, it was fun.

 

[Simon Lloyd]

If it's any good to you i can get ALL the combinations on one sheet 6 numbers per cell no duplications?

 

[Weaver]

If it's any good to you i can get ALL the combinations on one sheet 6 numbers per cell no duplications?

Climbed that mountain, bought the t-shirt. Next pointless conundrum please!

 

[Weaver]

If it's any good to you i can get ALL the combinations on one sheet 6 numbers per cell no duplications?

Simon

I think I could probably rewrite mine to do the same - it starts off as a string in any case.

I'm off to calculate pi to googol^googol places now....

 

[Trebor76]

Hey guys,

Thought I'd just throw in my 2 cents worth but based on the COMBIN function, there should be 8,145,060 possibilities i.e. =COMBIN(45,6) so I'd say Weaver's solution is out somehow (perhaps due the same numbers being drawn but in a different order).

Cheers,

Robert

 

[Weaver]

Trebor - I've often heard it said there's a 1 in 14,000,000 chance of winning the lottery, so I figured the original estimate was ball park. I just tried =COMBIN(49,6) and got 13,983,816.

PS - great function, never spotted it before.

PPS - where's the emoticon for 'Victory Dance'?