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.

 

[mirabeau]

But there are 2 small problems.
1) The lottery closed ))

Yeah. Sorry about that. I did it when I saw it coz it looked interesting.

Maybe useful if you ever try other lotteries.

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

It wasn't clear to me from your initial posting whether or not you wanted this. I don't know much about lotteries.

However, easily done if need be. It just takes about 3 extra lines of code.

 

[marus]

Well, I didn't win but I'm still interested in it for future extractions.
If you would be so kind to modify the code I would appreciate it.

I didn't think this could be possible but yet again here we are. Thanks for making it possible for me and thanks for taking time to answer my posts.

 

[mirabeau]

Does this one do it for you?

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

Sub select9uniq()
t = Timer
Dim c, d As Object, tmp
Dim rs As Long, b, u As String
Dim i As Long, j As Long
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 ml: a(j) = j: Next j
    For j = 1 To cl
        x = Int(Rnd * (ml - j + 1)) + j
        tmp = a(j): a(j) = a(x): a(x) = tmp
    Next j
    c = a
    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]

I runed it a couple of times and it seems in only generates small numbers from 1 to 12. It can't get past 12, why is that?

And when I paste the macro in generates 2 macros:
- select9uniq
- sortit

I runed sortit separately and I can't figure what it does, or is it part of select9?

 

[mirabeau]

I runed it a couple of times and it seems in only generates small numbers from 1 to 12. It can't get past 12, why is that?

That was an oversight by me. Change the line 2nd from the top to Const ml = 40 (or whatever you want the maximum number to be).

And when I paste the macro in generates 2 macros:
- select9uniq
- sortit

I runed sortit separately and I can't figure what it does, or is it part of select9?

The select9uniq() macro is the one that gives the results, and is the only one you need to specifically run.

Sortit() is just a sorting macro which is called (automatically) on a couple of occasions as part of select9uniq() when some sorting, which is necessary for the approach I used, is needed.

It won't do anything by itself as it stands, but could easily be converted to a stand-alone sort routine if so wanted. You would just have to feed in a suitable input, which in this case is done by select9uniq().

 

[marus]

Ok. One more thing as I'm not completely sure. Since the code is to advanced for me I can't see the part in it which takes into account my history of extractions.

I have arranged them on lines, 6 numbers on one line and I have 990 lines. So the domain is A1:F990.
After I run the code it generates another line right after my history.

Just want to make sure.

So the script takes in the account each line of 6 numbers, doesn't matter the order they are in, and generates a 9 set of numbers that no matter what combination they are grouped as 6 they won't be found in the history of extractions. Am I right?

 

[zerro]

I've run it with 12 numbers and it does hit with 6 quite a lot on 4000lines.
If you copy the line generated into sheet2 and use the first macro I gave you it will show you all hits of the macro your are using.
It does not seem to be looking at previous draws, I might be wrong but 12 numbers should still not show as many 6s and 5s
considering that 5 numbers has only hit duplicated 20 times in 4000 draws.
You can change the first line to generate any amount of numbers

 

[mirabeau]

Ok. One more thing as I'm not completely sure. Since the code is to advanced for me I can't see the part in it which takes into account my history of extractions.

I have arranged them on lines, 6 numbers on one line and I have 990 lines. So the domain is A1:F990.
After I run the code it generates another line right after my history.

Just want to make sure.

So the script takes in the account each line of 6 numbers, doesn't matter the order they are in, and generates a 9 set of numbers that no matter what combination they are grouped as 6 they won't be found in the history of extractions. Am I right?

I haven't at any stage figured out how your 6 fits into all this.

What the code does is to check a dataset on sheet1. This dataset I have taken to consist of a number of rows (the code checks how many) of 9 numbers (in columns A to I). The code checks that your dataset has no combination of 9 numbers repeated, regardless of their order.

The code then generates a new set of 9 numbers (non-repeating, between 1 and 40) that previously has not occurred, regardless of order, in your dataset and puts this as a row colored yellow at the bottom of the dataset.

The code does just this and no more.

If you want anything done about 6 numbers anywhere, you'd better restate just what you want, preferably giving some simplified data to illustrate.

 

[marus]

I think I done just that in the first post.

 

[mirabeau]

I think I done just that in the first post.

Not sufficient I'm afraid.

It gives me no indication of the relationship you seem to envisage between some 6 numbers and some (other??) 9 numbers.