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!
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!
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.
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?
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!
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!!
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?
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.
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
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.
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 😊
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
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'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?
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?
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?
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 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
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. ?????
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.
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 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
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
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 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!
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.
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?
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.
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/
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.
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
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.
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.
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
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 Application.ScreenUpdating = True End Sub I think I got it. Does it make sense?
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
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
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
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.
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
@@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.
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.
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
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 ?
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?
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.
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.
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
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.
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.
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!
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.
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
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?
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.
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 RU-vid won’t let me. :-( (No, no clickbait: there’s nothing to click. Just a fact.)
