Generate all possible EuroMillions combinations in Excel

Hello,

what would you do with the 33,561,158,400 combinations?
Eric

--
By trying continuously, you eventually succeed.
So the more it fails, the more chances you have that it works. (the Shadoks)
In addition to the thank you (yes, yes, it’s done!!!), remember to mark as resolved. Thank you.

Hello,

Thank you for your response!
Shouldn't there be some in the 139 million?

Ideally, I would like to be able to add a formula per cell later on (for example =right(x,2) to isolate the highest star and then filter and delete the cells (combinations of numbers that do not meet my criteria).

Would this be possible in Excel? It seems like a titanic calculation in the file.
Thank you
Pierre

Hello,

at my place, 50*49*48*47*46*12*11 indeed gives the number mentioned above.
With 16,384 columns and 1,000,000 rows per sheet, you'll get 15 completely filled sheets.
Anything else planned during this millennium?
But even 139 million wouldn’t make much sense to list. Excel isn’t designed for that.
eric

--
By continually trying, we eventually succeed.
So the more it fails, the more chances we have for it to work. (the Shadoks)
Besides saying thank you (yes, it can be done!!!), remember to mark it as resolved. Thank you.

Hello,

Just a quick note, there are indeed 139,838,160 possible combinations to find the right numbers, see here
https://tirage-gagnant.com/probabilites-euromillions-chances-de-gagner/

To find this number in Excel, use

=COMBIN(50;5)*COMBIN(12;2)

Best regards

Hello,

As everyone has mentioned, filling a large number of columns with all possible combinations will hardly be usable afterwards.

However, listing all the 5-digit combinations (which amounts to 2,118,760 combinations) and separately listing the combinations of the star numbers (which amounts to 66 combinations) may be easier to manage later; it remains to be seen what you want to do with them.

I propose a macro that first finds the 66 combinations of star numbers, then finds the 2,118,760 combinations for the 5-digit draw.
- The 66 combinations of star numbers in column A
- The 2,118,760 combinations of the 5-digit draws in a table from B2 to AJ60537.
All completed in less than 18 seconds on my PC.

The code

Sub Trouver_combinaisons() Dim Lig As Long, Col As Long, i As Double, q As Double Dim Val_1 As Long, Val_2 As Long, Val_3 As Long, Val_4 As Long, Val_5 As Long Dim Combi Dim Dep As Date Cells.ClearContents Application.ScreenUpdating = False Deb = Timer 'searching for combinations with 2 star values Trouver_combinaisons_2_Etoiles 'searching for combinations with 5 values Combi = ",1,2,3,4,5" Lig = 1 Col = 2 Dep = 1 ReDim Result(60536) As String q = 1 For i = 1 To 2118760 'Maximum number of 5-number draw combinations out of 50 If q = 1 Then Deb_Combi = Combi Combi = Split(Combi, ",") Val_1 = Combi(1) * 1 Val_2 = Combi(2) * 1 Val_3 = Combi(3) * 1 Val_4 = Combi(4) * 1 Val_5 = Combi(5) * 1 If Val_5 < 50 Then Val_5 = Val_5 + 1 Else If Val_4 < 49 Then Val_4 = Val_4 + 1 Val_5 = Val_4 + 1 Else If Val_3 < 48 Then Val_3 = Val_3 + 1 Val_4 = Val_3 + 1 Val_5 = Val_4 + 1 Else If Val_2 < 47 Then Val_2 = Val_2 + 1 Val_3 = Val_2 + 1 Val_4 = Val_3 + 1 Val_5 = Val_4 + 1 Else If Val_1 < 46 Then Val_1 = Val_1 + 1 Val_2 = Val_1 + 1 Val_3 = Val_2 + 1 Val_4 = Val_3 + 1 Val_5 = Val_4 + 1 End If End If End If End If End If Result(q) = Val_1 & "," & Val_2 & "," & Val_3 & "," & Val_4 & "," & Val_5 Combi = "," & Result(q) If i Mod 60536 = 0 Then Range(Cells(2, Col), Cells(60537, Col)) = Application.WorksheetFunction.Transpose(Result) Col = Col + 1 Erase Result ReDim Result(q) As String End If If q = 60536 Then Cells(2, Col - 1) = Right(Deb_Combi, Len(Deb_Combi) - 1) q = 1 Else q = q + 1 End If Next i Range("A1").Value = "Combinations of the draw numbers ""Stars""" Range("F1").Value = "Combinations of the draw of the 5 numbers" MsgBox "Search completed, execution time:" & Timer - Deb & "Sec" End Sub Sub Trouver_combinaisons_2_Etoiles() Dim Lig As Long, Col As Long, i As Long Dim Val_1 As Long, Val_2 As Long Dim Combi Application.ScreenUpdating = False 'searching for combinations with 5 values Combi = "_1_2" Lig = 2 Col = 1 Cells(1, Col) = Combi ReDim Result(66) As String For i = 1 To 65 'Maximum number of combinations of 2-number draws out of 12 -1 Combi = Split(Combi, "_") Val_1 = Combi(1) * 1 Val_2 = Combi(2) * 1 If Val_2 < 12 Then Val_2 = Val_2 + 1 Else If Val_1 < 11 Then Val_1 = Val_1 + 1 Val_2 = Val_1 + 1 End If End If Lig = Lig + 1 Result(i) = Val_1 & "_" & Val_2 Combi = "_" & Result(i) Next i Range(Cells(2, Col), Cells(667, Col)) = Application.WorksheetFunction.Transpose(Result) Cells(2, Col) = "1_2" End Sub

The file (click the button to generate the combinations)
https://mon-partage.fr/f/dj3OliwK/

To obtain the list of all possible combinations (5 numbers draw + star draw) which equals 139,838,160 combinations, it is necessary for each 5-digit number to add each of the 66 values of the star numbers. (If that is really what you want to do)

Best regards