You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
Hi Mr.GillesD
- Thread starter tomtom
- Start date
Re: Combinations from 3 groups
Well’ just three groups of numbers that make combos…. .many of them, of course…But I don’t want to combine (for example) exactly two numbers from all three group(2,2,2). All the other combos are acceptable…(2,3,1), (1,2,3), (0,1,5), (2,0,4) etc,…
GillesD said:Tomtom
This should be easy but I do not fully understand your question. Can you be more precise? And if possible with an example.
Well’ just three groups of numbers that make combos…. .many of them, of course…But I don’t want to combine (for example) exactly two numbers from all three group(2,2,2). All the other combos are acceptable…(2,3,1), (1,2,3), (0,1,5), (2,0,4) etc,…
6-number combinations out of 15
My approach for your question would not rely entirely on a macro. The macro listed at the end of this post will generate all 5005 6-number combinations out of 15 numbers. It will also label the first row with titles.
To identify combinations meeting specific criteria, I would enter a formula in the Test column (Column H). Then using the Automatic Filter in Excel, you can filter the combinations according to your needs.
In your case, for rejecting the combinations with 2 numbers in (1, 2, 3, 4, 5) and 2 numbers in (11, 12, 13, 14, 15), obviously with 2 numbers in (6, 7, 8, 9, 10), the formula in H2 would be:
=AND(COUNTIF(B2:G2,"<=5")=2,COUNTIF(B2:G2,">=11")=2)
Then copy it down to H5006. The formula will either return TRUE (1000 times) or FALSE (4005 times). Finally, use the Automatic Filter to filter out the 1000 combinations you do not want.
For me, this method is more flexible as you can modify the formula in column H to fit any criteria you want.
Macro (use a blank sheet when starting the macro)
Sub Comb_6x15()
Dim A As Integer, B As Integer, C As Integer, D As Integer, E As Integer, F As Integer
Dim N As Integer
Application.ScreenUpdating = False
Range("A1").Select
ActiveCell.Offset(0, 0).Value = "#"
ActiveCell.Offset(0, 1).Value = "N1"
ActiveCell.Offset(0, 2).Value = "N2"
ActiveCell.Offset(0, 3).Value = "N3"
ActiveCell.Offset(0, 4).Value = "N4"
ActiveCell.Offset(0, 5).Value = "N5"
ActiveCell.Offset(0, 6).Value = "N6"
ActiveCell.Offset(0, 7).Value = "Test"
N = 0
For A = 1 To 10
For B = A + 1 To 11
For C = B + 1 To 12
For D = C + 1 To 13
For E = D + 1 To 14
For F = E + 1 To 15
N = N + 1
ActiveCell.Offset(N, 0).Value = N
ActiveCell.Offset(N, 1).Value = A
ActiveCell.Offset(N, 2).Value = B
ActiveCell.Offset(N, 3).Value = C
ActiveCell.Offset(N, 4).Value = D
ActiveCell.Offset(N, 5).Value = E
ActiveCell.Offset(N, 6).Value = F
Next F
Next E
Next D
Next C
Next B
Next A
Application.ScreenUpdating = True
End Sub
My approach for your question would not rely entirely on a macro. The macro listed at the end of this post will generate all 5005 6-number combinations out of 15 numbers. It will also label the first row with titles.
To identify combinations meeting specific criteria, I would enter a formula in the Test column (Column H). Then using the Automatic Filter in Excel, you can filter the combinations according to your needs.
In your case, for rejecting the combinations with 2 numbers in (1, 2, 3, 4, 5) and 2 numbers in (11, 12, 13, 14, 15), obviously with 2 numbers in (6, 7, 8, 9, 10), the formula in H2 would be:
=AND(COUNTIF(B2:G2,"<=5")=2,COUNTIF(B2:G2,">=11")=2)
Then copy it down to H5006. The formula will either return TRUE (1000 times) or FALSE (4005 times). Finally, use the Automatic Filter to filter out the 1000 combinations you do not want.
For me, this method is more flexible as you can modify the formula in column H to fit any criteria you want.
Macro (use a blank sheet when starting the macro)
Sub Comb_6x15()
Dim A As Integer, B As Integer, C As Integer, D As Integer, E As Integer, F As Integer
Dim N As Integer
Application.ScreenUpdating = False
Range("A1").Select
ActiveCell.Offset(0, 0).Value = "#"
ActiveCell.Offset(0, 1).Value = "N1"
ActiveCell.Offset(0, 2).Value = "N2"
ActiveCell.Offset(0, 3).Value = "N3"
ActiveCell.Offset(0, 4).Value = "N4"
ActiveCell.Offset(0, 5).Value = "N5"
ActiveCell.Offset(0, 6).Value = "N6"
ActiveCell.Offset(0, 7).Value = "Test"
N = 0
For A = 1 To 10
For B = A + 1 To 11
For C = B + 1 To 12
For D = C + 1 To 13
For E = D + 1 To 14
For F = E + 1 To 15
N = N + 1
ActiveCell.Offset(N, 0).Value = N
ActiveCell.Offset(N, 1).Value = A
ActiveCell.Offset(N, 2).Value = B
ActiveCell.Offset(N, 3).Value = C
ActiveCell.Offset(N, 4).Value = D
ActiveCell.Offset(N, 5).Value = E
ActiveCell.Offset(N, 6).Value = F
Next F
Next E
Next D
Next C
Next B
Next A
Application.ScreenUpdating = True
End Sub
Re: 6-number combinations out of 15
Mr. GillesD thanks a lot for your help and a quick response. Sorry to say, but it seems that I formulated my question incorrectly. In fact, I was thinking about a (one) macro that is an arrangement of the one that makes combos out of 15 numbers, and =AND(COUNTIF(B2:G2,"<=5")=2,COUNTIF(B2:G2,">=11")=2). Therefore, a macro that already included the Function. From my point of view, such a macro will be more convenient since by itself will create requested 4005 combos without any need for future manual labor that includes copying and pasting into various cells and thousands of lines ….I had in mind your proposed solution earlier than I asked you for a way out. The only difference was that my COUNTIF counts 2 numbers from a group in a way {1;2;3;4;5} instead of “<=5”. In such a case is easier to exchange numbers in order to use any group of five numbers. But still, after the macro finishes a part of the job, it is a bit too much of work necessary to put in to get the result…. In reality, I’m not using any software to make combos from a few reasons, and one of them is that I simply can’t find some spare time for testing and searching for a really good one. However, a simple macro that is fast in producing some requested results is worth some extra effort, I think….
GillesD said:My approach for your question would not rely entirely on a macro. The macro listed at the end of this post will generate all 5005 6-number combinations out of 15 numbers. It will also label the first row with titles.
To identify combinations meeting specific criteria, I would enter a formula in the Test column (Column H). Then using the Automatic Filter in Excel, you can filter the combinations according to your needs.
In your case, for rejecting the combinations with 2 numbers in (1, 2, 3, 4, 5) and 2 numbers in (11, 12, 13, 14, 15), obviously with 2 numbers in (6, 7, 8, 9, 10), the formula in H2 would be:
=AND(COUNTIF(B2:G2,"<=5")=2,COUNTIF(B2:G2,">=11")=2)
Then copy it down to H5006. The formula will either return TRUE (1000 times) or FALSE (4005 times). Finally, use the Automatic Filter to filter out the 1000 combinations you do not want.
For me, this method is more flexible as you can modify the formula in column H to fit any criteria you want.
Macro (use a blank sheet when starting the macro)
Sub Comb_6x15()
Dim A As Integer, B As Integer, C As Integer, D As Integer, E As Integer, F As Integer
Dim N As Integer
Application.ScreenUpdating = False
Range("A1").Select
ActiveCell.Offset(0, 0).Value = "#"
ActiveCell.Offset(0, 1).Value = "N1"
ActiveCell.Offset(0, 2).Value = "N2"
ActiveCell.Offset(0, 3).Value = "N3"
ActiveCell.Offset(0, 4).Value = "N4"
ActiveCell.Offset(0, 5).Value = "N5"
ActiveCell.Offset(0, 6).Value = "N6"
ActiveCell.Offset(0, 7).Value = "Test"
N = 0
For A = 1 To 10
For B = A + 1 To 11
For C = B + 1 To 12
For D = C + 1 To 13
For E = D + 1 To 14
For F = E + 1 To 15
N = N + 1
ActiveCell.Offset(N, 0).Value = N
ActiveCell.Offset(N, 1).Value = A
ActiveCell.Offset(N, 2).Value = B
ActiveCell.Offset(N, 3).Value = C
ActiveCell.Offset(N, 4).Value = D
ActiveCell.Offset(N, 5).Value = E
ActiveCell.Offset(N, 6).Value = F
Next F
Next E
Next D
Next C
Next B
Next A
Application.ScreenUpdating = True
End Sub
Mr. GillesD thanks a lot for your help and a quick response. Sorry to say, but it seems that I formulated my question incorrectly. In fact, I was thinking about a (one) macro that is an arrangement of the one that makes combos out of 15 numbers, and =AND(COUNTIF(B2:G2,"<=5")=2,COUNTIF(B2:G2,">=11")=2). Therefore, a macro that already included the Function. From my point of view, such a macro will be more convenient since by itself will create requested 4005 combos without any need for future manual labor that includes copying and pasting into various cells and thousands of lines ….I had in mind your proposed solution earlier than I asked you for a way out. The only difference was that my COUNTIF counts 2 numbers from a group in a way {1;2;3;4;5} instead of “<=5”. In such a case is easier to exchange numbers in order to use any group of five numbers. But still, after the macro finishes a part of the job, it is a bit too much of work necessary to put in to get the result…. In reality, I’m not using any software to make combos from a few reasons, and one of them is that I simply can’t find some spare time for testing and searching for a really good one. However, a simple macro that is fast in producing some requested results is worth some extra effort, I think….
Macro modification
Tomtom
If you want the macro to just list the 4005 combinations that meet your requirements, do the following changes:
A - Just after the line "For F = E + 1 To 15', add this line:
If Not (B <= 5 And C >= 6 And D <= 10 And E >= 11) Then
B - Just before the line "Next F", add this line:
End If
Tomtom
If you want the macro to just list the 4005 combinations that meet your requirements, do the following changes:
A - Just after the line "For F = E + 1 To 15', add this line:
If Not (B <= 5 And C >= 6 And D <= 10 And E >= 11) Then
B - Just before the line "Next F", add this line:
End If
Re: Macro modification
Mr. GillesD, from previous posts I concluded that you use your combinations quite randomly and I fully respect your approach (the old good fortune ought to beat chaos at the end). Still, in strictly my opinion, chaos has so huge winning chances (nearly 14mill: 1) that the old good fortune may possibly welcome some accessible assistance… From that raison d'être, I believe your macro might be a great tool. However, wish to see you smiling in a lottery paper soon….
GillesD said:Tomtom
If you want the macro to just list the 4005 combinations that meet your requirements, do the following changes:
A - Just after the line "For F = E + 1 To 15', add this line:
If Not (B <= 5 And C >= 6 And D <= 10 And E >= 11) Then
B - Just before the line "Next F", add this line:
End If
Mr. GillesD, from previous posts I concluded that you use your combinations quite randomly and I fully respect your approach (the old good fortune ought to beat chaos at the end). Still, in strictly my opinion, chaos has so huge winning chances (nearly 14mill: 1) that the old good fortune may possibly welcome some accessible assistance… From that raison d'être, I believe your macro might be a great tool. However, wish to see you smiling in a lottery paper soon….