Excel - Generating All Possible Lottery Combinations in Excel - Step by Step Tutorial - Episode 2441

Microsoft Excel Tutorial: List all lottery combinations.

Welcome to episode 2441 of the MrExcel Netcast, where we will be discussing how to generate all possible lottery combinations in Excel. This is a question sent in by one of our viewers, Reid, who wants to find all combinations of six numbers from 1 to 44. As we all know, winning the lottery is not an easy feat, and with 44 numbers to choose from, there are a staggering 7,059,052 possible combinations. This is where Excel comes in handy, as it can help us generate a list of all these combinations.

The first challenge we face is that Excel only has 1,048,576 rows, which is not enough to accommodate all the combinations. To overcome this, we will be using VBA (Visual Basic for Applications) to automate the process. If you are new to VBA, don't worry, I will guide you through the steps. First, we need to enable the Developer tab in Excel by right-clicking and selecting "Customize the Ribbon". Then, we will insert a new module and paste the code that will be provided in the YouTube description.

The code, called "ListThemAll", will start in column A and row 1, and will have a counter to keep track of the number of combinations. Once we reach the maximum number of rows in the spreadsheet, which is determined by the "Rows.Count" function, we will move on to the next column. To speed up the process, we will turn off screen updating. Since the digits cannot be arranged in sequence, we know that the first number chosen can't be higher than 39. Therefore, we will use a loop to generate all possible combinations, starting with 1 to 39 for the first number, and then incrementing the second number by 1 each time. This process will continue until we reach the last number, which will be 44.

As the code runs, it will save the workbook every 25,000 combinations, and we can monitor its progress in Windows Explorer. Once it finishes, we will have a list of all 7,059,052 lottery combinations in Excel. Of course, this code can be adapted for different lottery systems, such as Powerball, where there are five balls from 1 to 69 and a Powerball from 1 to 26. If you want to learn more about macros and VBA, check out our book "Excel 2016 VBA and Macros" written by Tracy and myself.

Thank you for watching this episode of the MrExcel Netcast. If you found this tutorial helpful, please like, subscribe, and ring the bell to be notified of our future videos. Also, feel free to leave any questions or comments in the YouTube comments section below. I want to thank Reid for sending in this question, and I hope this tutorial has helped you in generating all possible lottery combinations in Excel. See you next time for another netcast from MrExcel. Hit it, Nancy!

Table of Contents
(0:00) Problem Statement: Use Excel to generate all lottery combinations
(0:35) Use COMBIN function to figure out how many combinations
(1:03) Changing Macro Security
(1:17) Add Developer tab in Excel
(1:35) Insert a VBA module and paste VBA code
(1:55) Explaining the VBA
(3:20) Saving the workbook every 25K records
(4:10) Run the macro
(5:09) Customizing for your lottery
(5:32) Buy the VBA book
(5:51) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial

This video answers these common search terms:
Concatenate in Excel
Developer tab in Excel
Excel macros
Excel Project Explorer
Excel rows limit
Excel screen updating
Excel VBA
Learn Excel
Lottery combinations
Powerball lottery

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.

[gerdsfargen]

Mr Excel is living legend! Been learning everything I need to in Excel from his tutorials for years. Bill Jelen!

[simfinso]

Thanks God.Mr.Excel is there on this planet for such complex problems. Thanks mr.Excel.

[excelisfun]

That is some of the coolest VBA code I have seen you podcast Thanks, Mr Excel : ) : ) : ) : )

[blgxpo]

Great tutorial! Thank you for posting this!
Could you please explain if it's possible to exclude from the list certain combinations based on specific criteria (like: no more than 3 consecutive numbers, all numbers odd, all numbers even, not more than 3 numbers ending in the same digit, etc.).
Your help is greatly appreciated, thanks again!

[TonyJackson]

I've been looking for this for a few years

[wayneedmondson]

Hi Mr. Excel. What an awesome bit of code there! Thanks for sharing it. Also, you snuck in the COMBIN() function at the beginning of the video. That's one that probably many people don't know about. Thanks for all the great tips :)) Thumbs up!!

[josipkova]

Hi @MrXL can you maybe make a script for printing full system combinations for lottery (I mean when you have certain numbers anf you made combinations from them). Thanks!

[darrylmorgan]

Really Neat VBA Code..Thank You Mr Excel :)

[bma]

This worked great. I changed it a little for Powerball and so far my computer has been running for the last 12 hours and still have a ways to go. Do you still have your book and if so does it contain the updates with the latest Excel?

[ruckus]

Is there a way to do this with choosing 5 numbers from 1-69 but excluding any combinations that have 4 or 5 consecutive numbers in a row? Example 1-2-3-4-10 or 32-33-34-35-54 would be excluded.

[deninsrmic]

Great tutorial Bill, got your great book on VBA 2010 & 2016. Shame you are not offering any VBA courses as books are well explained.

[cl]

Can you do excel for powerball and add an additional column for sums?
I have other questions, but wanted to know that first. Thank you

[marvinhife]

Hi, how we should do or filter from all combinations generated those list of all lotto results that came out already, and just remain those that has not come out yet. Lets say I have all the results since from year 2000-2022 and I used your lottery combinations to generate all the 6/55 combinations, then in my list of results from year 2000-2022 I wanted to removed them from the generated combinations of 6/55 and remain those that did not came out yet?

[TGMoney-Keasy]

I’ve been looking for something in excel that compares historical data to all possible combinations to determine how many of the possible combinations have been drawn and number of times any combination of numbers were drawn repeatedly 😊

[devonjansen]

Interesting! Kindly advise what about running the same code for lotto numbers 1-49?

[josestudyexcel]

Can you make a formula where it generates a series of lottery number combinations out of those most frequent numbers? Let's say I want to generate 50 lottery number combinations out of 18 frequent numbers.

[AlexD-xoji]

I've been looking for a way to use Excel more effectively to track lottery numbers and analyze the data

[MohammedAbdulhamid-vvks]

Hi MR Excel thank you for the great tutorial, we are following you from Norway I was wondering how can I modify the code if I only have 34 digits and want it to list all combinations of 7 digits? thank you in advance

[kazadimeji]

Thank you very much for the knowledge transmitted in this video, but I can't understand how you managed to align the combinations in columns A, B, C, D, F, G. I can't figure it out. do and I limit only in column A. Please help me.

[barthbriders]

Bonjour
J’ai utilisé cette méthode selon votre explication sauf que j’y arrive toujours pas à chaque fois l’action est interrompue

Si quelqu’un pouvait le faire et me l’envoyer en fichier PDF, un tirage 6 boules sur 32 sans remise sa donne 906 192 combinaisons possibles.

Merci pour votre diligence