Hi everyone,
I have put together the following to try and achieve my request.
I want to produce ALL the combinations of C(n,k) where "n" is 12 and
"k" is 3 in an array or memory so to speak because I do not need the
combinations written to the Excel worksheet.
I then want to loop through each one and compare it to the 6 number
combinations in the sheet named "Data" and in the range "B3:G?", if it
has 2 or MORE numbers in common add 1 to the 2 if 3 total, if it has
EXACTLY 3 numbers in common add 1 to the 3 if 3 total and put the
results in the sheet named "Statistics" in Cell "D10" for the 2 if 3
total and Cell "D14" for the 3 if 3 total.
Once any combination in memory has matched 2 or MORE numbers, or
EXACTLY 3 numbers in common with ANY 6 number combination then add 1
to the respective 2 if 3 or 3 if 3 total and move to the next
combination in memory. Some of the combinations in memory could
possibly match in MORE than one 6 number combination, but I only want
it to count 1 instance of the 2 or MORE or EXACTLY 3 numbers in common
with the 6 number combinations, NOT all instances.
I think maybe a Boolean variable needs to be used.
Anyway here is as far as I have got, assuming that I am on the right
track of course.
Option Explicit
Sub 2if3_3if3()
Dim n1 As Integer
Dim n2 As Integer
Dim n3 As Integer
Dim t(49, 49, 49) As Integer ' Numbers drawn from ( probably a better
way to do this )
Dim m(6) As Integer ' Total numbers drawn
Dim CombRange As Integer ' 6 numbers to be tested against
Application.ScreenUpdating = False
Sheets("Data").Select
' Reset array to 0
For n1 = 1 To 47
For n2 = n1 + 1 To 48
For n3 = n2 + 1 To 49
t(n1, n2, n3) = 0
Next n3
Next n2
Next n1
Range("B3").Select
' Calculate
Do While ActiveCell.Value <> ""
For CombRange = 1 To 6
m(CombRange) = ActiveCell.Offset(0, CombRange).Value
Next CombRange
' Calculate All the combinations of 3 from 6
t(m(1), m(2), m(3)) = t(m(1), m(2), m(3)) + 1
t(m(1), m(2), m(4)) = t(m(1), m(2), m(4)) + 1
t(m(1), m(2), m(5)) = t(m(1), m(2), m(5)) + 1
t(m(1), m(2), m(6)) = t(m(1), m(2), m(6)) + 1
t(m(1), m(3), m(4)) = t(m(1), m(3), m(4)) + 1
t(m(1), m(3), m(5)) = t(m(1), m(3), m(5)) + 1
t(m(1), m(3), m(6)) = t(m(1), m(3), m(6)) + 1
t(m(1), m(4), m(5)) = t(m(1), m(4), m(5)) + 1
t(m(1), m(4), m(6)) = t(m(1), m(4), m(6)) + 1
t(m(1), m(5), m(6)) = t(m(1), m(5), m(6)) + 1
t(m(2), m(3), m(4)) = t(m(2), m(3), m(4)) + 1
t(m(2), m(3), m(5)) = t(m(2), m(3), m(5)) + 1
t(m(2), m(3), m(6)) = t(m(2), m(3), m(6)) + 1
t(m(2), m(4), m(5)) = t(m(2), m(4), m(5)) + 1
t(m(2), m(4), m(6)) = t(m(2), m(4), m(6)) + 1
t(m(2), m(5), m(6)) = t(m(2), m(5), m(6)) + 1
t(m(3), m(4), m(5)) = t(m(3), m(4), m(5)) + 1
t(m(3), m(4), m(6)) = t(m(3), m(4), m(6)) + 1
t(m(3), m(5), m(6)) = t(m(3), m(5), m(6)) + 1
t(m(4), m(5), m(6)) = t(m(4), m(5), m(6)) + 1
' Compare to 6 number combinations and
' Count if >= 2 for the 2 if 5 total and
' Count if EXACTLY = 3 for the 3 if 3 total
ActiveCell.Offset(1, 0).Select
Loop
' Display results
Sheets("Statistics").Select
Range("D10").Value = ? ' 2 if 3 Total
Range("D14").Value = ? ' 3 if 3 Total
Application.ScreenUpdating = True
End Sub
I ideally want to be able to produce ALL of the following :-
2 if 2
2 if 3
2 if 4
2 if 5
2 if 6
3 if 3
3 if 4
3 if 5
3 if 6
4 if 4
4 if 5
4 if 6
5 if 5
5 if 6
6 if 6
Any help will be greatly appreciated.
Thanks in Advance.
All the Best.
Paul
