Generate numbers with criteria based on a series of data

[marus]

Let say we have a lottery with 6 numbers extracted between 1 and 40
Here are some examples of extractions:
1, 5, 23, 35, 11, 6
12, 15, 24, 6, 7, 38
2, 25, 32, 17, 14, 11

I want to generate a series of 9 numbers based on a data base like those extractions in the example with the condition that in any order you take them they have never been extracted as 6 numbers before.
Like 3, 4, 8, 9, 10, 13, 16, 18, 19

Please help, I need this in a hurry.

 

[zerro]

Here is a macro that will show you basically what you want, Paste your data in sheet1
Enter your nine numbers in sheet2.
Run the macro
It will show you that your 9 numbers have never been drawn, It will highlite all your hits and taking a system 9 equals 84 combinations is only a drop in the ocean of 3,838,380 The ranges can be changed to suit but I have done this entering 20 numbers out off 38 on Australian game and it's more misses than picking the 6 numbers and 20 numbers has 38760 combinations just over 10% of numbers. A 20,000 dollar bet in Australia.
Good Luck.

Sub FindMatches()
Set MyFunction = Application.WorksheetFunction
Set MyRange1 = Sheets("Sheet1").Range("A1:H1094")
Set MyRange2 = Sheets("Sheet2").Range("A1:Z17")
For Each Cell In MyRange1
If (Cell.Value <> "" And MyFunction.CountIf(MyRange2, Cell.Value) <> 0) Then
Cell.Interior.ColorIndex = 3
End If
Next Cell
End Sub

 

[marus]

It is helpful but not what I was intended.
I would like the script to generate those numbers to me.
Can you do that for me?
Nonetheless it's brilliant, thank you so much.

 

[zerro]

Sorry no I can't, I found that macro a few years ago and the number of combinations you want to generate is going to be 3,838,296 combinations that haven't been drawn plus take off the amount that has been drawn.
Even if you had a database with 10,000 draws you still have millions of combinations that will never be drawn in our lifetime
Say 3 draws a week = 3505 years before each draw has a chance to happen and then there would still be possibly hundreds of thousands of combinations that still haven't happened.
9 numbers is not enough numbers to play with, you need at least 20 and wheeling systems do not guarantee you a major prize, you need full systems that take every combination.
Here is a macro that will produce all from 6/40
Enter your combos as they are usingin this macro,
Single cell Just do a replace then use first macro to find matches
or just use your database against the combinations produced.
Below is 84 combos for 6 out of 9
01-02-03-04-05-06
01-02-03-04-05-07
01-02-03-04-05-08
01-02-03-04-05-09
01-02-03-04-06-07
01-02-03-04-06-08
01-02-03-04-06-09
01-02-03-04-07-08
01-02-03-04-07-09
01-02-03-04-08-09
01-02-03-05-06-07
01-02-03-05-06-08
01-02-03-05-06-09
01-02-03-05-07-08
01-02-03-05-07-09
01-02-03-05-08-09
01-02-03-06-07-08
01-02-03-06-07-09
01-02-03-06-08-09
01-02-03-07-08-09
01-02-04-05-06-07
01-02-04-05-06-08
01-02-04-05-06-09
01-02-04-05-07-08
01-02-04-05-07-09
01-02-04-05-08-09
01-02-04-06-07-08
01-02-04-06-07-09
01-02-04-06-08-09
01-02-04-07-08-09
01-02-05-06-07-08
01-02-05-06-07-09
01-02-05-06-08-09
01-02-05-07-08-09
01-02-06-07-08-09
01-03-04-05-06-07
01-03-04-05-06-08
01-03-04-05-06-09
01-03-04-05-07-08
01-03-04-05-07-09
01-03-04-05-08-09
01-03-04-06-07-08
01-03-04-06-07-09
01-03-04-06-08-09
01-03-04-07-08-09
01-03-05-06-07-08
01-03-05-06-07-09
01-03-05-06-08-09
01-03-05-07-08-09
01-03-06-07-08-09
01-04-05-06-07-08
01-04-05-06-07-09
01-04-05-06-08-09
01-04-05-07-08-09
01-04-06-07-08-09
01-05-06-07-08-09
02-03-04-05-06-07
02-03-04-05-06-08
02-03-04-05-06-09
02-03-04-05-07-08
02-03-04-05-07-09
02-03-04-05-08-09
02-03-04-06-07-08
02-03-04-06-07-09
02-03-04-06-08-09
02-03-04-07-08-09
02-03-05-06-07-08
02-03-05-06-07-09
02-03-05-06-08-09
02-03-05-07-08-09
02-03-06-07-08-09
02-04-05-06-07-08
02-04-05-06-07-09
02-04-05-06-08-09
02-04-05-07-08-09
02-04-06-07-08-09
02-05-06-07-08-09
03-04-05-06-07-08
03-04-05-06-07-09
03-04-05-06-08-09
03-04-05-07-08-09
03-04-06-07-08-09
03-05-06-07-08-09
04-05-06-07-08-09

Below macro is for 2003
change the 65000 to a 1000000 if using 2007 or later
change the 65001 to a 1000001 if using 2007 or later

Option Explicit
Dim A As Integer, B As Integer, C As Integer, D As Integer, E As Integer, F As Integer
Dim N As Long, nMinA As Integer, nMaxF As Integer

Sub List_6x40Comb()
' Macro to list combinations in an Excel sheet
Sheets("Sheet1").Select
Range("A1").Select
Application.ScreenUpdating = False
N = 1
Selection.ColumnWidth = 18
ActiveCell.Value = "Comb."
' Change minimum value for A or maximum value for F
' to cover range of values to be covered
nMinA = 1
nMaxF = 40
' Start of loops for 6 variables (A-F)
For A = nMinA To nMaxF - 5
For B = A + 1 To nMaxF - 4
For C = B + 1 To nMaxF - 3
For D = C + 1 To nMaxF - 2
For E = D + 1 To nMaxF - 1
For F = E + 1 To nMaxF
' Verification of number of combinations posted
' and move to top of next column if 65,000
If N = 65001 Then
Selection.ColumnWidth = 18
N = 1
ActiveCell.Offset(-65000, 1).Select
Application.ScreenUpdating = True
Application.ScreenUpdating = False
ActiveCell.Value = "Comb."
End If
' Copying the combination in the form N1-N2-N3-N4-N5-N6 in a cell
ActiveCell.Offset(1, 0).Select
N = N + 1
ActiveCell.Value = Application.WorksheetFunction.Text(A, "00") & "-" _
& Application.WorksheetFunction.Text(B, "00") & "-" _
& Application.WorksheetFunction.Text(C, "00") & "-" _
& Application.WorksheetFunction.Text(D, "00") & "-" _
& Application.WorksheetFunction.Text(E, "00") & "-" _
& Application.WorksheetFunction.Text(F, "00")
Next F
Next E
Next D
Next C
Next B
Next A
Application.ScreenUpdating = True
End Sub

 

[zerro]

Made a mistake in the first post
6 numbers from 20 numbers has 38760 combinations, just over 1% of all combinations.

 

[marus]

The rendering time is atrocious for the last script.
The lottery in my country uses the numbers from 1 to 49. I have a database of 990 extractions, that's almost 20 years of extractions in this game. They extract 6 numbers without repetition.
I managed to make some statistical data out if it, meaning sort the numbers to see how many times each one was extracted. I even sorted them vertically and horizontally so if I create a series of 6 to mach them to see if they have already been extracted.
I know there are many alternatives left, I'm just trying to increase my chances of winning.
There is a scheme among many others which allows you to play 9 numbers instead of 6, which in my opinion is the most cost-effective and offers high probability of winning.

The first script only highlights my numbers in that big database so it's a tedious job to match those numbers in 990 lines. It makes the job easier but it's still a tedious job. Maybe you can tweak it a bit.

If this works my friend and I win, you'll get your share

 

[zerro]

I cannot tweak that macro. It is the fastest one I've found.
I have been playing the lotterys for 40 years or more and we have 4 draws a week here from 45 numbers and one draw with 38 numbers and the best I have ever done is hit 5 numbers in powerball for $54000 win using 15 number combination.
A 6 / 49 lotto has 13983816 combinations - a 40 number lotto
of 3838380 leaves a massive 10,145,436 combinations uncovered.
You can pick 40 numbers out of 49 and most likely miss out on the big one.
Your playing a futile game that is only for fun and no matter what you read in books, There is no easy way to win lotto.
The game is made for the organisers not the player.
Download a lotto trial of (Lottery Statistic Anaylser) easily found on google.
It has a Combination Creator which has about the same speed as the macro.
If you have a fast computer the combinations are generated in 8 minutes. or so but then you have to break the lines up to whatever excel you are using. 60 rows if your using 2003, 4 if your 2007 or later.

The macro I posted does it for you.
Our lotto has over 4000 draws so yours with only 990 draws is a long way off. No 2 draws in Australia have ever duplicated, 5 numbers in a draw has about 20 times but it has happened in America were 6 numbers have repeated also in Bulgaria, The Bulgarian lottery different order but exact same numbers, in the same week.
Story here

http://news.bbc.co.uk/2/hi/8259801.stm

The only tweaking I can suggest is change these lines to single 0
and the - to a space which saves a bit with the single 0 the - doesn't really do anything

ActiveCell.Value = Application.WorksheetFunction.Text(A, "0") & " " _
& Application.WorksheetFunction.Text(B, "0") & " " _
& Application.WorksheetFunction.Text(C, "0") & " " _
& Application.WorksheetFunction.Text(D, "0") & " " _
& Application.WorksheetFunction.Text(E, "0") & " " _
& Application.WorksheetFunction.Text(F, "0")

 

[marus]

Thanks for your help.
I never said I found the easy way to win. Just trying to improve my chances.
I know there is a small probability for the same numbers to occur, that's why I compare them to the database.
Since today is Easter in our country, the lottery company made a special offer in which there will be 2 extractions in the same day and you can use one ticket for the both of them. So I figure I could use the scheme with the 9 numbers but excel hasn't got me where I wanted to go, and there will still be many combinations uncovered.

For now I'm undecided what I will do in a couple of hours when I lay down the numbers. I think it's a great opportunity to use 9 numbers scheme though.

Once again thank you for your help. I'll let you know what happened after.
Cheers.

 

[mirabeau]

I want to generate a series of 9 numbers based on a data base like those extractions in the example with the condition that in any order you take them they have never been extracted as 6 numbers before.
Like 3, 4, 8, 9, 10, 13, 16, 18, 19

Hi there.

This one looked like an interesting problem. So hope you may be interested in this minor contribution.

Supposing that on worksheet1 you have a database of some 1000 (also works for a lot more if you like) sets of 9 numbers taken randomly from 1 to 40.

If want to select 9 more that, in any order you like, have never been selected before, then copy and paste the following codes into a module and run the Sub select9() code. It shouldn't take very long at all to give a valid result.

Const cl = 9
Const ml = 40
Dim a(1 To cl)

Sub select9()
t = Timer
Dim c() As Integer, d As Object
Dim rs As Long, b, u As String
Dim i As Long, j As Long
ReDim c(1 To cl)
Set d = CreateObject("scripting.dictionary")
d.comparemode = 1

With Sheets("sheet1")
    If .Cells(1) = "" Then .Cells(1) = "Database"
    rs = .Cells(Rows.Count, 1).End(3).Row
    .Cells(rs, 1).Resize(, cl).Interior.Color = xlNone
    b = .Cells(1).Resize(rs, cl)
For i = 1 To rs
    For j = 1 To cl: a(j) = b(i, j): Next j
    sortit
    u = Empty
    For j = 1 To cl: u = u & Chr(30) & a(j): Next j
    d(u) = 1
Next i

Do While d(u) = 1
Randomize
For j = 1 To cl: c(j) = Int(Rnd * ml) + 1: a(j) = c(j): Next j
    sortit
    u = Empty
    For j = 1 To cl: u = u & Chr(30) & a(j): Next j
Loop
.Cells(rs + 1, 1).Resize(, cl) = c
.Cells(rs + 1, 1).Resize(, cl).Interior.Color = vbYellow
End With
MsgBox "Code took " & Format(Timer - t, "0.000") & " secs"
End Sub

Sub sortit()
Dim x, j As Long, tmp
x = Int(cl * 0.78)
Do While x > 0
For j = 1 To cl - x
  If a(j) < a(j + x) Then
        tmp = a(j)
        a(j) = a(j + x)
        a(j + x) = tmp
    End If
Next j
x = Int(x * 0.78)
Loop
End Sub

 

[marus]

The script is awesome !!!
It's exactly what I want !
But there are 2 small problems.
1) The lottery closed ))
2) I generated a few sets of numbers and I imediatly saw an issue: there were two identical numbers in a set of nine. I kind of need them without repetition.


Thank you very much!