Can Someone Please Help with this Looping & Match Macro
 

Hi Everyone,

I have Adapted some Code Originally Written by Tom Ogilvy for Something
Else ( Thanks Very Much Tom ), which May be of Help for this Request.

Option Explicit

Sub Covered()
Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim D As Integer
Dim E As Integer
Dim F As Integer
Dim DrawnFrom As Integer
Dim icnt As Integer
Dim lngCount(0 To 6) As Long
Dim lngSum As Long
Dim nCount As Long
Dim s As Integer
Dim varray As Variant
varray = Array(1, 2, 3, 4, 5, 6)

Application.ScreenUpdating = False
DrawnFrom = ActiveSheet.Range("A1")

For A = 1 To DrawnFrom - 5
For B = A + 1 To DrawnFrom - 4
For C = B + 1 To DrawnFrom - 3
For D = C + 1 To DrawnFrom - 2
For E = D + 1 To DrawnFrom - 1
For F = E + 1 To DrawnFrom

nCount = nCount + 1

If True Then
icnt = 0
For s = 0 To 5
If A = varray(s) Then icnt = icnt + 1
If B = varray(s) Then icnt = icnt + 1
If C = varray(s) Then icnt = icnt + 1
If D = varray(s) Then icnt = icnt + 1
If E = varray(s) Then icnt = icnt + 1
If F = varray(s) Then icnt = icnt + 1
Next
lngCount(icnt) = lngCount(icnt) + 1
End If

Next
Next
Next
Next
Next
Next

lngSum = 0

Range("N2").Select

For s = 0 To 6
If s = 3 Then lngSum = lngSum + lngCount(s)
ActiveCell.Value = s & " Matches = " & Format(lngCount(s), "#,###")
ActiveCell.Offset(1, 0).Select
Next

Application.ScreenUpdating = True
End Sub

I will Try and Explain this as Clear as Possible.
I have a 24 Number Abbreviated Wheel ( in Cells "G13:L27" ) of 15
Combinations with 6 Numbers in EACH Combination ( Other Wheels to Check
could have Less Or More Combinations than Currently so the Code will
Need to Recognise when it is the Last Combination to Check ).
Therefore, for this Example, there are 134,596 Combinations [ Excel
Formula COMBIN(24,6) ] to Check Against the 15 Combinations in the
Abbreviated Wheel in Total.
The Basis of what I am Trying to Achieve is to take the Wheel, Generate
ALL 134,596 Combinations for the Total Selected Numbers in the Wheel (
24 in this Example ) and Match ( Compare ) EACH Combination with the
Lines in the Wheel. Then Collate ALL the Results as Per the Categories
Below.
So the Total Combinations "Covered" for the Matched Category 2 if 3 for
Example, Means the Total Combinations "Covered" in the Abbreviated
Wheel ( for ALL 15 Combinations ) that have 2 Matched Numbers if I have
3 Matched Numbers of the 6 Numbers Drawn Within my Selection.
So the Results ( I Don't Know if the "Covered" Results Below are
Accurate ) for EACH Category would Look something like this :-

Matched Tested Covered
2 if 2 276 209
2 if 3 2,024 2,008
2 if 4 10,626 10,626
2 if 5 42,504 42,504
2 if 6 134,596 134,596
3 if 3 2,024 300
3 if 4 10,626 5,289
3 if 5 42,504 35,720
3 if 6 134,596 131,922
4 if 4 10,626 225
4 if 5 42,504 4,140
4 if 6 134,596 35,304
5 if 5 42,504 90
5 if 6 134,596 1,635

I can see the Logic of what Needs to be Done, But Unfortunately I have
NO Idea how to Continue and Achieve the Required Results.

Any Help will be Greatly Appreciated.
Many Thanks in Advance.
All the Best.
Paul

Can Someone Please Help with this Looping & Match Macro
 

Hi Everyone,

Is it Feasible that the Code I Posted Above could be Adapted to Produce
the Required Results, Or is its Structure and Logic Totally Different.
ANY Suggestions on how to Approach this, Or at Least a View on How to
Start will be Greatly Appreciated.

Many Thanks in Advance.
All the Best.
Paul

Paul Black wrote:
Hi Everyone,

I have Adapted some Code Originally Written by Tom Ogilvy for Something
Else ( Thanks Very Much Tom ), which May be of Help for this Request.

Option Explicit

Sub Covered()
Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim D As Integer
Dim E As Integer
Dim F As Integer
Dim DrawnFrom As Integer
Dim icnt As Integer
Dim lngCount(0 To 6) As Long
Dim lngSum As Long
Dim nCount As Long
Dim s As Integer
Dim varray As Variant
varray = Array(1, 2, 3, 4, 5, 6)

Application.ScreenUpdating = False
DrawnFrom = ActiveSheet.Range("A1")

For A = 1 To DrawnFrom - 5
For B = A + 1 To DrawnFrom - 4
For C = B + 1 To DrawnFrom - 3
For D = C + 1 To DrawnFrom - 2
For E = D + 1 To DrawnFrom - 1
For F = E + 1 To DrawnFrom

nCount = nCount + 1

If True Then
icnt = 0
For s = 0 To 5
If A = varray(s) Then icnt = icnt + 1
If B = varray(s) Then icnt = icnt + 1
If C = varray(s) Then icnt = icnt + 1
If D = varray(s) Then icnt = icnt + 1
If E = varray(s) Then icnt = icnt + 1
If F = varray(s) Then icnt = icnt + 1
Next
lngCount(icnt) = lngCount(icnt) + 1
End If

Next
Next
Next
Next
Next
Next

lngSum = 0

Range("N2").Select

For s = 0 To 6
If s = 3 Then lngSum = lngSum + lngCount(s)
ActiveCell.Value = s & " Matches = " & Format(lngCount(s), "#,###")
ActiveCell.Offset(1, 0).Select
Next

Application.ScreenUpdating = True
End Sub

I will Try and Explain this as Clear as Possible.
I have a 24 Number Abbreviated Wheel ( in Cells "G13:L27" ) of 15
Combinations with 6 Numbers in EACH Combination ( Other Wheels to Check
could have Less Or More Combinations than Currently so the Code will
Need to Recognise when it is the Last Combination to Check ).
Therefore, for this Example, there are 134,596 Combinations [ Excel
Formula COMBIN(24,6) ] to Check Against the 15 Combinations in the
Abbreviated Wheel in Total.
The Basis of what I am Trying to Achieve is to take the Wheel, Generate
ALL 134,596 Combinations for the Total Selected Numbers in the Wheel (
24 in this Example ) and Match ( Compare ) EACH Combination with the
Lines in the Wheel. Then Collate ALL the Results as Per the Categories
Below.
So the Total Combinations "Covered" for the Matched Category 2 if 3 for
Example, Means the Total Combinations "Covered" in the Abbreviated
Wheel ( for ALL 15 Combinations ) that have 2 Matched Numbers if I have
3 Matched Numbers of the 6 Numbers Drawn Within my Selection.
So the Results ( I Don't Know if the "Covered" Results Below are
Accurate ) for EACH Category would Look something like this :-

Matched Tested Covered
2 if 2 276 209
2 if 3 2,024 2,008
2 if 4 10,626 10,626
2 if 5 42,504 42,504
2 if 6 134,596 134,596
3 if 3 2,024 300
3 if 4 10,626 5,289
3 if 5 42,504 35,720
3 if 6 134,596 131,922
4 if 4 10,626 225
4 if 5 42,504 4,140
4 if 6 134,596 35,304
5 if 5 42,504 90
5 if 6 134,596 1,635

I can see the Logic of what Needs to be Done, But Unfortunately I have
NO Idea how to Continue and Achieve the Required Results.

Any Help will be Greatly Appreciated.
Many Thanks in Advance.
All the Best.
Paul