Thanks NickKey! Ever since 1985 when I used a spreadsheet to with $690 in the lottery and then to win a trifecta at the Kennel Club, I was hooked. Glad to help!
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!!
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!
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!
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.
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?
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?
What will be the code for the combination 7 numbers between 1 and 50 and Combination 6 numbers 1 and 49. Will those codes run in 16 GB RAM with 64 Bit.
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.
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 😊
ich verfüge über eine Excel-Liste der bisherigen Ziehungen und kann hier auch bereits eine Menge interessante Auswertungen machen. Für die aktuellste Tippreihe sind die Erfahrungen der vergangenen Ziehungen relevant.
How about permutations and combinations in excel when you have repeating numbers like in the pick-3 or pick-5 type lotteries? What I'm attempting is an equation that looks at 5 columns and returns the number of permutations and combinations. Any thoughts as to how this can be accomplished? Example 12345 has 120 permutations 1 combination
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
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
I'm confused. I'm using the Powerball 2021 and it only goes until 24999. It doesn't give me all the combinations and I don't have the breakpoint on anymore. Could I get some help on it please?
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.
Hi, I like your video. I have an excel spreadsheet for football bettings. 10 matches. 59049 variations are possible. I want to write a program that can calculate all the possible variations automatically each time I add the teams and the odds to the spreadsheet. Home/Draw/Away Can you help me with this?
7 million !! I wish I had known when you were going to try such an option. Let me prepare 2 million options for you. Our chance of winning would be 70%. cantact me : www.lottocombination.com
What I am looking for is a way to get Excel to generate a list of the numbers that appear with each other in a group of drawings I select. Not sure which function or functions to use to do that.
Hi Mr. Excel, I came across this tutorial searching for Combo mixes with no repeats and your presentation caught my eye. I am trying to run or create a report in excel that will give me all possible combinations with a list of 10 items each having 3 possible outcomes, how would you generate a list without repeating any combinations?
There are 59049 combinations. Here are the steps: 1. Fill A1:J1 with Item 1, Item 2, Item 3, ... Item 10 2. Fill B1:J2 with 1 3. Formula in J3 is =IF(J2=3,0,J2)+1 4. Formula in I3 is =IF(J3
make some conditional number extracting from 49 numbers? Any 5 numbers from 49 ok 7 million combinatios. any 5 if 6 in the firtst 5000 lines or similar?
I was doing word column combinations in my excel. And rows are continuing loding till now. So can I save it.. for stop loading rows and catch up already loaded rows. ?????
I REALLY....REALLY NEED HELP WITH MY SITUATION, PLEASE! I have combinations in multiple cell blocks. I need to add a selection of numbers to all cellblocks while maintaining the correct numerical order of the combination. For example: I have cell blocks containing such combinations as: 03 05 09....15 17 21...ect and I want to add 2 even numbers say 2 and 4. How can I add to cell blocks making them 02 03 04 05 09, ect ??? BIG thanx!
Hi MrExcel, I'm so glad I came across this video. Thank you so much for posting this information. We really do appreciate it. I have no issues with the initial VBA macro you created (6/44). I'm wondering, how would the VBA macro look if I wanted to find all different 5 number variation combos for 30 numbers, not in any particular number sequence. For example 1,3,6,10,18,27,31,39,43.... (The numbers are between 1 and 60 for this example). How would that VBA macro look? Thanks in advance, Sir!
@MrXL Any help with this question would be greatly appreciated. Is my question easy to understand? If not, I have no problem rewording it. Thanks in advance!
Is there a way to list all permutations given data multiple columns? An example would be, there are 5 slots and the data for those slots exist in multiple columns and there is a different number of data points per column. I want to list all permutations. Is this even a thing? Edit: Thank you
In a Pick 5 game where you choose five digits from 0 to 9, the odds of a single straight bet being correct are one in 100,000. If you play 100 games, the odds of one of them being correct are one in 1000. If you choose 5 different digits and box them, (known as a 120-way box), then the odds of a single ticket winning are 1 in 833. If you play this game 100 times, then you have a one in 8.33 odds of winning. The payout for a $1 bet is $416.
Hello Bill,anoop here. You know i have been searching these since 7 yrs.shane that i don't have a computer and not enough computer literate.is possible for tou to calculate that possibilities for feom.1 to 40 please it will be a great help for and the kids for the orphanage that am working for. Thank you in advance.
Hi Mr Excel, I love your videos, this one got me interested, my question is what if I had to choose a 7 number combination of the numbers 1 to 44 what will the code be
Choosing 7 from 44 is a lot of numbers.... over 38 million. This will most likely crash Excel before it finishes (unless you are running 64-bit Excel with 16 or 32 GB of RAM). Sub ListFrom44Choosing7() TC = 1 ' ThisColumn TR = 1 ' ThisRow Ctr = 1 ' Ctr = 1 MaxRows = Rows.Count Application.ScreenUpdating = False ' Last possible is 44-43-42-41-40-39-38 For a = 1 To 38 For b = (a + 1) To 39 For c = (b + 1) To 40 For d = (c + 1) To 41 For e = (d + 1) To 42 For f = (e + 1) To 43 For g = (f + 1) To 44 Cells(TR, TC).Value = a & "-" & b & "-" & c & "-" & d & "-" & e & "-" & f & "-" & g Ctr = Ctr + 1 If Ctr = 100 Then Exit Sub If Ctr Mod 100000 = 0 Then ThisWorkbook.Save End If TR = TR + 1 If TR = MaxRows Then TR = 1 TC = TC + 1 End If Next g, f, e, d, c, b, a Application.StatusBar = False End Sub
Hello Mr Excel! Please Can You Tell me How can I compare the combinations generated in the form presented a-b-c-d-e with the lottery results that are numbers presented in separate cells in order to determine which combinations are winning?
Is this some sort of Keno game? Sub Choose14From30() TC = 1 ' ThisColumn TR = 1 ' ThisRow Ctr = 1 ' Ctr = 1 MaxRows = Rows.Count Application.ScreenUpdating = False ' Last possible is 17-18-19-20-21-22-23-24-25-26-27-28-29-30 For A = 1 To 17 For B = (A + 1) To 18 For C = (B + 1) To 19 For D = (C + 1) To 20 For E = (D + 1) To 21 For F = (E + 1) To 22 For G = (F + 1) To 23 For H = (G + 1) To 24 For I = (H + 1) To 25 For J = (I + 1) To 26 For K = (J + 1) To 27 For L = (K + 1) To 28 For M = (L + 1) To 29 For N = (M + 1) To 30 Cells(TR, TC).Value = A & "-" & B & "-" & C & "-" & D & "-" & E & "-" & F & "-" & G & "-" & H & "-" & I & "-" & J & "-" & K & "-" & L & "-" & M & "-" & N Ctr = Ctr + 1 If Ctr > 99 Then Exit Sub If Ctr Mod 1000000 = 0 Then ThisWorkbook.Save End If TR = TR + 1 If TR = MaxRows Then TR = 1 TC = TC + 3 End If Next N, M, L, K, J, I, H, G, F, E, D, C, B, A Application.StatusBar = False End Sub
Mr. Excel. Instead of aggregating all possible number combinations at one time. Is there a way to aggregate only the possible number combinations starting with 1. So for example in 5 digit combination 1 to 70, which only start with 1. I would like to create a separate page for combinations starting with 1, then another page for combinations starting with 2, etc. Also, the way the code suggested here populated each number combination in column a (1-2-3-4-5). Is there a way to generate the number combination where the combinations are generated as A - 1, B-2, C-3, D-4, E-5? I believe if these methods could be utilized it would make it easier to disseminate the data.
Let's start with your second question. In many of these lottery systems, we are generating 40 million cells. If you would instead list them into five columns, then you are generating 200 million cells and it is far more likely that you will need 64-bit Excel. You can see many comments from people who have a machine that is stopping long before the program can run because they are running 32-bit Excel with not enough memory. If you use 5 cells instead of 1 then it dies much sooner. Second, it is very easy to later split the data into multiple columns. Choose a range of interest - perhaps A2:A50000. In Excel, click the Data tab along the top. Just over half way across, there is a tool called Text To Columns. Select that tool. In Step 1, choose Delmited and click Next. In Step 2, unselect Tab. Choose Other. In the Other box, type a hyphen. Click Finish. Third, if you really want to build the worksheet with the numbers in five columns, you would replace this line: Cells(TR, TC).Value = A & "-" & B & "-" & C & "-" & D & "-" & E With This Cells(TR, TC).Resize(1, 5).Value = array(A, B, C, D, E) Later in the macro, change this line: TC = TC + 3 to TC = TC + 7 For the first question, I don't currently have that code. It is a bit more complex than what I would write for free. What would you think about generating 66 different workbooks? Trying to put 66 large worksheets in one workbook is going to run into memory issues.
@@BillJelen Thanks for your reply. I ran the program you made available here. I will use it when I transfer my data to my other lottery workbook. I ran into the problem of memory. So I am wondering if after I transfer my data and delete it. Is there a way to start the list of lottery numbers where the program stopped due to insufficient memory?
@@lisagust3806 Let's say that the last number before the memory problem was 4-8-12-16-21 You could have the program skip all of the groups starting with 1, 2, and 3 by changing this line of code: For a = 1 to 31 to be For a = 4 to 31 You would still get duplicates starting with 4-5-6-7-8 up through 4-8-12-16-21. If you are running out of memory before you get all the way through the numbers that start with 1, then I it would be harder to restart.
How do I generate different combinations from a 4x11 grid of numbers? numbers only 0-9 are used. I have a list of different numbers but need all possible combinations.
This is nice but is there a way in Excel to find how many combinations are in a range of sums. So if i wanted to see which combinations are in the sum range of 100, could you make excel list all those combinations in that specific range of sums? That would be really useful to me.
The only way that I can think of doing this is to run the macro in this video. Add a new column to the right that does the SUM. Then use Data, Filter to look for combinations that match 100 or any number.
39 numbers, chosen 5 at a time. The first one is 1-2-3-4-5 and the last one is 35-36-37-38-39. The VBA code would be: Sub ListThemAll() TC = 1 TR = 1 Ctr = 1 MaxRows = Rows.Count EndCell = 7059052 Application.ScreenUpdating = False For a = 1 To 35 For b = (a + 1) To 36 For c = (b + 1) To 37 For d = (c + 1) To 38 For e = (d + 1) To 39 Application.StatusBar = Ctr & " on way to " & EndCell Cells(TR, TC).Value = a & "-" & b & "-" & c & "-" & d & "-" & e 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 e Next d Next c Next b Next a Application.StatusBar = False Application.ScreenUpdating = True End Sub
Those are called Permutations instead of Combinations. Excel offers =PERMUT to count them. But I don't have code to create them. You might try posting a new thread at www.mrexcel.com/board/forums/excel-questions.10/
Giving the winning numbers would mean that a million people share the jackpot and everyone gets a few dollars. I am convinced the key is to come up with a method that no one else is using. That way, when you hit, you are the only one with the numbers and the jackpot is not split.
This is an interesting question. I can picture two different scenarios. Which one is yours? 1) Do you mean that you want to point to a cell that contains 15-25-5-12-18 and it would say that this is a permutation of 5-12-15-18-25? Enter the five numbers in A1, and then use this formula: =TEXTJOIN("-",,SORT(TEXTSPLIT(A1,,"-")+0)) 2) Or do you want to pass five numbers 5-12-15-18-25 and generate all 120 possible permutations of those five numbers? This one is definitely not built into Excel. It could be done with VBA in Excel, but let me know if this is what you really want.
Make the workbook is saved with a name before you run the macro. The program tries to do an AutoSave at row 25000. If the workbook doesn’t already have a path and file name it could stop there.
Sub Choose5From50() TC = 1 ' ThisColumn TR = 1 ' ThisRow Ctr = 1 ' Ctr = 1 MaxRows = Rows.Count Application.ScreenUpdating = False ' Last possible is 46-47-48-49-50 For a = 1 To 46 For b = (a + 1) To 47 For c = (b + 1) To 48 For d = (c + 1) To 49 For e = (d + 1) To 50 Cells(TR, TC).Value = a & "-" & b & "-" & c & "-" & d & "-" & e Ctr = Ctr + 1 If Ctr Mod 250000 = 0 Then ThisWorkbook.Save End If TR = TR + 1 If TR = MaxRows Then TR = 1 TC = TC + 1 End If Next e, d, c, b, a Application.StatusBar = False End Sub
Mr. Excel, I tried to to this with a 5 numbers 1-69 without a powerball and something went haywire. I believe there should be 11,238,513 combinations. Can you explain how to alter the formula for just the 5 number without the 6th powerball number please & thank you.
I agree with your 11,238,513. Here is the code to generate it: Sub Choose5From69() TC = 1 ' ThisColumn TR = 1 ' ThisRow Ctr = 1 ' Ctr = 1 ' 5 of 69 will generate 11,238,513 combinations ' according to =COMBIN(69,5) MaxRows = Rows.Count Application.ScreenUpdating = False ' Last possible is 65-66-67-68-69 For a = 1 To 65 For b = (a + 1) To 66 For c = (b + 1) To 67 For d = (c + 1) To 68 For e = (d + 1) To 69 Cells(TR, TC).Value = a & "-" & b & "-" & c & "-" & d & "-" & e Ctr = Ctr + 1 If Ctr Mod 25000 = 0 Then ThisWorkbook.Save End If TR = TR + 1 If TR = MaxRows Then TR = 1 TC = TC + 1 End If Next e, d, c, b, a Application.StatusBar = False End Sub
Hi Mr. Excel, thanks for the video. If you had a list of 24 names and you wanted to generate all possible 6 name combinations, how would you generate a list without repeating any combinations?
Hi Ryan First - in any cell, enter =COMBIN(24,6) and press Enter. This will tell you there will be 134,596 combinations of 24 names. To generate the names: List the 24 names in cells A1 through A24. Run this macro: Sub List24NamesChosenBy6() TR = 1 ' ThisRow Application.ScreenUpdating = False ' Last possible is 19-0-21-22-23-24 For a = 1 To 19 For b = (a + 1) To 20 For c = (b + 1) To 21 For d = (c + 1) To 22 For e = (d + 1) To 23 For f = (e + 1) To 24 Cells(TR, 3).Value = Cells(a, 1).Value Cells(TR, 4).Value = Cells(b, 1).Value Cells(TR, 5).Value = Cells(c, 1).Value Cells(TR, 6).Value = Cells(d, 1).Value Cells(TR, 7).Value = Cells(e, 1).Value Cells(TR, 8).Value = Cells(f, 1).Value TR = TR + 1 Next f, e, d, c, b, a Application.ScreenUpdating = True End Sub On my machine, it takes about 46 seconds to run and generates all combinations in C1:H134596.
@@MrXL What would be the difference if I was to put the names through A4 to A27? I did not figure out where it's taken into consideration in your script. Thank you so much!
Sub List20NamesChosenBy6() TR = 29 ' ThisRow Application.ScreenUpdating = False ' Last possible is 15-16-17-18-19-20
For a = 4 To 18 For b = (a + 1) To 19 For c = (b + 1) To 20 For d = (c + 1) To 21 For e = (d + 1) To 22 For f = (e + 1) To 23 Cells(TR, 2).Value = Cells(a, 1).Value Cells(TR, 3).Value = Cells(b, 1).Value Cells(TR, 4).Value = Cells(c, 1).Value Cells(TR, 5).Value = Cells(d, 1).Value Cells(TR, 6).Value = Cells(e, 1).Value Cells(TR, 7).Value = Cells(f, 1).Value TR = TR + 1 Next f, e, d, c, b, a
hey Mr Excel been trying this lottery combination but only one row is generated until 24999 only I waited for more than an hour but still nothing. how do I know its working or not because its been hours but still only one row
Here is my trick. The macro is set to save every 25000 rows. I open Windows Explorer and set the View to Details. In this view, you can see the last date and time the workbook is saved. Start the macro running. Watch for the file date and time to update.
Hello Mr Excel. I have tried this it is still not working 😭. Please help me how to do it please please. I have been trying do many times but nothing still. Any alternative way or something I can do ?
@@shonnellYouCantHandlethetruth Sure. How can you get me the workbook? I went to your "About" page to find your e-mail, but you don't list it there. Go to my About page, scroll down, and you can find my e-mail. Send me the example.
Sub ListThemAll35() TC = 1 ' ThisColumn TR = 1 ' ThisRow Ctr = 1 ' Ctr = 1 MaxRows = Rows.Count Application.ScreenUpdating = False ' Last possible is 39-38-37-36-35 For a = 1 To 35 For b = (a + 1) To 36 For c = (b + 1) To 37 For d = (c + 1) To 38 For e = (d + 1) To 39 Cells(TR, TC).Value = a & "-" & b & "-" & c & "-" & d & "-" & e Ctr = Ctr + 1 If Ctr Mod 25000 = 0 Then ThisWorkbook.Save End If TR = TR + 1 If TR = MaxRows Then TR = 1 TC = TC + 1 End If Next e, d, c, b, a Application.StatusBar = False End Sub
If I posted that formula here in public, then everyone would play the same number and no one would win anything. (Imagine splitting a $1 million jackpot 300 million ways). You’ve got to come up with the magic mojo yourself. I can just give you tools to help analyze.
An easy solution in Power Query is possible, and it takes less time to calculate than this formidable VBA-solution (some 15 minutes). (No offence, Mr.Excel :-) The twist here? Well, I wish I could post it here, but CZcams won’t let me. :-( (No, no clickbait: there’s nothing to click. Just a fact.)
At 1:48, there are six lines of code ending in 39, 40, 41, 42, 43, and 44. Edit those to end in 44, 45, 46, 47, 48, and 49 instead: For a = 1 to 44 For b = (a+1) to 45 For c = (b+1) to 46 For d = (c+1) to 47 For e = (d+1) to 48 For f = (e+1) to 49
Hi Mr. Excel! Can you please help me? I need a code for a lottery which requires 6 numbers out from 1 to 58. I know that it has 40 million+ combinations, and it will surely take many hours to run, but it will be worth it as a guide for me in winning this. Thank you!
If you study this code compared to the code in the video, can you see the simple changes you need to have the numbers end at 58? Sub Choose6From58() TC = 1 ' ThisColumn TR = 1 ' ThisRow Ctr = 1 ' Ctr = 1 MaxRows = Rows.Count Application.ScreenUpdating = False ' Last possible is 58-57-56-55-54-53 For a = 1 To 53 For b = (a + 1) To 54 For c = (b + 1) To 55 For d = (c + 1) To 56 For e = (d + 1) To 57 For f = (e + 1) To 58 Cells(TR, TC).Value = a & "-" & b & "-" & c & "-" & d & "-" & e & "-" & f Ctr = Ctr + 1 If Ctr Mod 1000000 = 0 Then ThisWorkbook.Save End If TR = TR + 1 If TR = MaxRows Then TR = 1 TC = TC + 3 End If End If Next f, e, d, c, b, a Application.StatusBar = False End Sub
@@MrXL I've tried it on my laptop which has only 4 GB of ram and run it for about 30 minutes then I've got 25 million combinations but the pc was throttling, and I've stopped the process and decided to rerun it when I'm not busy in editing some projects.
@@MrXL Hi MrExcel! Great job with the excel btw. I want to know if the code above can be sequenced in normal counting order for those 6 numbers or they will be all mixed?
I changed the code and it doesn't work. I need it for 11/22. Then I will delete all that look like 1,2,3,4,5,6,7,8.. and 1,3,5,7,9, and 2,4,6,8 etc. Then deletion with the help of duplicates that have been extracted so far. That is idea.
@@MrXL Unfortunately, I did not save the edited code. I'm not an expert in excel, so I would ask, if you have time, to set the code to list all possible combinations from 1-22, if a total of 11 numbers are drawn daily.
Sub ListThemAll TR = 2 for a = 0 to 9 for b = 0 to 9 for c = 0 to 9 Cells(TR, 1).Value = a & "-" & b & "-" & c TR = TR + 1 Next c Next b Next a End Sub
@@MrXL Thank you so much for getting back to me. One more question if I could but how could I input previous winning numbers and use that algorithm to find the next possible numbers
I just have the Excel skills,@@zhannadilisio7374 . If I had the winning algorithm, I sure wouldn't post it in a public video because then everyone would win and it would dilute the jackpot pool. Many people have hired me over the last decades to write the Excel formulas to calculate algorithms that they invent. Those are all confidential and I can't share them.
Hello, Mr. Excel. Thank you for the videos and the information. I like all your videos, and you have my subscription. After all the list of numbers, is it possible to remove, giving 6 digits, all the lines with 3 or more numbers in common? For example, I have all the possible combinations in excel; I want to insert 1-2-3-4-5-6, and now I want to eliminate all the lines that have 3 or more numbers in common. Thank you very much for your knowledge.
Let me make sure I have this correct. You will select a set of six numbers. Maybe 13-14-36-45-57-67. You want to eliminate all of the possibilities that would have matched 3 or more of those six numbers? What a great idea. But tough to do. Let me think about that one for a bit.
@@AndreaVacca87 I ran the macro this morning. It removed 179,494 of the 7,059,052 possible combinations, leaving 6,879,558 combinations left. Two things: 1) Because it is difficult to parse the numbers out of a text value like "7-8-22-29-32-38", I chose to remove the numbers as I wrote the combinations to the sheet. 2) There are six lines in the code where you have to edit the numbers from the last drawing. They look like this: LastDraw(1) = 2 LastDraw(2) = 4 LastDraw(3) = 17 LastDraw(4) = 21 LastDraw(5) = 24 LastDraw(6) = 43 In my case, the numbers to remove were 2-4-17-21-24-43. You can see how to change the code for your numbers to remove, right? Here is the new code: Sub ListThemAllUnlessThreeMatch() TC = 1 ' ThisColumn TR = 1 ' ThisRow Ctr = 1 ' Ctr = 1 Dim LastDraw(1 To 6) As Integer ' Change these lines to show the numbers in the last drawing ' Logic below will eliminate any combinations that have three of these LastDraw(1) = 2 LastDraw(2) = 4 LastDraw(3) = 17 LastDraw(4) = 21 LastDraw(5) = 24 LastDraw(6) = 43 MaxRows = Rows.Count Application.ScreenUpdating = False ' Last possible is 39-40-41-42-43-44 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 MatchCtr = 0 ' Count how many match the LastDraw For j = 1 To 6 If a = LastDraw(j) Then MatchCtr = MatchCtr + 1 If b = LastDraw(j) Then MatchCtr = MatchCtr + 1 If c = LastDraw(j) Then MatchCtr = MatchCtr + 1 If d = LastDraw(j) Then MatchCtr = MatchCtr + 1 If e = LastDraw(j) Then MatchCtr = MatchCtr + 1 If f = LastDraw(j) Then MatchCtr = MatchCtr + 1 Next j If MatchCtr < 3 Then Cells(TR, TC).Value = a & "-" & b & "-" & c & "-" & d & "-" & e & "-" & f Ctr = Ctr + 1 If Ctr Mod 2500000 = 0 Then ThisWorkbook.Save End If TR = TR + 1 If TR = MaxRows Then TR = 1 TC = TC + 1 End If End If Next f, e, d, c, b, a Application.StatusBar = False End Sub Good luck!
HI MR Excel, thank you for the tutorial, it's great. I was wondering how I should modify the code if I only had 35 numbers and wanted it to list all the combinations of 5 numbers? Thank you in advance.
Are the numbers 1 to 35? Use this: Sub ListThemAll35() TC = 1 ' ThisColumn TR = 1 ' ThisRow Ctr = 1 ' Ctr = 1 MaxRows = Rows.Count Application.ScreenUpdating = False ' Last possible is 31-32-33-34-35 For a = 1 To 31 For b = (a + 1) To 32 For c = (b + 1) To 33 For d = (c + 1) To 34 For e = (d + 1) To 35 Cells(TR, TC).Value = a & "-" & b & "-" & c & "-" & d & "-" & e Ctr = Ctr + 1 If Ctr Mod 25000 = 0 Then ThisWorkbook.Save End If TR = TR + 1 If TR = MaxRows Then TR = 1 TC = TC + 1 End If Next e, d, c, b, a Application.StatusBar = False End Sub Or are you in a system where you need to select 5 numbers from 1 to 60 and you have 35 of those that seem hot at the moment? In that case, it would be easiest if you listed the 35 hot numbers in A1:A35 of Sheet2 and Sheet1 was blank for the combination of numbers. Let me know if this is your situation.
Be aware that there are 38 million combinations. This will likely require over 40 hours to run on a very fast PC. Sub ListFrom44Choosing7() TC = 1 ' ThisColumn TR = 1 ' ThisRow Ctr = 1 ' Ctr = 1 MaxRows = Rows.Count Application.ScreenUpdating = False ' Last possible is 44-43-42-41-40-39-38 For a = 1 To 38 For b = (a + 1) To 39 For c = (b + 1) To 40 For d = (c + 1) To 41 For e = (d + 1) To 42 For f = (e + 1) To 43 For g = (f + 1) To 44 Cells(TR, TC).Value = a & "-" & b & "-" & c & "-" & d & "-" & e & "-" & f & "-" & g Ctr = Ctr + 1 If Ctr Mod 500000 = 0 Then ThisWorkbook.Save End If TR = TR + 1 If TR = MaxRows Then TR = 1 TC = TC + 1 End If Next g, f, e, d, c, b, a Application.StatusBar = False End Sub
Rather than list ALL combinations: What is required is (a spreadsheet or program) which returns the required set of numbers, based on a position (in your example 1 to 7,059,052) and vice versa: For example Based on 6/44 (7,059,052) Position 6,000,000 = 12 14 21 23 26 28 Set 20 25 30 35 40 44 = 6,911,737 And for the current MegaMillions (5/70 + 1/25) (302,575,349) Position 6,000,000 = 01 07 14 23 34 + (25) Set 20 25 30 35 40 + (05) = 245,618,830 etc.
In cells A1:A10, put 10 position numbers. For example, 6000000 in A1. 6911737 in A2. And then any other position numbers in A3 to A10. Sub StoreInArray() Ctr = 1 Dim Res(7059053) For a = 1 To 39 Application.StatusBar = a & " on way 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 Res(Ctr) = a & "-" & b & "-" & c & "-" & d & "-" & e & "-" & f Ctr = Ctr + 1 Next f Next e Next d Next c Next b Next a ' For the numbers in A1:A10, fill in the results in B For i = 1 To 10 Cells(i, 2).Value = Res(Cells(i, 1).Value) Next i Application.StatusBar = False Application.ScreenUpdating = True End Sub Run the macro. It takes about 40 seconds to load the array and then report the results in B1:B10.
Another option would be to increment the column with 'a' so each column will have ALL the combinations with the same 'a' value by adding "TR = 1" and "TC = TC + 1" before the "Next a" statement and removing the other incrementation statements. As a way to more "reliably" track progress, at the end of each For a...loop, you could set ScreenUpdating = True, TR = 1, TC = TC + 1, Range("A1").Offset(0, TC + 1).Select, ScreenUpdating = False. This would have the values in a column all populate at the same time and select the first cell of the next column so the screen always keeps the last column on the screen. This would only minorly slow down the execution.
Thanks for the good idea about moving each "a" value to a new column. Toggling ScreenUpdating on then off is still no longer working for me in long macros. Once I hit the time limit for Windows to deem that Excel is "Not Responding", the ScreenUpdating trick is not working for me. Strangely, triggering a periodic Save keeps the status bar and ScreenUpdating alive.
Using Excel Online or Microsoft 365, enter this in A1: =SORT(TAKE(SORTBY(SEQUENCE(29),RANDARRAY(29)),6)) Every time you press F9, you will get 6 new numbers.
If you have the newest version of Excel,@@jamijami7586 then the formula will return 6 numbers. I just tried it and it gave me 1, 2, 4, 16, 18, and 24. I press F9 and I then get 4, 15, 23, 25, 27, 29. This formula is using new functions that were introduced in November of 2022. If your Excel is older than that, you can try Excel Online. Steps: 1. Open a new tab in a browser on your computer (Use Edge or Chrome) 2. In the Address bar (not the search box), type Excel.New 3. Copy this formula: =SORT(TAKE(SORTBY(SEQUENCE(29),RANDARRAY(29)),6)) 4. Paste in any cell in Excel
@@MrXL I can understand your formula. If I will put this formula after I will press F9. It's generate one sequence again I press the F9 it's again give another sequence. But I want, if I put any formula in excel, It will show whole 6 digit number in between 1 to 29. I don't know, how many sequences in between 1 to 29. So pls I need while sequences. Help me.
you know this programming ok. But you did not offer people something to use profitable. you can get from excel an5 or 6 numbers from 49 numbers no problem.Have you got a system economical for people with your knowledge to offer to the people?
Choosing 5 from 1 to 36: Sub Choose5From36() TC = 1 ' ThisColumn TR = 1 ' ThisRow Ctr = 1 ' Ctr = 1 MaxRows = Rows.Count Application.ScreenUpdating = False ' Last possible is 32-33-34-35-36 For a = 1 To 32 For b = (a + 1) To 33 For c = (b + 1) To 34 For d = (c + 1) To 35 For e = (d + 1) To 36 Cells(TR, TC).Value = a & "-" & b & "-" & c & "-" & d & "-" & e Ctr = Ctr + 1 If Ctr Mod 25000 = 0 Then ThisWorkbook.Save End If TR = TR + 1 If TR = MaxRows Then TR = 1 TC = TC + 1 End If Next e, d, c, b, a Application.StatusBar = False End Sub
That is some of the coolest VBA code I have seen you podcast about!!!!! Thanks, Mr Excel : ) : ) : ) : )
Thanks Mike!
He is a cool cat just like you!!!
Mr Excel is living legend! Been learning everything I need to in Excel from his tutorials for years. Bill Jelen!
Thanks NickKey! Ever since 1985 when I used a spreadsheet to with $690 in the lottery and then to win a trifecta at the Kennel Club, I was hooked. Glad to help!
@@MrXL How do you use it to win $690? Might also use your technique to win here in Ph! lols😁
Thanks God.Mr.Excel is there on this planet for such complex problems. Thanks mr.Excel.
I've been looking for this for a few years
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!!
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!
Are you a lottery enthusiast?
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!
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.
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?
Thank for the video
Really Neat VBA Code..Thank You Mr Excel :)
Great tutorial Bill, got your great book on VBA 2010 & 2016. Shame you are not offering any VBA courses as books are well explained.
I have a course based on that book: I will put the URL in the video description above.
it is more helpful for further study
Many many thanks sir.
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
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?
What will be the code for the combination 7 numbers between 1 and 50 and Combination 6 numbers 1 and 49. Will those codes run in 16 GB RAM with 64 Bit.
Amazing, thanks Mr Excel!
Glad you liked it!
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.
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 😊
ich verfüge über eine Excel-Liste der bisherigen Ziehungen und kann hier auch bereits eine Menge interessante Auswertungen machen. Für die aktuellste Tippreihe sind die Erfahrungen der vergangenen Ziehungen relevant.
Very good video, I liked it
How about permutations and combinations in excel when you have repeating numbers like in the pick-3 or pick-5 type lotteries?
What I'm attempting is an equation that looks at 5 columns and returns the number of permutations and combinations.
Any thoughts as to how this can be accomplished?
Example 12345 has
120 permutations
1 combination
Can i download your project to test it? When i try your code in Calc it does not work.
Interesting! Kindly advise what about running the same code for lotto numbers 1-49?
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
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
Merci ! excellent
How about 6/42 all combi?
I'm confused. I'm using the Powerball 2021 and it only goes until 24999. It doesn't give me all the combinations and I don't have the breakpoint on anymore. Could I get some help on it please?
Awesome, from Port Orange.
Greetings from just down the coast!
I've been looking for a way to use Excel more effectively to track lottery numbers and analyze the data
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.
Hi,
I like your video.
I have an excel spreadsheet for football bettings.
10 matches. 59049 variations are possible.
I want to write a program that can calculate all the possible variations automatically each time I add the teams and the odds to the spreadsheet. Home/Draw/Away
Can you help me with this?
Mr excel i wish you were in my country i would be a millionaire, i just can't find a person who knew how to do a excel sheets .
I actually bought 7,059,051 lottery tickets once, and I didn't even win. What are the odds?
Using different numbers for each ticket might have helped. D'oh.
Next time you do this, please let me know the one ticket you didn't buy!
How is that even possible?
7 million !! I wish I had known when you were going to try such an option. Let me prepare 2 million options for you. Our chance of winning would be 70%. cantact me : www.lottocombination.com
@@MrXL can you provide all posible combination of 6/45 lotto. if posible please replay sir
What I am looking for is a way to get Excel to generate a list of the numbers that appear with each other in a group of drawings I select. Not sure which function or functions to use to do that.
Can I still generate the combinations but have each number in its own cell?😢
Hi Mr. Excel, I came across this tutorial searching for Combo mixes with no repeats and your presentation caught my eye. I am trying to run or create a report in excel that will give me all possible combinations with a list of 10 items each having 3 possible outcomes, how would you generate a list without repeating any combinations?
There are 59049 combinations. Here are the steps:
1. Fill A1:J1 with Item 1, Item 2, Item 3, ... Item 10
2. Fill B1:J2 with 1
3. Formula in J3 is =IF(J2=3,0,J2)+1
4. Formula in I3 is =IF(J3
How does this need to be coded differently to work in Google Sheets? It won't work for me on there.
make some conditional number extracting from 49 numbers? Any 5 numbers from 49 ok 7 million combinatios. any 5 if 6 in the firtst 5000 lines or similar?
I was doing word column combinations in my excel.
And rows are continuing loding till now. So can I save it.. for stop loading rows and catch up already loaded rows. ?????
I REALLY....REALLY NEED HELP WITH MY SITUATION, PLEASE! I have combinations in multiple cell blocks. I need to add a selection of numbers to all cellblocks while maintaining the correct numerical order of the combination. For example: I have cell blocks containing such combinations as: 03 05 09....15 17 21...ect and I want to add 2 even numbers say 2 and 4. How can I add to cell blocks making them 02 03 04 05 09, ect ??? BIG thanx!
can i get all the # from 1 to 39 plz my computer dont have all those up to date features in xcel
Greetings Mr. Excel!
Do you have a formula for 6-42 all combinations? I'm willing to pay. Thank you and more power. God bless!
Can you replay me the code “ listthemAll” didn’t work for me
Hi MrExcel, I'm so glad I came across this video. Thank you so much for posting this information. We really do appreciate it. I have no issues with the initial VBA macro you created (6/44). I'm wondering, how would the VBA macro look if I wanted to find all different 5 number variation combos for 30 numbers, not in any particular number sequence. For example 1,3,6,10,18,27,31,39,43.... (The numbers are between 1 and 60 for this example). How would that VBA macro look? Thanks in advance, Sir!
@MrXL Any help with this question would be greatly appreciated. Is my question easy to understand? If not, I have no problem rewording it. Thanks in advance!
@MrXL Is this possible what I'm asking, Sir?
Is there a way to list all permutations given data multiple columns? An example would be, there are 5 slots and the data for those slots exist in multiple columns and there is a different number of data points per column. I want to list all permutations. Is this even a thing? Edit: Thank you
Hi Mr excel hope your well, I have a question , what are the odds of choosing 5 correct numbers on a pick 5 entry playing 100 games?
In a Pick 5 game where you choose five digits from 0 to 9, the odds of a single straight bet being correct are one in 100,000.
If you play 100 games, the odds of one of them being correct are one in 1000.
If you choose 5 different digits and box them, (known as a 120-way box), then the odds of a single ticket winning are 1 in 833. If you play this game 100 times, then you have a one in 8.33 odds of winning. The payout for a $1 bet is $416.
Hello Bill,anoop here. You know i have been searching these since 7 yrs.shane that i don't have a computer and not enough computer literate.is possible for tou to calculate that possibilities for feom.1 to 40 please it will be a great help for and the kids for the orphanage that am working for. Thank you in advance.
The macro works, but why does it move so slow?
Hi Mr Excel, I love your videos, this one got me interested, my question is what if I had to choose a 7 number combination of the numbers 1 to 44 what will the code be
Choosing 7 from 44 is a lot of numbers.... over 38 million. This will most likely crash Excel before it finishes (unless you are running 64-bit Excel with 16 or 32 GB of RAM).
Sub ListFrom44Choosing7()
TC = 1 ' ThisColumn
TR = 1 ' ThisRow
Ctr = 1 ' Ctr = 1
MaxRows = Rows.Count
Application.ScreenUpdating = False
' Last possible is 44-43-42-41-40-39-38
For a = 1 To 38
For b = (a + 1) To 39
For c = (b + 1) To 40
For d = (c + 1) To 41
For e = (d + 1) To 42
For f = (e + 1) To 43
For g = (f + 1) To 44
Cells(TR, TC).Value = a & "-" & b & "-" & c & "-" & d & "-" & e & "-" & f & "-" & g
Ctr = Ctr + 1
If Ctr = 100 Then Exit Sub
If Ctr Mod 100000 = 0 Then
ThisWorkbook.Save
End If
TR = TR + 1
If TR = MaxRows Then
TR = 1
TC = TC + 1
End If
Next g, f, e, d, c, b, a
Application.StatusBar = False
End Sub
@@MrXL Thank you very much for this MrExcel 🤩. The PC am using is a 16gig ram I hope it wont melt 😅
Hello Mr Excel! Please Can You Tell me How can I compare the combinations generated in the form presented a-b-c-d-e with the lottery results that are numbers presented in separate cells in order to determine which combinations are winning?
Use uk49
thanks for the video Mr Excel! ... the combination I need help with is for 14 numbers between 1 and 30.
Is this some sort of Keno game?
Sub Choose14From30()
TC = 1 ' ThisColumn
TR = 1 ' ThisRow
Ctr = 1 ' Ctr = 1
MaxRows = Rows.Count
Application.ScreenUpdating = False
' Last possible is 17-18-19-20-21-22-23-24-25-26-27-28-29-30
For A = 1 To 17
For B = (A + 1) To 18
For C = (B + 1) To 19
For D = (C + 1) To 20
For E = (D + 1) To 21
For F = (E + 1) To 22
For G = (F + 1) To 23
For H = (G + 1) To 24
For I = (H + 1) To 25
For J = (I + 1) To 26
For K = (J + 1) To 27
For L = (K + 1) To 28
For M = (L + 1) To 29
For N = (M + 1) To 30
Cells(TR, TC).Value = A & "-" & B & "-" & C & "-" & D & "-" & E & "-" & F & "-" & G & "-" & H & "-" & I & "-" & J & "-" & K & "-" & L & "-" & M & "-" & N
Ctr = Ctr + 1
If Ctr > 99 Then Exit Sub
If Ctr Mod 1000000 = 0 Then
ThisWorkbook.Save
End If
TR = TR + 1
If TR = MaxRows Then
TR = 1
TC = TC + 3
End If
Next N, M, L, K, J, I, H, G, F, E, D, C, B, A
Application.StatusBar = False
End Sub
@@BillJelen thx Mr Excel!! yeah is sort a keno game in a south american country... btw what videos or manuals do you recommend to learn VB from 0?
@@mfariasnav There is a great channel called czcams.com/users/excelvbaisfun
this code you are using is different than the one down you copied. i cant see the last possible line?
The last two lines of code are:
Application.StatusBar = False
End Sub
Hello.. How to work in Emirates draw.. Lottery.. Plz tell me formula
Mr. Excel. Instead of aggregating all possible number combinations at one time. Is there a way to aggregate only the possible number combinations starting with 1. So for example in 5 digit combination 1 to 70, which only start with 1. I would like to create a separate page for combinations starting with 1, then another page for combinations starting with 2, etc. Also, the way the code suggested here populated each number combination in column a (1-2-3-4-5). Is there a way to generate the number combination where the combinations are generated as A - 1, B-2, C-3, D-4, E-5? I believe if these methods could be utilized it would make it easier to disseminate the data.
Let's start with your second question. In many of these lottery systems, we are generating 40 million cells. If you would instead list them into five columns, then you are generating 200 million cells and it is far more likely that you will need 64-bit Excel. You can see many comments from people who have a machine that is stopping long before the program can run because they are running 32-bit Excel with not enough memory. If you use 5 cells instead of 1 then it dies much sooner.
Second, it is very easy to later split the data into multiple columns. Choose a range of interest - perhaps A2:A50000. In Excel, click the Data tab along the top. Just over half way across, there is a tool called Text To Columns. Select that tool. In Step 1, choose Delmited and click Next. In Step 2, unselect Tab. Choose Other. In the Other box, type a hyphen. Click Finish.
Third, if you really want to build the worksheet with the numbers in five columns, you would replace this line:
Cells(TR, TC).Value = A & "-" & B & "-" & C & "-" & D & "-" & E
With This
Cells(TR, TC).Resize(1, 5).Value = array(A, B, C, D, E)
Later in the macro, change this line:
TC = TC + 3
to
TC = TC + 7
For the first question, I don't currently have that code. It is a bit more complex than what I would write for free. What would you think about generating 66 different workbooks? Trying to put 66 large worksheets in one workbook is going to run into memory issues.
@@BillJelen Thanks for your reply. I ran the program you made available here. I will use it when I transfer my data to my other lottery workbook. I ran into the problem of memory. So I am wondering if after I transfer my data and delete it. Is there a way to start the list of lottery numbers where the program stopped due to insufficient memory?
@@lisagust3806 Let's say that the last number before the memory problem was 4-8-12-16-21
You could have the program skip all of the groups starting with 1, 2, and 3 by changing this line of code:
For a = 1 to 31
to be
For a = 4 to 31
You would still get duplicates starting with 4-5-6-7-8 up through 4-8-12-16-21.
If you are running out of memory before you get all the way through the numbers that start with 1, then I it would be harder to restart.
How do I generate different combinations from a 4x11 grid of numbers? numbers only 0-9 are used. I have a list of different numbers but need all possible combinations.
How many numbers are you choosing? Is it one number from each row so four numbers are drawn?
This is nice but is there a way in Excel to find how many combinations are in a range of sums. So if i wanted to see which combinations are in the sum range of 100, could you make excel list all those combinations in that specific range of sums? That would be really useful to me.
The only way that I can think of doing this is to run the macro in this video. Add a new column to the right that does the SUM. Then use Data, Filter to look for combinations that match 100 or any number.
How do you make a generator 0f 39 numbers to make 575,757 combinations
39 numbers, chosen 5 at a time. The first one is 1-2-3-4-5 and the last one is 35-36-37-38-39. The VBA code would be:
Sub ListThemAll()
TC = 1
TR = 1
Ctr = 1
MaxRows = Rows.Count
EndCell = 7059052
Application.ScreenUpdating = False
For a = 1 To 35
For b = (a + 1) To 36
For c = (b + 1) To 37
For d = (c + 1) To 38
For e = (d + 1) To 39
Application.StatusBar = Ctr & " on way to " & EndCell
Cells(TR, TC).Value = a & "-" & b & "-" & c & "-" & d & "-" & e
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 e
Next d
Next c
Next b
Next a
Application.StatusBar = False
Application.ScreenUpdating = True
End Sub
You must be in Missouri. Hello homey!!!!
You can access the mobile application you need from this website www.lottocombination.com
Is there a way of finding all possible combinations for specific numbers? For example, 16,21,35,6 could be 6-16-21-35 or 21-35-6-16.
Those are called Permutations instead of Combinations. Excel offers =PERMUT to count them. But I don't have code to create them. You might try posting a new thread at www.mrexcel.com/board/forums/excel-questions.10/
How do I adopt to 1x49 like Canada 49
how many people won already with this?
Is that possible write code to eliminate the past lottery number so we know which one have high probability?
yes it is possible but you don't need to mess with excel . contact me www.lottocombination.com
Can you tell 6 digit lottery number tricks
Wait you didn’t give the winning numbers 😢
Giving the winning numbers would mean that a million people share the jackpot and everyone gets a few dollars. I am convinced the key is to come up with a method that no one else is using. That way, when you hit, you are the only one with the numbers and the jackpot is not split.
@@MrXL no to me …not you but great point. What iron some have won 3/4 times.
pODERIA POR FAVOR COMPARTILHAS A FORMULA? OBRIGADA
Is there a way i can make a look up formula that finds permutation (in any order numbers)?
This is an interesting question. I can picture two different scenarios. Which one is yours?
1) Do you mean that you want to point to a cell that contains 15-25-5-12-18 and it would say that this is a permutation of 5-12-15-18-25? Enter the five numbers in A1, and then use this formula: =TEXTJOIN("-",,SORT(TEXTSPLIT(A1,,"-")+0))
2) Or do you want to pass five numbers 5-12-15-18-25 and generate all 120 possible permutations of those five numbers? This one is definitely not built into Excel. It could be done with VBA in Excel, but let me know if this is what you really want.
hmm, why i'm only getting up to 24999 rows only? need help please
Make the workbook is saved with a name before you run the macro. The program tries to do an AutoSave at row 25000. If the workbook doesn’t already have a path and file name it could stop there.
Amazing video. Please how can I get the combination from 1-50 in 5 sets instead of 6?
Sub Choose5From50()
TC = 1 ' ThisColumn
TR = 1 ' ThisRow
Ctr = 1 ' Ctr = 1
MaxRows = Rows.Count
Application.ScreenUpdating = False
' Last possible is 46-47-48-49-50
For a = 1 To 46
For b = (a + 1) To 47
For c = (b + 1) To 48
For d = (c + 1) To 49
For e = (d + 1) To 50
Cells(TR, TC).Value = a & "-" & b & "-" & c & "-" & d & "-" & e
Ctr = Ctr + 1
If Ctr Mod 250000 = 0 Then
ThisWorkbook.Save
End If
TR = TR + 1
If TR = MaxRows Then
TR = 1
TC = TC + 1
End If
Next e, d, c, b, a
Application.StatusBar = False
End Sub
Mr. Excel, I tried to to this with a 5 numbers 1-69 without a powerball and something went haywire. I believe there should be 11,238,513 combinations. Can you explain how to alter the formula for just the 5 number without the 6th powerball number please & thank you.
I agree with your 11,238,513. Here is the code to generate it:
Sub Choose5From69()
TC = 1 ' ThisColumn
TR = 1 ' ThisRow
Ctr = 1 ' Ctr = 1
' 5 of 69 will generate 11,238,513 combinations
' according to =COMBIN(69,5)
MaxRows = Rows.Count
Application.ScreenUpdating = False
' Last possible is 65-66-67-68-69
For a = 1 To 65
For b = (a + 1) To 66
For c = (b + 1) To 67
For d = (c + 1) To 68
For e = (d + 1) To 69
Cells(TR, TC).Value = a & "-" & b & "-" & c & "-" & d & "-" & e
Ctr = Ctr + 1
If Ctr Mod 25000 = 0 Then
ThisWorkbook.Save
End If
TR = TR + 1
If TR = MaxRows Then
TR = 1
TC = TC + 1
End If
Next e, d, c, b, a
Application.StatusBar = False
End Sub
@@MrXL thank you
Hi Mr. Excel, thanks for the video. If you had a list of 24 names and you wanted to generate all possible 6 name combinations, how would you generate a list without repeating any combinations?
Hi Ryan
First - in any cell, enter =COMBIN(24,6) and press Enter. This will tell you there will be 134,596 combinations of 24 names.
To generate the names:
List the 24 names in cells A1 through A24.
Run this macro:
Sub List24NamesChosenBy6()
TR = 1 ' ThisRow
Application.ScreenUpdating = False
' Last possible is 19-0-21-22-23-24
For a = 1 To 19
For b = (a + 1) To 20
For c = (b + 1) To 21
For d = (c + 1) To 22
For e = (d + 1) To 23
For f = (e + 1) To 24
Cells(TR, 3).Value = Cells(a, 1).Value
Cells(TR, 4).Value = Cells(b, 1).Value
Cells(TR, 5).Value = Cells(c, 1).Value
Cells(TR, 6).Value = Cells(d, 1).Value
Cells(TR, 7).Value = Cells(e, 1).Value
Cells(TR, 8).Value = Cells(f, 1).Value
TR = TR + 1
Next f, e, d, c, b, a
Application.ScreenUpdating = True
End Sub
On my machine, it takes about 46 seconds to run and generates all combinations in C1:H134596.
maybe it will work for you; www.lottocombination.com
@@MrXL What would be the difference if I was to put the names through A4 to A27? I did not figure out where it's taken into consideration in your script. Thank you so much!
Sub List20NamesChosenBy6()
TR = 29 ' ThisRow
Application.ScreenUpdating = False
' Last possible is 15-16-17-18-19-20
For a = 4 To 18
For b = (a + 1) To 19
For c = (b + 1) To 20
For d = (c + 1) To 21
For e = (d + 1) To 22
For f = (e + 1) To 23
Cells(TR, 2).Value = Cells(a, 1).Value
Cells(TR, 3).Value = Cells(b, 1).Value
Cells(TR, 4).Value = Cells(c, 1).Value
Cells(TR, 5).Value = Cells(d, 1).Value
Cells(TR, 6).Value = Cells(e, 1).Value
Cells(TR, 7).Value = Cells(f, 1).Value
TR = TR + 1
Next f, e, d, c, b, a
Application.ScreenUpdating = True
End Sub
I think I got it. Does it make sense?
hey Mr Excel been trying this lottery combination but only one row is generated until 24999 only I waited for more than an hour but still nothing. how do I know its working or not because its been hours but still only one row
Here is my trick. The macro is set to save every 25000 rows. I open Windows Explorer and set the View to Details. In this view, you can see the last date and time the workbook is saved. Start the macro running. Watch for the file date and time to update.
Hello Mr Excel. I have tried this it is still not working 😭. Please help me how to do it please please. I have been trying do many times but nothing still. Any alternative way or something I can do ?
Can I ask if it's possible for something that I made up but can't get it to work properly
Can you take a look at it for me
@@shonnellYouCantHandlethetruth Sure. How can you get me the workbook? I went to your "About" page to find your e-mail, but you don't list it there. Go to my About page, scroll down, and you can find my e-mail. Send me the example.
How do I calculate the combination for lottery numbers 01 to 39 and in 5 set. Numbers can Not be repeated.
eg.- 01-13-24-29-38
Sub ListThemAll35()
TC = 1 ' ThisColumn
TR = 1 ' ThisRow
Ctr = 1 ' Ctr = 1
MaxRows = Rows.Count
Application.ScreenUpdating = False
' Last possible is 39-38-37-36-35
For a = 1 To 35
For b = (a + 1) To 36
For c = (b + 1) To 37
For d = (c + 1) To 38
For e = (d + 1) To 39
Cells(TR, TC).Value = a & "-" & b & "-" & c & "-" & d & "-" & e
Ctr = Ctr + 1
If Ctr Mod 25000 = 0 Then
ThisWorkbook.Save
End If
TR = TR + 1
If TR = MaxRows Then
TR = 1
TC = TC + 1
End If
Next e, d, c, b, a
Application.StatusBar = False
End Sub
Has anyone done this and won or want to partner up with me
What if I have 30 or less numbers what do you think the odds will be
How many numbers are drawn?
MR Excel can you please help with the formula to use in order to select winning combination if you can.
If I posted that formula here in public, then everyone would play the same number and no one would win anything. (Imagine splitting a $1 million jackpot 300 million ways). You’ve got to come up with the magic mojo yourself. I can just give you tools to help analyze.
MR Excel am still waiting for the tools pls
@@mirriamjulile9738 Here is the complete list of tools that I have published: czcams.com/users/MrExcelcomsearch?query=lottery
I need help please!!! How do i input for lottery 00-54???
Is there really an option for "00" to be drawn? How many balls are selected?
An easy solution in Power Query is possible, and it takes less time to calculate than this formidable VBA-solution (some 15 minutes).
(No offence, Mr.Excel :-)
The twist here? Well, I wish I could post it here, but CZcams won’t let me. :-(
(No, no clickbait: there’s nothing to click. Just a fact.)
Yes, this post is a test.
Now I have created my combination but I keep on choosing the wrong combination when ever I play
How can I use the same function for creating names instead using numbers
For example using a particular no of letters to auto generate possible names for example "a, c, d, g, h, y, o, p, q, m, n, g, l, v, x"
@@PublicGin This question would best be handled at the MrExcel board. It is free to register and post: www.mrexcel.com/board/
Will looking forward to here from you 👨🎓
Hello MrExcel, can this code be edited for numbers from 0 to 49
At 1:48, there are six lines of code ending in 39, 40, 41, 42, 43, and 44.
Edit those to end in 44, 45, 46, 47, 48, and 49 instead:
For a = 1 to 44
For b = (a+1) to 45
For c = (b+1) to 46
For d = (c+1) to 47
For e = (d+1) to 48
For f = (e+1) to 49
maybe it will work for you; www.lottocombination.com
Can I chart
Hi Mr. Excel! Can you please help me? I need a code for a lottery which requires 6 numbers out from 1 to 58. I know that it has 40 million+ combinations, and it will surely take many hours to run, but it will be worth it as a guide for me in winning this. Thank you!
If you study this code compared to the code in the video, can you see the simple changes you need to have the numbers end at 58?
Sub Choose6From58()
TC = 1 ' ThisColumn
TR = 1 ' ThisRow
Ctr = 1 ' Ctr = 1
MaxRows = Rows.Count
Application.ScreenUpdating = False
' Last possible is 58-57-56-55-54-53
For a = 1 To 53
For b = (a + 1) To 54
For c = (b + 1) To 55
For d = (c + 1) To 56
For e = (d + 1) To 57
For f = (e + 1) To 58
Cells(TR, TC).Value = a & "-" & b & "-" & c & "-" & d & "-" & e & "-" & f
Ctr = Ctr + 1
If Ctr Mod 1000000 = 0 Then
ThisWorkbook.Save
End If
TR = TR + 1
If TR = MaxRows Then
TR = 1
TC = TC + 3
End If
End If
Next f, e, d, c, b, a
Application.StatusBar = False
End Sub
@@MrXL thanks a lot! does this work on my excel 2016 version?
@@besthifisystem6511 the code runs in 2016. The only issue might be that you need 64-but Excel and likely 16 GB of RAM to store 40 million cells.
@@MrXL I've tried it on my laptop which has only 4 GB of ram and run it for about 30 minutes then I've got 25 million combinations but the pc was throttling, and I've stopped the process and decided to rerun it when I'm not busy in editing some projects.
@@MrXL Hi MrExcel! Great job with the excel btw. I want to know if the code above can be sequenced in normal counting order for those 6 numbers or they will be all mixed?
Where a can find the formula?
Ai mulher, ele não falou bo vídeo que iria ter na descrição
I changed the code and it doesn't work. I need it for 11/22. Then I will delete all that look like 1,2,3,4,5,6,7,8.. and 1,3,5,7,9, and 2,4,6,8 etc. Then deletion with the help of duplicates that have been extracted so far. That is idea.
Post your changed code in a reply here and I can troubleshoot it.
@@MrXL Unfortunately, I did not save the edited code. I'm not an expert in excel, so I would ask, if you have time, to set the code to list all possible combinations from 1-22, if a total of 11 numbers are drawn daily.
What would be the formula to do this for the daily 3 numbers?
Sub ListThemAll
TR = 2
for a = 0 to 9
for b = 0 to 9
for c = 0 to 9
Cells(TR, 1).Value = a & "-" & b & "-" & c
TR = TR + 1
Next c
Next b
Next a
End Sub
@@MrXL Thank you so much for getting back to me. One more question if I could but how could I input previous winning numbers and use that algorithm to find the next possible numbers
I just have the Excel skills,@@zhannadilisio7374 . If I had the winning algorithm, I sure wouldn't post it in a public video because then everyone would win and it would dilute the jackpot pool.
Many people have hired me over the last decades to write the Excel formulas to calculate algorithms that they invent. Those are all confidential and I can't share them.
@@MrXL I understand. Thank you anyways!
Hello, Mr. Excel. Thank you for the videos and the information. I like all your videos, and you have my subscription. After all the list of numbers, is it possible to remove, giving 6 digits, all the lines with 3 or more numbers in common? For example, I have all the possible combinations in excel; I want to insert 1-2-3-4-5-6, and now I want to eliminate all the lines that have 3 or more numbers in common. Thank you very much for your knowledge.
Let me make sure I have this correct.
You will select a set of six numbers. Maybe 13-14-36-45-57-67. You want to eliminate all of the possibilities that would have matched 3 or more of those six numbers?
What a great idea. But tough to do. Let me think about that one for a bit.
@@MrXL yes, thank you!
@@AndreaVacca87 I ran the macro this morning. It removed 179,494 of the 7,059,052 possible combinations, leaving 6,879,558 combinations left.
Two things:
1) Because it is difficult to parse the numbers out of a text value like "7-8-22-29-32-38", I chose to remove the numbers as I wrote the combinations to the sheet.
2) There are six lines in the code where you have to edit the numbers from the last drawing. They look like this:
LastDraw(1) = 2
LastDraw(2) = 4
LastDraw(3) = 17
LastDraw(4) = 21
LastDraw(5) = 24
LastDraw(6) = 43
In my case, the numbers to remove were 2-4-17-21-24-43. You can see how to change the code for your numbers to remove, right?
Here is the new code:
Sub ListThemAllUnlessThreeMatch()
TC = 1 ' ThisColumn
TR = 1 ' ThisRow
Ctr = 1 ' Ctr = 1
Dim LastDraw(1 To 6) As Integer
' Change these lines to show the numbers in the last drawing
' Logic below will eliminate any combinations that have three of these
LastDraw(1) = 2
LastDraw(2) = 4
LastDraw(3) = 17
LastDraw(4) = 21
LastDraw(5) = 24
LastDraw(6) = 43
MaxRows = Rows.Count
Application.ScreenUpdating = False
' Last possible is 39-40-41-42-43-44
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
MatchCtr = 0
' Count how many match the LastDraw
For j = 1 To 6
If a = LastDraw(j) Then MatchCtr = MatchCtr + 1
If b = LastDraw(j) Then MatchCtr = MatchCtr + 1
If c = LastDraw(j) Then MatchCtr = MatchCtr + 1
If d = LastDraw(j) Then MatchCtr = MatchCtr + 1
If e = LastDraw(j) Then MatchCtr = MatchCtr + 1
If f = LastDraw(j) Then MatchCtr = MatchCtr + 1
Next j
If MatchCtr < 3 Then
Cells(TR, TC).Value = a & "-" & b & "-" & c & "-" & d & "-" & e & "-" & f
Ctr = Ctr + 1
If Ctr Mod 2500000 = 0 Then
ThisWorkbook.Save
End If
TR = TR + 1
If TR = MaxRows Then
TR = 1
TC = TC + 1
End If
End If
Next f, e, d, c, b, a
Application.StatusBar = False
End Sub
Good luck!
@@MrXL Thank you so much, you're the best.
@@MrXL What if I want to remove multiple drawings?
HI MR Excel, thank you for the tutorial, it's great. I was wondering how I should modify the code if I only had 35 numbers and wanted it to list all the combinations of 5 numbers? Thank you in advance.
Are the numbers 1 to 35? Use this:
Sub ListThemAll35()
TC = 1 ' ThisColumn
TR = 1 ' ThisRow
Ctr = 1 ' Ctr = 1
MaxRows = Rows.Count
Application.ScreenUpdating = False
' Last possible is 31-32-33-34-35
For a = 1 To 31
For b = (a + 1) To 32
For c = (b + 1) To 33
For d = (c + 1) To 34
For e = (d + 1) To 35
Cells(TR, TC).Value = a & "-" & b & "-" & c & "-" & d & "-" & e
Ctr = Ctr + 1
If Ctr Mod 25000 = 0 Then
ThisWorkbook.Save
End If
TR = TR + 1
If TR = MaxRows Then
TR = 1
TC = TC + 1
End If
Next e, d, c, b, a
Application.StatusBar = False
End Sub
Or are you in a system where you need to select 5 numbers from 1 to 60 and you have 35 of those that seem hot at the moment? In that case, it would be easiest if you listed the 35 hot numbers in A1:A35 of Sheet2 and Sheet1 was blank for the combination of numbers. Let me know if this is your situation.
Hello everyone, I am looking for a way in Excel to generate five million possibilities from 1 to 49 (6 chiffres)
maybe it will work for you; www.lottocombination.com
Can i make every 7 number combinition upto 44
Be aware that there are 38 million combinations. This will likely require over 40 hours to run on a very fast PC.
Sub ListFrom44Choosing7()
TC = 1 ' ThisColumn
TR = 1 ' ThisRow
Ctr = 1 ' Ctr = 1
MaxRows = Rows.Count
Application.ScreenUpdating = False
' Last possible is 44-43-42-41-40-39-38
For a = 1 To 38
For b = (a + 1) To 39
For c = (b + 1) To 40
For d = (c + 1) To 41
For e = (d + 1) To 42
For f = (e + 1) To 43
For g = (f + 1) To 44
Cells(TR, TC).Value = a & "-" & b & "-" & c & "-" & d & "-" & e & "-" & f & "-" & g
Ctr = Ctr + 1
If Ctr Mod 500000 = 0 Then
ThisWorkbook.Save
End If
TR = TR + 1
If TR = MaxRows Then
TR = 1
TC = TC + 1
End If
Next g, f, e, d, c, b, a
Application.StatusBar = False
End Sub
Oh yea
Rather than list ALL combinations:
What is required is (a spreadsheet or program)
which returns the required set of numbers,
based on a position (in your example 1 to 7,059,052) and vice versa:
For example
Based on 6/44 (7,059,052)
Position 6,000,000 = 12 14 21 23 26 28
Set 20 25 30 35 40 44 = 6,911,737
And for the current MegaMillions (5/70 + 1/25) (302,575,349)
Position 6,000,000 = 01 07 14 23 34 + (25)
Set 20 25 30 35 40 + (05) = 245,618,830
etc.
In cells A1:A10, put 10 position numbers. For example, 6000000 in A1. 6911737 in A2. And then any other position numbers in A3 to A10.
Sub StoreInArray()
Ctr = 1
Dim Res(7059053)
For a = 1 To 39
Application.StatusBar = a & " on way 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
Res(Ctr) = a & "-" & b & "-" & c & "-" & d & "-" & e & "-" & f
Ctr = Ctr + 1
Next f
Next e
Next d
Next c
Next b
Next a
' For the numbers in A1:A10, fill in the results in B
For i = 1 To 10
Cells(i, 2).Value = Res(Cells(i, 1).Value)
Next i
Application.StatusBar = False
Application.ScreenUpdating = True
End Sub
Run the macro. It takes about 40 seconds to load the array and then report the results in B1:B10.
Another option would be to increment the column with 'a' so each column will have ALL the combinations with the same 'a' value by adding "TR = 1" and "TC = TC + 1" before the "Next a" statement and removing the other incrementation statements.
As a way to more "reliably" track progress, at the end of each For a...loop, you could set ScreenUpdating = True, TR = 1, TC = TC + 1, Range("A1").Offset(0, TC + 1).Select, ScreenUpdating = False. This would have the values in a column all populate at the same time and select the first cell of the next column so the screen always keeps the last column on the screen. This would only minorly slow down the execution.
Thanks for the good idea about moving each "a" value to a new column.
Toggling ScreenUpdating on then off is still no longer working for me in long macros. Once I hit the time limit for Windows to deem that Excel is "Not Responding", the ScreenUpdating trick is not working for me. Strangely, triggering a periodic Save keeps the status bar and ScreenUpdating alive.
How to get a lottey 6 digit number in between 1 to 29?
Using Excel Online or Microsoft 365, enter this in A1: =SORT(TAKE(SORTBY(SEQUENCE(29),RANDARRAY(29)),6))
Every time you press F9, you will get 6 new numbers.
@@MrXLhow can I get full 6 digit numbers?
If you have the newest version of Excel,@@jamijami7586 then the formula will return 6 numbers. I just tried it and it gave me 1, 2, 4, 16, 18, and 24.
I press F9 and I then get 4, 15, 23, 25, 27, 29.
This formula is using new functions that were introduced in November of 2022.
If your Excel is older than that, you can try Excel Online.
Steps:
1. Open a new tab in a browser on your computer (Use Edge or Chrome)
2. In the Address bar (not the search box), type Excel.New
3. Copy this formula: =SORT(TAKE(SORTBY(SEQUENCE(29),RANDARRAY(29)),6))
4. Paste in any cell in Excel
@@MrXL I can understand your formula. If I will put this formula after I will press F9. It's generate one sequence again I press the F9 it's again give another sequence. But I want, if I put any formula in excel, It will show whole 6 digit number in between 1 to 29.
I don't know, how many sequences in between 1 to 29. So pls I need while sequences. Help me.
Could I hire someone to make every 5 number combination for me from 1-43. So I can print it out and keep in a binder.
Sure - drop me a note to Pub@MrExcel.com.
maybe it will work for you; www.lottocombination.com
can you provide me all combination sir??
No I don’t sell the data. I only provide instructions on how you can generate the data.
you know this programming ok. But you did not offer people something to use profitable. you can get from excel an5 or 6 numbers from 49 numbers no problem.Have you got
a system economical for people with your knowledge to offer to the people?
Oh ..no
Can you give me the formula for fantasy 5 with the sequence of 1-36 with 376992 possibilities please.
Choosing 5 from 1 to 36:
Sub Choose5From36()
TC = 1 ' ThisColumn
TR = 1 ' ThisRow
Ctr = 1 ' Ctr = 1
MaxRows = Rows.Count
Application.ScreenUpdating = False
' Last possible is 32-33-34-35-36
For a = 1 To 32
For b = (a + 1) To 33
For c = (b + 1) To 34
For d = (c + 1) To 35
For e = (d + 1) To 36
Cells(TR, TC).Value = a & "-" & b & "-" & c & "-" & d & "-" & e
Ctr = Ctr + 1
If Ctr Mod 25000 = 0 Then
ThisWorkbook.Save
End If
TR = TR + 1
If TR = MaxRows Then
TR = 1
TC = TC + 1
End If
Next e, d, c, b, a
Application.StatusBar = False
End Sub
Thank you so much Mr excel