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
But there's no point because it's unusable.
eric
I just have an idea in my mind and I would like to try to analyze a bit more; that doesn’t mean I’m on the path to the perfect analysis that will give me the winning numbers. As they say, chance has no memory. However, when I have an idea in mind, I just like to try it out to the end :)
No worries, I will find another way. Thanks anyway.
Pierre
Can you see yourself reading 16,384 columns by 1,000,000 rows, and that across multiple sheets?
Because there's no way to filter without also building the necessary tools...