Hi Everyone,

Here is some Code Originally Written for Something Else, 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 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 "Covered" 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

Sorry, you'd need to avoid any lingo and just explain problem for
me to even get a clue about what you are doing....

LFS

Thanks for the Reply LFS,

I Just Wanted to Explain as Clearly as Possible what I am Hopefully
Trying to Achieve.

Basically, in Cells "G13:L27" I have 15 Combinations ( the Number could
be Less Or More ) of 6 Numbers.

01 03 07 12 15 16
01 04 05 17 20 21
01 08 09 10 19 22
01 13 14 18 23 24
02 03 06 09 21 23
02 10 12 14 16 20
02 11 15 19 20 24
03 04 07 10 18 24
03 05 07 14 17 19
04 06 08 14 15 22
04 09 11 13 16 19
05 10 13 15 17 23
05 11 12 18 21 22
06 08 12 16 17 24
07 08 13 20 22 23

The Numbers above Range from 1 to 24.
Each Combination of 6 Numbers are Unique
Therefore, for this Example, there are 134,596 Combinations [ Excel
Formula COMBIN(24,6) ] of 6 Numbers from 24. I would like to Loop
through ALL these Combinations and Compare ( Match ) then to EACH of
the 15 Combinations above.
For Example, the 2 if 3 "Covered" Category Means, if I have 3 Numbers
Matched in my First Combination with Any of the 134,596 Combinations,
Count the Number of 2's Matched. This Needs to be Done for EACH
Category and EACH Combination.
So the Results ( I Don't Know if the "Covered" Results Below are
Accurate ) for EACH "Covered" 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

Many Thanks in Advance.
All the Best.
Paul

Larry Serflaten wrote:
Show quoteHide quote

It appears you just re-worded your original post.  This is a Visual Basic
newsgroup.  If you have an Access programming problem, then you
should probably be asking your qustion in an Access newsgroup.

If I understand what you are trying to say, I might outline it like this:

You have some group of unique numbers that range from 1 to 24.
From a list of all possible permutations (of a similarly sized group)
you want to categorize and count how many matches there are
between your groupings and the permutated list.  For the purpose of
this discussion your current group size is 6 unique numbers that
range from 1 to 24.

That is as much as I got.  I don't understand what you mean by
2 if 3, or any of those other combinations.


It sounds like you are asking if you have a group that has 3 matching numbers
(compared to the listing) then you want to count how many of those contain
the number 2.  (eg, 2 if 3)

In the original post:

That sounds like you want to count how many from the group that has 3
matching numbers, actually have 2 matching numbers.  Of course the answer is
all of them (they all have at least 2 that match) or none of them, (they all
have exactly 3 matching numbers).  That is what confused me the first time,
and I didn't get much farther with the current explaination.

Thake note I did not mention anything about wheels, or Access, or cells,
or anything else, other than the number sets you are working with.  See if
you can try to explain it again, as a number set problem....

LFS

[VB6] Passing the variable value to the form?
Fluctuation of printer alignment
Database and report question
Newbie: How to extract year from a Date object
msgbox problem
How can i make sure
get users screen resolution
Missing File Export Template in VB.NET
about renaming the table ..
File Copy Without Closure