PDA

View Full Version : Calculate Matches



PAB
08-23-2012, 08:47 AM
Good afternoon,

I have a list of 6 number combinations with numbers from 1 to 49 in cells E8:J?.
What I would like to do is cycle through ALL 8 number combinations and just list those 8 number combinations that have had MORE than say 20 match 3 or better.
Is this possible with 8 number combinations being so big?
Thanks in advance.

Kind regards,
PAB

russtownsend
08-23-2012, 02:31 PM
Can you clarify a little please ? I can't picture clearly what you're tryimg to do.

PAB
08-23-2012, 02:41 PM
Thanks for the reply and welcome to the board.

Basically, I am trying to find the best performing set of 8 numbers that has had the most 3, 4, 5 & 6 matches in all of the 6 number combinations.

Regards,
PAB

PAB
08-26-2012, 04:30 AM
I have put the following together in order to try and make it a bit clearer of what I would like.
I have explained it in a bit more detail in the middle of the code below.

Option Explicit
Sub Count_Matches()
Dim wsR As Worksheet
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 G As Integer
Dim H As Integer
Dim nMinA As Integer
Dim nMaxH As Integer
Dim n As Long
With Application
.ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
End With
Set wsR = Sheets("Results")
nMinA = 1
nMaxH = 12 ' I have used twelve here but it will actually be 49.
n = 0
With wsR
Cells.Delete
End With
For A = nMinA To nMaxH - 7
For B = A + 1 To nMaxH - 6
For C = B + 1 To nMaxH - 5
For D = C + 1 To nMaxH - 4
For E = D + 1 To nMaxH - 3
For F = E + 1 To nMaxH - 2
For G = F + 1 To nMaxH - 1
For H = G + 1 To nMaxH
' Ignore this bit below.
If n = 65501 Then
n = 1
ActiveCell.Offset(-65500, 10).Select
End If

' The above will cycle through ALL the 8 number combinations from nMaxH.
' What I would like it to do is compare the numbers in the 8 number combinations
' to ALL the 6 number combinations in cells E10:J? and count the number of matches.
' I then want it to output in cells N10:U19 the top ten performing 8 number
' combinations for 0, 1, 2, 3, 4, 5 & 6 number matches.
ActiveCell.Offset(n, 0).Value = A
ActiveCell.Offset(n, 1).Value = B
ActiveCell.Offset(n, 2).Value = C
ActiveCell.Offset(n, 3).Value = D
ActiveCell.Offset(n, 4).Value = E
ActiveCell.Offset(n, 5).Value = F
ActiveCell.Offset(n, 6).Value = G
ActiveCell.Offset(n, 7).Value = H
n = n + 1
Next H
Next G
Next F
Next E
Next D
Next C
Next B
Next A
With Application
.DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
End With
End Sub

Thanks in advance.

snb
08-26-2012, 04:40 AM
Please post a sample workbook.

PAB
08-26-2012, 04:53 AM
Thanks for the reply snb.

I actually left out where to place the 8 number combinations that are the best performing, but I think the inclusion of the sample Workbook will rectify this.

PAB
08-26-2012, 05:04 AM
Please ignore previous attachement and find new attachement below.

PAB
08-28-2012, 02:11 AM
I have done some work on this over the weekend and tried to incorporate some code that actually calculates the number of matches if the combinations in cells N:U are already there.

The objective is to cycle through ALL the 8 number combinations from nMaxH and compare the numbers in the 8 number combinations to ALL the 6 number combinations in cells E10:J? and count the number of matches.

I then want it to output in cells N10:U19 the top ten performing 8 number combinations with the actual matches for 0, 1, 2, 3, 4, 5 & 6 number matches.

I hope this makes sense.
I have attached the updated Workbook.
Thanks in advance.

Kind regards,
PAB

Bob Phillips
08-28-2012, 02:39 AM
Your code doesn't exactly do a lot. Why don't you supply the first two results for N:U and explain why it is those two?

PAB
08-28-2012, 04:05 AM
Thanks for the reply xld,

This is the thing, I don't know any 8 number combination that is the best performing.
In order to find this out the program needs to cycle through ALL the 8 number combinations from nMaxH and calculate the total matches for each 8 number combination for the categories of 0, 1, 2, 3, 4, 5, 5+Bonus & 6 matches against each of the 6 number combinations in cells E:K.
Then the top 10 with the most matches I would like output to cells N:U.
I hope this explains it a bit better.

Kind regards,
PAB

Teeroy
08-28-2012, 05:09 AM
Hi PAB,

Don't mean to dash your hopes but statistically what you are trying to do is not valid, forecasting from randomised results. Putting that aside with the number of combinations you'd need to check a 1700 odd size dataset to compare against is too small (from memory only, I haven't done this type of probability in 10 years or more).

It's an interesting mental exercise though. You'd probably get a starting point using a pivot table on the results (count) to determine which numbers come up most frequently.

PAB
08-28-2012, 05:19 AM
Thanks for the reply Teeroy,

The thing is that this is not forecasting from randomised results.
The 8 number combinations produced for C(49,8) are set, as well as the actual 6 number combinations drawn.
The exercise is to find out which top 10, 8 number combinations have achieved the highest matches in matched categories of 0, 1, 2, 3, 4, 5, 5+Bonus & 6 taking into account ALL of the 6 number combinations drawn.

Kind regards,
PAB

Teeroy
08-28-2012, 02:01 PM
Hi PAB,

My bad. I made an assumption you were forecasting the lottery (or pools) from past results. Trying to generate all C(49,8) combinations and test them is analogous to a cryptographic brute force attack; if it took 1/10ms per combination to generate and test each combination you'd be going for almost 3,500 years.

I'm no mathematician but off the top of my head I'd try Set Theory to backtrack from the results and optimise the intersection. It would probably be better to decide on an approach before commencing coding.