Lottery 1st & 2nd Digits Calculations

[ststern45]

Need help with creating 2 lists:
Example: 5/39 Lottery Game
List #1 - 1st digits/left digits
Pad all numbers with a zero in numbers less than 10 (01-02-03-04-05)
Create a list of all 1st or left digit sets
01-02-03-04-05 = 0,0,0,0,0
35-36-37-38-39 = 3,3,3,3,3
Count how many sets from the entire list of 575757 have the 1st digits 0-0-0-0-0 through 3-3-3-3-3
Remove any duplicate 1st digit sets from the list

List #2 - 2nd digits/right digits
Pad all numbers with a zero in numbers less than 10 (01-02-03-04-05)
Create a list of all 2nd or right d1,2,3,4,5
35-36-37-38-39 = 5,6,7,8,9
Count how many sets from the entire list of 575757 have the 2nd digits 1,2,3,4,5 through 5,6,7,8,9
Remove any duplicate 2nd digit sets from the list.

Thank you in advance.

 

[StephenCrump]

List # 1:

	A	B	C	D	E	F
1					Total	575,757
2
3	Combins					Number
4	0	0	0	0	0	126
5	0	0	0	0	1	1,260
6	0	0	0	0	2	1,260
7	0	0	0	0	3	1,260
8	0	0	0	1	1	3,780
9	0	0	0	1	2	8,400
10	0	0	0	1	3	8,400
11	0	0	0	2	2	3,780
12	0	0	0	2	3	8,400
13	0	0	0	3	3	3,780
14	0	0	1	1	1	4,320
15	0	0	1	1	2	16,200
16	0	0	1	1	3	16,200
17	0	0	1	2	2	16,200
18	0	0	1	2	3	36,000
19	0	0	1	3	3	16,200
20	0	0	2	2	2	4,320
21	0	0	2	2	3	16,200
22	0	0	2	3	3	16,200
23	0	0	3	3	3	4,320
24	0	1	1	1	1	1,890
25	0	1	1	1	2	10,800
26	0	1	1	1	3	10,800
27	0	1	1	2	2	18,225
28	0	1	1	2	3	40,500
29	0	1	1	3	3	18,225
30	0	1	2	2	2	10,800
31	0	1	2	2	3	40,500
32	0	1	2	3	3	40,500
33	0	1	3	3	3	10,800
34	0	2	2	2	2	1,890
35	0	2	2	2	3	10,800
36	0	2	2	3	3	18,225
37	0	2	3	3	3	10,800
38	0	3	3	3	3	1,890
39	1	1	1	1	1	252
40	1	1	1	1	2	2,100
41	1	1	1	1	3	2,100
42	1	1	1	2	2	5,400
43	1	1	1	2	3	12,000
44	1	1	1	3	3	5,400
45	1	1	2	2	2	5,400
46	1	1	2	2	3	20,250
47	1	1	2	3	3	20,250
48	1	1	3	3	3	5,400
49	1	2	2	2	2	2,100
50	1	2	2	2	3	12,000
51	1	2	2	3	3	20,250
52	1	2	3	3	3	12,000
53	1	3	3	3	3	2,100
54	2	2	2	2	2	252
55	2	2	2	2	3	2,100
56	2	2	2	3	3	5,400
57	2	2	3	3	3	5,400
58	2	3	3	3	3	2,100
59	3	3	3	3	3	252
List1

Cell Formulas
Range	Formula
F1	F1	=SUM(F4:F60)
F4:F59	F4	=PRODUCT(COMBIN({9,10,10,10},COUNTIF(A4:E4,{0,1,2,3})))
Press CTRL+SHIFT+ENTER to enter array formulas.

List #2 is derived similarly, but attached here as it's too big to post: Combins Lists.xlsx

 

[ststern45]

Excellent work Stephen.
Thank you for the Excel Combins Lists.xlsx
May I ask how you created the strings starting in A4:E4 down to A59 through E59 in List1?
Thank you for the work.
Very impressive!!

 

[ststern45]

My guess is that you created 2 lists of all possible strings for 1st and 2nd digts (left & right) for the 5/39 game then removed any of the strings where the total sets = 0?

For example,
5/39 game
List1
1st Digit Strings
0-0-0-0-0 (01-02-03-04-05)
3-3-3-3-3 (35-36-37-38-39)
Position 1 - Low # = 0, High # = 3
Position 2 - Low # = 0, High # = 3
Position 3 - Low # = 0, High # = 3
Position 4 - Low # = 0, High # = 3
Position 5 - Low # = 0, High # = 3

List2
2nd Digit Strings
01-02-03-04-05
35-36-37-38-39

Position 1 - Low # = 0, High # = 9
Position 2 - Low # = 0, High # = 9
Position 3 - Low # = 0, High # = 9
Position 4 - Low # = 0, High # = 9
Position 5 - Low # = 0, High # = 9

 

[ststern45]

So list1 (1st digit sets) would consist of a total of 1024 possible strings
list 2 (2nd digits sets) would consist of a total of 100,000 possible strings but would need to be reduced since the highest possible number for positon 4 = 38, and position 5 = 39
I could be wrong but wanted to give it a try.

 

[ststern45]

Question:
Could you explain the part of the formula below and the calculations
COMBIN({9,10,10,10}
Is the 9,10,10,10 based off of the 39 total numbers in the game?
Suppose the game was a 5/30 with 30 total numbers or 5/43 with 43 total numbers?
Thanks in advance

 

[shg]

>> Is the 9,10,10,10 based off of the 39 total numbers in the game?

1 to 9 is 9 numbers; 10 to 19, 20 to 29, and 30 to 39 are 10 numbers each.

>> Suppose the game was a 5/30 with 30 total numbers ...

1 to 9, 10 to 19, 20 to 29, 30: COMBIN(9, 10, 10, 1)

>> or 5/43 with 43 total numbers?

1 to 9, 10 to 19, 20 to 29, 30 to 39, 40 to 43: COMBIN(9, 10, 10, 10, 4)

 

[ststern45]

Thank you for the reply. The calculations you provided work great.
I understand how this works.
Very much appreciated.

 

[ststern45]

My next project would be create the list of all possible strings for the 1st Digits
This is the vba code below I used to test the calculations.
Is there a faster way yo accomplish this?
Below is the code for a 5/39 game
The issue arrises when creating a list from a larger game matrix such as a 5/69 oo 5/69 game
Once the calculations reach the last cell which is 1,048,576 I receive an error.
Is there code that can be added that will contine to the next column?

Big thanks to Jack at Lottoforums for the code.

1st Digit Code

Sub first_digits539()
Dim a, b, c, d, e As Integer
Dim a1, b1, c1, d1, e1 As Integer
Dim count As Long

Columns("A:A").Select

Selection.NumberFormat = "@"
Range("a1").Select

count = 0

For a = 1 To 35
a1 = Int(a / 10)
For b = a + 1 To 36
b1 = Int(b / 10)
For c = b + 1 To 37
c1 = Int(c / 10)
For d = c + 1 To 38
d1 = Int(d / 10)
For e = d + 1 To 39
e1 = Int(e / 10)

count = count + 1

ActiveCell.Offset(count, 0) = CStr(a1 & b1 & c1 & d1 & e1)

Next
Next
Next
Next
Next

Columns("A:A").Select
ActiveSheet.Range("$A$1:$A$575757").RemoveDuplicates Columns:=1, Header:= _
xlNo
Range("A1").Select
End Sub



Second Digit Code

Sub second_digits539()
Dim a, b, c, d, e As Integer
Dim a1, b1, c1, d1, e1 As Integer
Dim count As Long

Columns("A:A").Select

Selection.NumberFormat = "@"
Range("a1").Select

count = 0

For a = 1 To 35
a1 = a Mod 10
For b = a + 1 To 36
b1 = b Mod 10
For c = b + 1 To 37
c1 = c Mod 10
For d = c + 1 To 38
d1 = d Mod 10
For e = d + 1 To 39
e1 = e Mod 10

count = count + 1

ActiveCell.Offset(count, 0) = CStr(a1 & b1 & c1 & d1 & e1)

Next
Next
Next
Next
Next

Columns("A:A").Select
ActiveSheet.Range("$A$1:$A$575757").RemoveDuplicates Columns:=1, Header:= _
xlNo
Range("A1").Select
End Sub

 

[StephenCrump]

May I ask how you created the strings starting in A4:E4 down to A59 through E59 in List1?

VBA, based on pgc01's code here: Combination, Repeats (AAB,BBC) allowed, Repeats (AAC,ACA,CAA) only considered as One
which can be used to generate combinations/permutations with/without repeats.

For List #2, this generates a few impossible combinations, e.g.
0|0|0|0|1 (there are only three 0's available), and
2|2|2|2|2 (only four 2's available).

For these combinations, the COMBIN() formula will produce an error, so I just filtered the errors out of List #2.

Have a play with the workbook attached: Combination Lists.xlsm

	A	B	C	D	E	F	G	H	I	J	K
1	Lottery(n,p)	69	5
2	Method	First
3		0	1	2	3	4	5	6	7	8	9
4	No	9	10	10	10	10	10	10	0	0	0
5
6	CountDigits	7		Total:	11,238,513
7				Check:	11,238,513
8
9	Combins
10	0	0	0	0	0	126
11	0	0	0	0	1	1,260
12	0	0	0	0	2	1,260
13	0	0	0	0	3	1,260
14	0	0	0	0	4	1,260
15	0	0	0	0	5	1,260
16	0	0	0	0	6	1,260
17	0	0	0	1	1	3,780
18	0	0	0	1	2	8,400
19	0	0	0	1	3	8,400
20	0	0	0	1	4	8,400
21	0	0	0	1	5	8,400
22	0	0	0	1	6	8,400
Combins

Cell Formulas
Range	Formula
B4	B4	=IF(Method="First",MIN(9,n),INT(n/10))
C4:K4	C4	=IF(Method="First",MIN(10,n-SUM($B4:B4)),INT(n/10)+(MOD(n,10)>=C3))
B6	B6	=MATCH(2,1/(NoDigits>0))
E6	E6	=AGGREGATE(9,6,INDEX(MyResults,,p+1))
E7	E7	=COMBIN(n,p)
F10:F22	F10	=PRODUCT(COMBIN(NoDigits,COUNTIF(A10:E10,Digits)))
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
Digits	=Combins!$B$3:$K$3	F10:F22
Method	=Combins!$B$2	B4:K4
MyResults	=Combins!$A$10:$F$471	E6, F10
n	=Combins!$B$1	E7, B4:K4
NoDigits	=Combins!$B$4:$K$4	B6, F10:F22, C4:K4
p	=Combins!$C$1	E6:E7
ResultsHere	=Combins!$A$10	E6, F10

Cells with Data Validation
Cell	Allow	Criteria
B2	List	First,Last

Option Explicit
'Sheet Module
Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Application.Intersect(Target, Union(Range("n"), Range("p"), Range("Method"))) Is Nothing Then _
        Call CombPerm

End Sub

'Code Module

' PGC Set 2007
' Calculates and writes the Combinations / Permutations with/without repetition
' vElements - Array with the set elements (1 to n)
' p - number of elements in 1 combination/permutation
' bComb - True: Combinations, False: Permutations
' bRepet - True: with repetition, False: without repetition
' vResult - Array to hold 1 permutation/combination (1 to p)
' lRow - row number. the next combination/permutation is written in lRow+1
' vResultAll - Array to hold all the permutations/combinations (1 to Total, 1 to p)
' iElement - order of the element to process in case of combination
' iIndex - position of the next element in the combination/permutation
' Sub CombPerm() deals with the input / output
' Sub CombPermNP() generates the combinations / permutations
Sub CombPerm()

    Dim p As Long, i As Long, N As Long
    Dim vElements As Variant, vResult As Variant, vResultAll As Variant, lTotal As Long
    Dim lRow As Long, bComb As Boolean, bRepet As Boolean
    
    p = Range("p").Value
    N = Range("CountDigits").Value
    bComb = True
    bRepet = True
              
    ReDim vElements(1 To N)
    For i = 1 To N
        vElements(i) = i - 1
    Next i
    
    On Error Resume Next
    Range("MyResults").ClearContents
    On Error GoTo 0
    With Application.WorksheetFunction
        If bComb = True Then
                lTotal = .Combin(N + IIf(bRepet, p - 1, 0), p)
        Else
            If bRepet = False Then lTotal = .Permut(N, p) Else lTotal = N ^ p
        End If
    End With
    ReDim vResult(1 To p)
    ReDim vResultAll(1 To lTotal, 1 To p)
    
    ' Calculate the Combinations / Permutations
    Call CombPermNP(vElements, p, bComb, bRepet, vResult, lRow, vResultAll, 1, 1)
    With Range("ResultsHere").Resize(lTotal, p)
        .Value = vResultAll
        With .Columns(p + 1)
            .FormulaR1C1 = "=PRODUCT(COMBIN(NoDigits,COUNTIF(RC[-" & p & "]:RC[-1],Digits)))"
            .FormulaArray = .FormulaR1C1
        End With
        .Resize(, p + 1).Name = "MyResults"
    End With
    
End Sub
Sub CombPermNP(ByVal vElements As Variant, ByVal p As Integer, ByVal bComb As Boolean, ByVal bRepet As Boolean, _
                             ByVal vResult As Variant, ByRef lRow As Long, ByRef vResultAll As Variant, ByVal iElement As Integer, ByVal iIndex As Integer)
    Dim i As Long, j As Long, bSkip As Boolean
    
    For i = IIf(bComb, iElement, 1) To UBound(vElements)
        bSkip = False
        ' in case of permutation without repetition makes sure the element is not yet used
        If (Not bComb) And Not bRepet Then
            For j = 1 To p
                If vElements(i) = vResult(j) And Not IsEmpty(vResult(j)) Then
                    bSkip = True
                    Exit For
                End If
            Next
        End If
    
        If Not bSkip Then
            vResult(iIndex) = vElements(i)
            If iIndex = p Then
                lRow = lRow + 1
                For j = 1 To p
                    vResultAll(lRow, j) = vResult(j)
                Next j
            Else
                Call CombPermNP(vElements, p, bComb, bRepet, vResult, lRow, vResultAll, i + IIf(bComb And bRepet, 0, 1), iIndex + 1)
            End If
        End If
    Next i
End Sub