Hi,

I want to produce ALL the combinations of C(n,k) where "n" is 12 and
"k" is 4 in an array or memory so to speak because I do not need the
combinations written to the worksheet.
I then want to loop through each one and compare it to the 6 number
combinations in the range "B3:G20", if it has 2 or MORE numbers in
common add 1 to the total.
Once any combination in memory has matched 2 or MORE numbers in common
with ANY 6 number combination then add 1 to the total and move to the
next combination in memory. This is because I only want it to count 1
instance of the 2 or MORE numbers in common with the 6 number
combinations, NOT all instances.
I think maybe a Boolean variable needs to be used.

Thanks in Advance.
All the Best.
Paul

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

On Aug 2, 11:32 am, Paul Black wrote:
Hi,

I want to produce ALL the combinations of C(n,k) where "n" is 12 and
"k" is 4 in an array or memory so to speak because I do not need the
combinations written to the worksheet.
I then want to loop through each one and compare it to the 6 number
combinations in the range "B3:G20", if it has 2 or MORE numbers in
common add 1 to the total.
Once any combination in memory has matched 2 or MORE numbers in common
with ANY 6 number combination then add 1 to the total and move to the
next combination in memory. This is because I only want it to count 1
instance of the 2 or MORE numbers in common with the 6 number
combinations, NOT all instances.
I think maybe a Boolean variable needs to be used.

Thanks in Advance.
All the Best.
Paul

Hi everyone,

Has anybody got any ideas or suggestions please.

Thanks in Advance.
All the Best.
Paul

On Aug 3, 1:43 pm, Paul Black wrote:
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

On Aug 2, 11:32 am, Paul Black wrote:



Hi,

I want to produce ALL the combinations of C(n,k) where "n" is 12 and
"k" is 4 in an array or memory so to speak because I do not need the
combinations written to the worksheet.
I then want to loop through each one and compare it to the 6 number
combinations in the range "B3:G20", if it has 2 or MORE numbers in
common add 1 to the total.
Once any combination in memory has matched 2 or MORE numbers in common
with ANY 6 number combination then add 1 to the total and move to the
next combination in memory. This is because I only want it to count 1
instance of the 2 or MORE numbers in common with the 6 number
combinations, NOT all instances.
I think maybe a Boolean variable needs to be used.

Thanks in Advance.
All the Best.
Paul- Hide quoted text -

- Show quoted text -