Combination help:

[stemac]

Hi, The question is for our lotto syndicate:

The numbers range 1 to 49 with a drawn set of 6 numbers:

In any set of of six numbers there are:

246,820 combinations that contain 3 of the six numbers drawn

13,545 combinations that contain 4 of the six numbers drawn

252 combinations that contain 5 of the six numbers drawn

How can I generate the the 252 - 5 of 6 number combinations from the 6
numbers?
and the 3 and 4 out of six.

In effect there will be 252 sets of 6 numbers generated which 5 of the
drawn numbers.
(In seperate cells please)

I had a look at Myrna Larsons code combinations/permutations (now
thats clever!)
and trawled through lots of past posts by such as Tom Ogilvy and Myrna
Larson
but cannot find what I want to see:

Thanks very much for any help/pointers

Ste

 

[Bernard Liengme]

Google with "excel permutations combinations" to get about 200K hits
best wishes

 

[Mike H]

Hi,

I don't understand one of either the question or your maths.

252 combinations that contain 5 of the six numbers drawn

if 6 numbers are drawn (say) 1 to 6 then there are only 6 combinations of 5
numbers, those being:-


1,2,3,4,5
1,2,3,4,6
1,2,3,5,6
1,2,4,5,6
1,3,4,5,6
2,3,4,5,6


and for combinations of 3 from 6:-

1,2,3,,
1,2,4,,
1,2,5,,
1,2,6,,
1,3,4,,
1,3,5,,
1,3,6,,
1,4,5,,
1,4,6,,
1,5,6,,
2,3,4,,
2,3,5,,
2,3,6,,
2,4,5,,
2,4,6,,
2,5,6,,
3,4,5,,
3,4,6,,
3,5,6,,
4,5,6,,

What am I missing?

Mike

 

[Mike H]

Hi,

I know what I'm missing, the ability to read a question correctly. If I
understand of the 6 number drawn you want every combination of 5 with the 6th
number being each of the numbers not drawn added to that 5 making 258
combination not 252.

Put you numbers drawn in a1 to A6. Right click your sheet tab, view code and
paste the code below in and run it. the codes a bit messy because I rushed.

Sub thelottery()
Count = 1
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set myRange = Range("A1:A" & LastRow)
For Each c In myRange
numbers = numbers + 1
Next
Dim n(49)
For p = 1 To numbers
n(p) = Cells(p, 1).Value
Next
For i = 1 To numbers
For j = 1 To numbers
If j <= i Then GoTo 100
For k = 1 To numbers
If k <= j Then GoTo 200
For l = 1 To numbers
If l <= k Then GoTo 300
For m = 1 To numbers
If m <= l Then GoTo 400
For x = 1 To numbers
If i = x Then firstno = n(x)
Next
For x = 1 To numbers
If j = x Then secondno = n(x)
Next
For x = 1 To numbers
If k = x Then thirdno = n(x)
Next
For x = 1 To numbers
If l = x Then fourthno = n(x)
Next
For x = 1 To numbers
If m = x Then fifthno = n(x)
Next
For x = 1 To 49
If WorksheetFunction.CountIf(Range("A1:A6"), x) = 0 Then
notdrawn = x
Cells(Count, 2).Value = firstno
Cells(Count, 2).Offset(, 1).Value = secondno
Cells(Count, 2).Offset(, 2).Value = thirdno
Cells(Count, 2).Offset(, 3).Value = fourthno
Cells(Count, 2).Offset(, 4).Value = fifthno
Cells(Count, 2).Offset(, 5).Value = x
Count = Count + 1
End If
Next
400 Next
300 Next
200 Next
100 Next
Next
Cells(1, 8).Value = Count - 1
End Sub

Mike

 

[stemac]

Hi Bernard, I did google "excel permutations combinations" and went
through all the 'Combinations' previous posts
but unfortunately could not find any code that does anything like the
code Mike has written...

Mike, that code is fantastic! thankyou very much, there is NO way I
could have written it.
you are right, I thought there would be 252 combo's but I was wrong.

Q. Where in the code would I adjust it to generate 4's and the 3's
I am not pretending to understand your code, because I don't.
Is the alteration something to do with the fourthno and fifthno?

Once again...cheers

Ste

 

[Mike H]

Hi,

It would be a complete re-write to do it for 3 or 4 because for (say) 3 you
would need to add 3 unused numbers. I did consider trying to make the code
generic for 3,4 or 5 but quickly concluded that it would be a lot more
complicated then I initally thought.

Mike

 

[Mike H]

I'm forgetting my manners, thank you for the kind comments about the code and
I'm glad I could help. It's getting very late now here in the UK, check back
tomorrow, I'm intrigued by this and I think I'll have a go at a generic
solution for 3,4 or 5 numbers but right now a couple of vodkas and bed.

Mike

 

[stemac]

Hi Mike, thanks for the response and I understand.

The code you have written gives me something solid to work with
and I am very grateful.

Cheers

Ste

 

[stemac]

Cheers, I'II check back, may have a snifter myself its a bit cool up
north

Ste