6/49 combination

[GillesD]

Selecting combinations for 7 / 27

PAB

I will supply you with one way to identify combinations that meet specific criteria soon. But this could be obtained through various means.

The first two rejection criteria are quite clear:
- all numbers being EVEN (like 2, 4, 6, 8, 10, 12 and 14);
- all numbers being ODD (like 1, 3, 5, 7, 9, 11 and 13).

But the last criteria is not clear: "Total is Greater Than or Less Than 98" would be the same as "Total is equal to 98". Is that what you meant, or did you mean "Total is Greater Than 218 or Less Than 98" which would accept only combinations with a total between 98 and 218 (or any other values you specify)?

 

[PAB]

Hi GillesD,

You are a Diamond.
I am looking forward to seeing ways of identify combinations that meet specific criteria.
As far as the "Total is Greater Than or Less Than 98", you are quite right ( I don't know what I was thinking ) , I did mean the flexibility if the "Total is Greater Than 218 or Less Than 98" which would accept only combinations with a total between 98 and 218 ( or any other values that I specify ).
Another one that might significantly reduce the Number of Combinations is Consecutive Numbers ( Consecutives of 2,3,4,5, 6 and 7 ).

Thanks ever so much for your help and time.
Best Regards
PAB

 

[GillesD]

Selecting combinations in Excel

If all combinations are listed in a sheet, this is one way but not the only one (a small program in VBA could be another way) to achieve what you are looking for.

First insert a line on top of all combinations and add labels N1, N2, …, N7 to columns A to G and copy this for all columns with combinations.

Then insert new sheets to get a total of 14 sheets and transfer each set of 7 individual columns to a single sheet. The first set of 7 columns should remain on Sheet1, the next set of 7 columns goes on Sheet2 and so on to fill each sheet with 65,000 combinations except the last one with 43,030 combinations.

Enter the labels Crit. 1 and Crit. 2 (for Criteria #) in cells H1 and I1. Then in cells H2 and I2, enter the following formulas:

=OR(AND(A2/2=INT(A2/2),B2/2=INT(B2/2),C2/2=INT(C2/2),D2/2=INT(D2/2),E2/2=INT(E2/2),F2/2=INT(F2/2),G2/2=INT(G2/2)),AND(A2/2<>INT(A2/2),B2/2<>INT(B2/2),C2/2<>INT(C2/2),D2/2<>INT(D2/2),E2/2<>INT(E2/2),F2/2<>INT(F2/2),G2/2<>INT(G2/2)))

=OR(SUM(A2:G2)<98,SUM(A2:G2)>218)

The first formula will return TRUE only if either all 7 numbers are all EVEN or all ODD and the second formula will return TRUE if the sum is lower than 98 or higher than 218. The first combination with numbers 1, 2, 3, 4, 5 ,6 and 7 will return FALSE in H2 and TRUE in I2.

Then copy the formulas down to apply then to all combinations on that page. And finally activate the automatic filter feature and select the value for which you want lines to be displayed (in your example, look for the value FALSE in both columns H and I). The rows not meeting the values chosen will be masked (in this case with a TRUE value in both columns).

Using this set-up, you can change the formulas as you want to identify the combinations that meet your criteria. You may even work with more criteria if you want (like rejecting combinations with 5 or more consecutive numbers). Just add more columns with formulas that identify specific characteristics for the 7-numbers combinations. It might not be the best way to do it but it offers some flexibility. The 14 sheets with data is the major problem with this approach.

You could save some time in selecting all sheets when you set-up the spreadsheet but the automatic filter feature does not work when multiple sheets are selected.

But if you want to list only some specific combinations that meet known criteria, then the VBA program given before could be modified to list only those that meet the criteria. This would effectively reduce the number of combinations in the sheet. But some coding will be necessary if you want to change your criteria.

 

[PAB]

Hi GillesD,

Thanks very much for your time in coming up with a solution, it produces exactly the results required. The only downside is the Number of sheets and the Memory used. As you said before, the best way to approach this is probably using a small VB Program, of which, unfortunately I do not know how.

Good luck tonight.
All the Best.
PAB