Excel List All Lottery Combinations - 2441

Reid would like to list all 6-number combinations of the numbers 1 to 44. For example, 1-2-3-4-5-6, 1-2-3-4-5-7, and so on up to 39-40-41-42-43-44. The first thing to realize is that all lottery combinations are a lot of numbers. Over 7 million possibilities according to the COMBIN function in Excel. (For Power Ball, there are 292 million combinations!). Listing all combinations will be difficult because Excel only includes 1,048,576 rows.
In this video, I show how to enable Macros in your version of Excel and then the macro code to list all possible combinations.

Here is the code you can copy into your project.

Sub ListThemAll()
    TC = 1
    TR = 1
    Ctr = 1
    MaxRows = Rows.Count
    EndCell = 7059052
    Application.ScreenUpdating = False
    For a = 1 To 39
    For b = (a + 1) To 40
    For c = (b + 1) To 41
    For d = (c + 1) To 42
    For e = (d + 1) To 43
    For f = (e + 1) To 44
    Application.StatusBar = Ctr & " on way to " & EndCell
    Cells(TR, TC).Value = a & "-" & b & "-" & c & "-" & d & "-" & e & "-" & f
    Ctr = Ctr + 1
    If Ctr Mod 25000 = 0 Then
        Cells(TR - 20, TC).Select
        Application.ScreenUpdating = True
        ThisWorkbook.Save
        Application.ScreenUpdating = False
    End If
    TR = TR + 1
    If TR = MaxRows Then
        TR = 1
        TC = TC + 1
    End If
    Next f
    Next e
    Next d
    Next c
    Next b
    Next a
    Application.StatusBar = False
    Application.ScreenUpdating = True
End Sub

Transcript of the video:

Learn Excel from MrExcel Podcast episode 2441: List All Lottery Combinations.
Welcome back to the MrExcel Netcast. I'm Bill Jelen.
Today’s question sent in by Reid.
Wants to find all combinations of six numbers from 1 to 44.
For example, 1-2-3-4-5-6, 1-2-3-4-5-7 all the way up to 39-40-41-42-43-44. Obviously a lottery question.
You know the first thing we have to be aware of is there's a lot of them right.
That's why it's so hard to win the lottery.
If you have 44 numbers chosen 6 at a time that is 7,059,052. So you want to get a list of seven million items.
The first problem is we don't have that many rows in Excel.
We only have 1,048,576.
So the solution is going to fill all of column A through F and maybe even part of G Rather than start to type 1-2-3-4-5-6 like that, let's switch over to VBA.
Now if you've never used VBA before, you have to do this: alt T for Tools, M for Macro, S for Security.
Change this from the top setting down to the second setting.
If you don't have the Developer tab, right click, Customize the Ribbon, turn on the Developer tab.
Once you have the Developer tab, then we can go into Visual Basic like that.
So you'll see in your Project Explorer. View, Project Explorer or Ctrl+R.
There is a list of all the sheets and we're going to say Insert, Module to get a new module.
And then we're going to paste the code. The code will be down in the YouTube description.
Just copy it and paste it. So let's talk about this.
This is called ListThemAll. ThisColumn we are going to start in Column One.
We're going to start in row one. And just have a counter to count how many we have.
As soon as we get to roll 1,048,576, we want to move to the next column.
So Max Rows in the spreadsheet as Rows.Count.
Oh my God, don't try this if you're back in Excel 2003 with only 65536 rows. I guess it would work.
To speed things up, turn off screen updating.
And we know, if the digits are arranged in sequence, can't be higher than 39.
Because 39-40-41-42-43-44 would be the very last number.
So for the first the first number chosen, it's going to be from 1 to 39. For a = 1 to 39.
And then for B, it's going to be one number higher than whatever A is.
So the first time through A is going to be one, and we're going to run from 2 to 40.
But eventually A is going to be 27 are we are going to run from 28 to 40.
That'll be easier there. For C = 1 + b to 41.
D is C + 1 to 42. E is D + 1 to 43.
F is E plus one to 44. Alright, this row and this column.
The first time through, Cells(1,1) is going to be equal to.
We are going to concatenate together, whatever A is with a dash B dash C dash.
All the way on out to F. No dash after F.
Counter equals counter plus one. Now, this takes some time.
On one computer here it took about an hour to generate all of these, and I'm not going to make you watch that.
But it's very tedious to not know if it's working or if it's hung up.
So every 25,000 or so. Counter equals counter plus one.
If the counter divided by 25,000, if that remainder is equal to zero, then save the workbook.
And then I can look in Windows Explorer and see that it's counting up. Add 1 to the row.
If the row becomes equal to Max Row, then set the row back to one and this column equals this column plus one. End if there.
And then it just goes backwards Next F, E, D, C, B, A.
Now I don't want this whole thing to run, but let's just get to the 1st 25,000.
That'll give us a great indication of what's going on.
So we have a macro called ListThemAll. I will close the VBA module. Close the VBA window.
And then here list the macros. We find ListThemAll and click run.
Now that was fast. That's really encouraging.
The 1st 25,000 happened that fast. Let's switch back to Excel and we got 1-2-3-4-5-6.
1-2-3-4-5-7. Let's see how far we got in the first 25,000.
We are up to 1-2-5-13-25-30. Alright, so that's good.
That means that we can just turn off this breakpoint and let the thing run.
But as I mentioned, it's going to take over an hour for the whole thing to run.
Luckily, I've already run it just to see if it would work.
Alright, here's the one that finished. So we have A1 to A1048575.
If I choose all of these cells, including column G.
And we look down here the count 7,059,052, which I think is pretty much what I predicted.
So there are all the lottery combinations.
Now I know lotteries are different depending on where you are.
For example, Powerball in 2021, five balls from 1 to 69.
So there's the first five loops from 69 back to 65 and then the Powerball can be from 1 to 26. It's a different color ball, the red ball.
So that's how you would code up the Powerball.
You can adapt this for just about any lottery system.
If you want learn about macros, check out the book that Tracy and I wrote Excel 2016 VBA and Macros.
There's actually a 2019 version and soon a 2021 version. They're all pretty much the same.
Not a lot of change in VBA over the years.
If you like these videos, please, down below. Like, Subscribe, and Ring the bell.
Feel free to post any questions or comments down in the YouTube comments below.
Well, I wan to thank Reid for sending that question and I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel. Hit it, Nancy!

 

[excelNewbie22]

 

[Samgraphics]

@Samgraphics The following code will list the results down the sheet, I think that is what you were expecting.

Sub List5of35ViaArrayOneColumnRange()
'
    Dim ArraySlotCount                  As Long
    Dim Ball_1                          As Long, Ball_2 As Long, Ball_3 As Long, Ball_4 As Long, Ball_5 As Long
    Dim ColumnIncrement                 As Long
    Dim CombinationCounter              As Long
    Dim ResultStartRow                  As Long
    Dim ResultEndRow                    As Long
    Dim MaxWhiteBallValue               As Long
    Dim TotalExpectedCominations        As Long
    Dim ThisColumn                      As Long
'
    Const MaxRows As Long = 65536                                                                           ' Set to maximum number of slots in Array
    Const BallsToDraw As Long = 5                                                                           ' <--- Set the number of balls to be drawn
    MaxWhiteBallValue = 35                                                                                  ' <--- Set to highest value of white ball
'
    Dim CombinationsArray(1 To MaxRows, 1 To BallsToDraw)   As Variant                                      ' Set Length and Width of array
'
    ArraySlotCount = 0                                                                                      ' Initialize ArraySlotCount
    ColumnIncrement = BallsToDraw + 1                                                                       ' Set the number of columns to advance
    CombinationCounter = 1                                                                                  ' Initialize CombinationCounter
    ResultEndRow = 0
    ResultStartRow = 1
    ThisColumn = 1                                                                                          ' Initialize 1st column to display results in
    ArraySlotCount = 0                                                                                      ' Initialize ArraySlotCount
    TotalExpectedCominations = Application.Combin(MaxWhiteBallValue, BallsToDraw)                           ' Expected # of total combinations
'
    Application.ScreenUpdating = False                                                                      ' Turn Screen Updating off
'
    For Ball_1 = 1 To MaxWhiteBallValue - 4                                                                 ' Establish loop for 1st ball
        For Ball_2 = (Ball_1 + 1) To MaxWhiteBallValue - 3                                                  '   Establish loop for 2nd ball
            For Ball_3 = (Ball_2 + 1) To MaxWhiteBallValue - 2                                              '       Establish loop for 3rd ball
                For Ball_4 = (Ball_3 + 1) To MaxWhiteBallValue - 1                                          '           Establish loop for 4th ball
                    For Ball_5 = (Ball_4 + 1) To MaxWhiteBallValue                                          '               Establish loop for 5th ball
                            ArraySlotCount = ArraySlotCount + 1                                             '                       Increment ArraySlotCount
'
'                           Save combination into array
                            CombinationsArray(ArraySlotCount, 1) = Ball_1                                   '                       Save ball number to array
                            CombinationsArray(ArraySlotCount, 2) = Ball_2                                   '                       Save ball number to array
                            CombinationsArray(ArraySlotCount, 3) = Ball_3                                   '                       Save ball number to array
                            CombinationsArray(ArraySlotCount, 4) = Ball_4                                   '                       Save ball number to array
                            CombinationsArray(ArraySlotCount, 5) = Ball_5                                   '                       Save ball number to array
'
                            CombinationCounter = CombinationCounter + 1                                     '                       Increment CombinationCounter
'
                            If CombinationCounter Mod 550000 = 0 Then                                       '                       If CombinationCounter = 550k then ...
'                               Update StatusBar about every 10 seconds
                                Application.StatusBar = "Result " & CombinationCounter & " on way to " & TotalExpectedCominations
'
                                DoEvents                                                                    '                           DoEvents
                            End If
'
                            ResultEndRow = ResultEndRow + 1                                                 '                       Increment ResultEndRow
'
                            If ArraySlotCount = MaxRows Then                                                '                       If ArraySlotCount=array max slots
'                               Dump contents of CombinationsArray to the screen
                                Range(Cells(ResultStartRow, ThisColumn), Cells(ResultEndRow, ThisColumn + BallsToDraw - 1)) = CombinationsArray
'
                                Erase CombinationsArray                                                     '                           Erase contents of array
                                ArraySlotCount = 0                                                          '                           Reset ArraySlotCount
                                ResultStartRow = ResultStartRow + MaxRows
                            End If
                    Next
                Next
            Next
        Next
    Next
'
    Range(Cells(ResultStartRow, ThisColumn), Cells(ResultEndRow, ThisColumn + BallsToDraw - 1)) = CombinationsArray  ' Dump contents of last array to the screen
    Columns.AutoFit                                                                                         ' Resize all columns to fit the data within them
    Application.StatusBar = "Completed!"                                                                    ' Let user know via status bar that program is done
'
    Application.ScreenUpdating = True                                                                       ' Turn Screen Updating back on
End Sub

HI JOHNNY, I'm in need of your help again. Can you please help me modify the above code to do two things?

1. The first would be to include bonus numbers 1 - 12.

2. The second is to be able to enter the number list that I want. Say for example I have 10 numbers I like as my favorite(and they're not sequential. So not from 1 to 10 but like 2,4,8,16,20 etc) and I only wanted it to list all the possible combinations of these 10 numbers plus my favorite bonus numbers.

I know that the list because it includes the bonus numbers could be huge, I was wondering if I could alter it so I can do it in pieces? so instead of the array starting at 1,2,3,4,5 , 1,2,3,4,6, etc, it starts at the 2s, 2,3,4,5,6, and then it stops generating after it has listed all the 2s combinations? Hope that makes sense.


Thank you so much for your help.