Hi Everyone,

I will Try and Explain this Clearly for a 2 if 3 Scenario.
If we have the Following 6 Number Combinations in Cells G13:L17 ...

1 2 3 4 5 6
1 2 3 5 6 8
1 2 3 5 7 8
1 2 3 6 7 8
1 2 4 7 8 9

.... and Wanted to Calculate How Many 2 Number Combinations are Covered
if we Match 3 Numbers.
If we took the Set of Numbers 1 2 3 for Example, the Numbers 1 2 Appear
in 5 Combinations, but we Only Want it Counted Once. So in Theory, the
Macro would Only Need to Check the First Combination to Satisfy if the
Numbers 1 2 are Covered. The Next 2 if 3 Scenario will be 1 3, in this
Case there are 4 Combinations with the Numbers 1 3 in the Set 1 2 3,
and Again, the Macro would Only Need to Check the First Combination to
Satisfy if the Numbers 1 3 are Covered. Obviously the Numbers 1 4, 1 5,
1 6, 1 7, 1 8, 1 9, 2 4, 2 5 etc are Not included in the Set 1 2 3, but
are Included in Other Sets of 3 Numbers. For Example, 1 4 will be
Included in the Set 1 3 4. If Any of the 3 Numbers Sets doesn't Include
Any of the 2 Number Combinations that can be Made with Numbers 1 to 9
then that 2 Number Combination is Not Covered and therfore Not Included
in the 2 if 3 Total.
This will Continue for All the Sets of 3 Numbers that can be Made from
the Combinations, taken 1 Combination at a Time. Basically, I want a
Total of the 2 Number Combinations Covered if we Use Sets of 3 Numbers.

Any Help will be Greatly Appreciated.

All the Best.
Paul

Hi Tom,
The Code I have is as Follows.
Is there Another Way to Write the Following Code ( without Using the
Scripting Dictonary ) that Doesn't Use the Scripting Dictionary Please
so I can Better Understand :-

Code:
Sub test_5()
Dim a, dic As Object
Set dic = CreateObject("Scripting.Dictionary")
a = Range("g13").CurrentRegion.Value
For i = 1 To UBound(a, 1)
For ii = 1 To 2
For iii = ii + 1 To 3
For iv = iii + 1 To 4
For v = iv + 1 To 5
For vi = v + 1 To 6
z = a(i, ii) & "," & a(i, iii) & a(i, iv) &
a(i, v) & a(i, vi)
If Not dic.exists(z) Then
dic.Add z, Nothing
n = n + 1
End If
Next vi, v, iv, iii, ii, i
Set dic = Nothing
Range("O16") = n
End Sub

Thanks in Advance.
All the Best.
Paul

Tom Ogilvy wrote:
how to write a vba procedure that counts the number of unique 2 of 3
combinations in the data set in G13:L17

--
Regards,
Tom Ogilvy


"JLGWhiz" wrote:

What is the question?

"Paul Black" wrote:

Hi Everyone,

I will Try and Explain this Clearly for a 2 if 3 Scenario.
If we have the Following 6 Number Combinations in Cells G13:L17 ...

1 2 3 4 5 6
1 2 3 5 6 8
1 2 3 5 7 8
1 2 3 6 7 8
1 2 4 7 8 9

.... and Wanted to Calculate How Many 2 Number Combinations are Covered
if we Match 3 Numbers.
If we took the Set of Numbers 1 2 3 for Example, the Numbers 1 2 Appear
in 5 Combinations, but we Only Want it Counted Once. So in Theory, the
Macro would Only Need to Check the First Combination to Satisfy if the
Numbers 1 2 are Covered. The Next 2 if 3 Scenario will be 1 3, in this
Case there are 4 Combinations with the Numbers 1 3 in the Set 1 2 3,
and Again, the Macro would Only Need to Check the First Combination to
Satisfy if the Numbers 1 3 are Covered. Obviously the Numbers 1 4, 1 5,
1 6, 1 7, 1 8, 1 9, 2 4, 2 5 etc are Not included in the Set 1 2 3, but
are Included in Other Sets of 3 Numbers. For Example, 1 4 will be
Included in the Set 1 3 4. If Any of the 3 Numbers Sets doesn't Include
Any of the 2 Number Combinations that can be Made with Numbers 1 to 9
then that 2 Number Combination is Not Covered and therfore Not Included
in the 2 if 3 Total.
This will Continue for All the Sets of 3 Numbers that can be Made from
the Combinations, taken 1 Combination at a Time. Basically, I want a
Total of the 2 Number Combinations Covered if we Use Sets of 3 Numbers.

Any Help will be Greatly Appreciated.

All the Best.
Paul

The dictionary just accumulates the unique instances. It sounds like a good
way to me.

--
Regards
Tom Ogilvy


"Paul Black" wrote in message
oups.com...
Hi Tom,
The Code I have is as Follows.
Is there Another Way to Write the Following Code ( without Using the
Scripting Dictonary ) that Doesn't Use the Scripting Dictionary Please
so I can Better Understand :-

Code:
Sub test_5()
Dim a, dic As Object
Set dic = CreateObject("Scripting.Dictionary")
a = Range("g13").CurrentRegion.Value
For i = 1 To UBound(a, 1)
For ii = 1 To 2
For iii = ii + 1 To 3
For iv = iii + 1 To 4
For v = iv + 1 To 5
For vi = v + 1 To 6
z = a(i, ii) & "," & a(i, iii) & a(i, iv) &
a(i, v) & a(i, vi)
If Not dic.exists(z) Then
dic.Add z, Nothing
n = n + 1
End If
Next vi, v, iv, iii, ii, i
Set dic = Nothing
Range("O16") = n
End Sub

Thanks in Advance.
All the Best.
Paul

Tom Ogilvy wrote:
how to write a vba procedure that counts the number of unique 2 of 3
combinations in the data set in G13:L17

--
Regards,
Tom Ogilvy


"JLGWhiz" wrote:

What is the question?

"Paul Black" wrote:

Hi Everyone,

I will Try and Explain this Clearly for a 2 if 3 Scenario.
If we have the Following 6 Number Combinations in Cells G13:L17 ...

1 2 3 4 5 6
1 2 3 5 6 8
1 2 3 5 7 8
1 2 3 6 7 8
1 2 4 7 8 9

.... and Wanted to Calculate How Many 2 Number Combinations are
Covered
if we Match 3 Numbers.
If we took the Set of Numbers 1 2 3 for Example, the Numbers 1 2
Appear
in 5 Combinations, but we Only Want it Counted Once. So in Theory,
the
Macro would Only Need to Check the First Combination to Satisfy if
the
Numbers 1 2 are Covered. The Next 2 if 3 Scenario will be 1 3, in
this
Case there are 4 Combinations with the Numbers 1 3 in the Set 1 2 3,
and Again, the Macro would Only Need to Check the First Combination
to
Satisfy if the Numbers 1 3 are Covered. Obviously the Numbers 1 4, 1
5,
1 6, 1 7, 1 8, 1 9, 2 4, 2 5 etc are Not included in the Set 1 2 3,
but
are Included in Other Sets of 3 Numbers. For Example, 1 4 will be
Included in the Set 1 3 4. If Any of the 3 Numbers Sets doesn't
Include
Any of the 2 Number Combinations that can be Made with Numbers 1 to 9
then that 2 Number Combination is Not Covered and therfore Not
Included
in the 2 if 3 Total.
This will Continue for All the Sets of 3 Numbers that can be Made
from
the Combinations, taken 1 Combination at a Time. Basically, I want a
Total of the 2 Number Combinations Covered if we Use Sets of 3
Numbers.

Any Help will be Greatly Appreciated.

All the Best.
Paul

Thanks for the Reply Tom,

Would you be Able to Assist me in Adapting the Code I Posted to
Calculate the 2 if 3 Scenario Please. I Assume ( Probably Wrongly ) that
it will Mean Adding a If...Then Statement or Such like to Count and Hold
the Total.
Thanks Very Much in Advance.

All the Best.
Paul


*** Sent via Developersdex http://www.developersdex.com ***

I am not sure I understand the rules.

From your description, it sounds like you need to loop throught the data,
row at a time.

For each row, generate every combination of two values from 6 and put them
in the dictionary
when done, count the items in the dictionary.

every combination of two has to be an subset of a combination of 3.

Tell me where I don't understand the rules.

--
Regards,
Tom Ogilvy

"Paul Black" wrote in message
...
Thanks for the Reply Tom,

Would you be Able to Assist me in Adapting the Code I Posted to
Calculate the 2 if 3 Scenario Please. I Assume ( Probably Wrongly ) that
it will Mean Adding a If...Then Statement or Such like to Count and Hold
the Total.
Thanks Very Much in Advance.

All the Best.
Paul


*** Sent via Developersdex http://www.developersdex.com ***

Thanks for the Reply Tom,

I think your Explanation is Correct. I am just having Trouble Trying to
Explain Exactly what I want to Achieve.
There are 20 Triples for Each 6 Number Combination.
So for the First Triple in the First Combination there are Doubles of 1
2, 1 4 & 2 4. Now 1 2 is Also Included in the First Triple in the
Second Combination 1 2 5. The Double 1 2 Only Needs to be Counted the
Once though. The Code Needs to Count Unique Doubles in Each Triple in
All the Combinations.

Thanks Very Much in Advance.
All the Best.
Paul

Tom Ogilvy wrote:
I am not sure I understand the rules.

From your description, it sounds like you need to loop throught the data,
row at a time.

For each row, generate every combination of two values from 6 and put them
in the dictionary
when done, count the items in the dictionary.

every combination of two has to be an subset of a combination of 3.

Tell me where I don't understand the rules.

--
Regards,
Tom Ogilvy

"Paul Black" wrote in message
...
Thanks for the Reply Tom,

Would you be Able to Assist me in Adapting the Code I Posted to
Calculate the 2 if 3 Scenario Please. I Assume ( Probably Wrongly ) that
it will Mean Adding a If...Then Statement or Such like to Count and Hold
the Total.
Thanks Very Much in Advance.

All the Best.
Paul


*** Sent via Developersdex http://www.developersdex.com ***

Sub CountDoubles()
Dim s As String, arr As Variant
Dim i As Long, j As Long
Dim n As Long
Set dic = CreateObject("Scripting.Dictionary")
For Each cell In Range("G13:G17")
arr = cell.Resize(1, 6).Value
For i = 1 To 5
For j = i + 1 To 6
s = arr(1, i) & "," & arr(1, j)
If Not dic.Exists(s) Then
dic.Add s, Nothing
n = n + 1
End If
Next
Next
Next
MsgBox n
End Sub


--
Regards,
Tom Ogilvy

"Paul Black" wrote in message
ps.com...
Thanks for the Reply Tom,

I think your Explanation is Correct. I am just having Trouble Trying to
Explain Exactly what I want to Achieve.
There are 20 Triples for Each 6 Number Combination.
So for the First Triple in the First Combination there are Doubles of 1
2, 1 4 & 2 4. Now 1 2 is Also Included in the First Triple in the
Second Combination 1 2 5. The Double 1 2 Only Needs to be Counted the
Once though. The Code Needs to Count Unique Doubles in Each Triple in
All the Combinations.

Thanks Very Much in Advance.
All the Best.
Paul

Tom Ogilvy wrote:
I am not sure I understand the rules.

From your description, it sounds like you need to loop throught the data,
row at a time.

For each row, generate every combination of two values from 6 and put
them
in the dictionary
when done, count the items in the dictionary.

every combination of two has to be an subset of a combination of 3.

Tell me where I don't understand the rules.

--
Regards,
Tom Ogilvy

"Paul Black" wrote in message
...
Thanks for the Reply Tom,

Would you be Able to Assist me in Adapting the Code I Posted to
Calculate the 2 if 3 Scenario Please. I Assume ( Probably Wrongly )
that
it will Mean Adding a If...Then Statement or Such like to Count and
Hold
the Total.
Thanks Very Much in Advance.

All the Best.
Paul


*** Sent via Developersdex http://www.developersdex.com ***

Hi Tom,

Thanks for the Response.
Unfortunately the Code did Not do as I Expected. This is Due to MY Bad
Explanation of what I would like.
I think the Following Explanation is Better Suited and will Hopefully
bring it into Focus.

Lets Assume that we have 6 Numbers in Each Combination …
01 02 03 08 09 10
etc
etc
… for Example.

If we just Concentrate on the 2 if 3 Scenario, the Code Needs to do the
Following :-
Because the Maximum Number in the Combination in this Case is 10, we
Need to Calculate and Store the 120 Combinations ( =COMBIN(10,3) = 120 )
Produced for 3 Numbers from 10 Numbers. I Assume this will be Stored in
the Scripting Dictionary.
Then Loop through Each Triple Stored in the Scripting Dictionary and
Count How Many of those 120 Triples have Exactly 2 Numbers in Our
Combination of 01 02 03 08 09 10. The Answer in this Case for
Combination 01 02 03 08 09 10 should Equal 80.
The Macro Basically Needs to Loop through Every 3 Number Combination in
the Scripting Dictionary and Compare it to Each Individual Combination
in Our Wheel. If Exactly 2 Numbers in Any of Our Wheel Combinations
Match Exactly 2 Numbers in Any of the 3 Number Combinations in the
Scripting Dictionary then that is Counted and the Macro Moves on to the
Next Combination in the Scripting Dictionary etc.
Or the Macro could Loop through the Wheel and Compare it to the
Scripting Dictionary.

Thanks Very Much in Advance.
All the Best.
Paul

*** Sent via Developersdex http://www.developersdex.com ***

Hi Tom,

I Hope your Year is going Well.
Have you had Time to have a Look through my above Post yet?.

Thanks in Advance.
All the Best.
Paul

Paul Black wrote:
Hi Tom,

Thanks for the Response.
Unfortunately the Code did Not do as I Expected. This is Due to MY Bad
Explanation of what I would like.
I think the Following Explanation is Better Suited and will Hopefully
bring it into Focus.

Lets Assume that we have 6 Numbers in Each Combination ...
01 02 03 08 09 10
etc
etc
... for Example.

If we just Concentrate on the 2 if 3 Scenario, the Code Needs to do the
Following :-
Because the Maximum Number in the Combination in this Case is 10, we
Need to Calculate and Store the 120 Combinations ( =COMBIN(10,3) = 120 )
Produced for 3 Numbers from 10 Numbers. I Assume this will be Stored in
the Scripting Dictionary.
Then Loop through Each Triple Stored in the Scripting Dictionary and
Count How Many of those 120 Triples have Exactly 2 Numbers in Our
Combination of 01 02 03 08 09 10. The Answer in this Case for
Combination 01 02 03 08 09 10 should Equal 80.
The Macro Basically Needs to Loop through Every 3 Number Combination in
the Scripting Dictionary and Compare it to Each Individual Combination
in Our Wheel. If Exactly 2 Numbers in Any of Our Wheel Combinations
Match Exactly 2 Numbers in Any of the 3 Number Combinations in the
Scripting Dictionary then that is Counted and the Macro Moves on to the
Next Combination in the Scripting Dictionary etc.
Or the Macro could Loop through the Wheel and Compare it to the
Scripting Dictionary.

Thanks Very Much in Advance.
All the Best.
Paul

*** Sent via Developersdex http://www.developersdex.com ***

Sub CountDoubles()
Dim s As String, arr As Variant
Dim i As Long, j As Long
Dim n As Long
Dim v(1 To 120, 1 To 3) As Long
Dim v1(1 To 120) As Long
l = 0
For i = 1 To 8
For j = i + 1 To 9
For k = j + 1 To 10
l = l + 1
v(l, 1) = i
v(l, 2) = j
v(l, 3) = k
Next
Next
Next
For l = 1 To 120
v1(l) = 0
Next
For Each cell In Range("G13:G17")
arr = cell.Resize(1, 6).Value
For l = 1 To 120
If v1(l) = 0 Then
cnt = 0
For i = 1 To 3
For j = 1 To 6
If arr(1, j) = v(l, i) Then cnt = cnt + 1
Next
Next
If cnt = 2 Then
v1(l) = 1
n = n + 1
End If
End If
Next l
Next cell
MsgBox n
End Sub


gave me what I expected, but for your example, I expected 60, not 80.

--
Regards,
Tom Ogilvy

"Paul Black" wrote in message
ps.com...
Hi Tom,

I Hope your Year is going Well.
Have you had Time to have a Look through my above Post yet?.

Thanks in Advance.
All the Best.
Paul

Paul Black wrote:
Hi Tom,

Thanks for the Response.
Unfortunately the Code did Not do as I Expected. This is Due to MY Bad
Explanation of what I would like.
I think the Following Explanation is Better Suited and will Hopefully
bring it into Focus.

Lets Assume that we have 6 Numbers in Each Combination ...
01 02 03 08 09 10
etc
etc
... for Example.

If we just Concentrate on the 2 if 3 Scenario, the Code Needs to do the
Following :-
Because the Maximum Number in the Combination in this Case is 10, we
Need to Calculate and Store the 120 Combinations ( =COMBIN(10,3) = 120 )
Produced for 3 Numbers from 10 Numbers. I Assume this will be Stored in
the Scripting Dictionary.
Then Loop through Each Triple Stored in the Scripting Dictionary and
Count How Many of those 120 Triples have Exactly 2 Numbers in Our
Combination of 01 02 03 08 09 10. The Answer in this Case for
Combination 01 02 03 08 09 10 should Equal 80.
The Macro Basically Needs to Loop through Every 3 Number Combination in
the Scripting Dictionary and Compare it to Each Individual Combination
in Our Wheel. If Exactly 2 Numbers in Any of Our Wheel Combinations
Match Exactly 2 Numbers in Any of the 3 Number Combinations in the
Scripting Dictionary then that is Counted and the Macro Moves on to the
Next Combination in the Scripting Dictionary etc.
Or the Macro could Loop through the Wheel and Compare it to the
Scripting Dictionary.

Thanks Very Much in Advance.
All the Best.
Paul

*** Sent via Developersdex http://www.developersdex.com ***