Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combination help:
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 a 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combination help:
Google with "excel permutations combinations" to get about 200K hits
best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email wrote in message ... 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 a 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combination help:
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 " wrote: 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 a 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combination help:
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 "Mike H" wrote: 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 " wrote: 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 a 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combination help:
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combination help:
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 " wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combination help:
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 "Mike H" wrote: 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 " wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combination help:
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combination help:
Cheers, I'II check back, may have a snifter myself its a bit cool up
north Ste |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
combination | Excel Discussion (Misc queries) | |||
Combination | Charts and Charting in Excel | |||
Possible Combination | Excel Programming | |||
combination | Excel Programming | |||
combination | Excel Programming |