Of which 6 lines, which covers exactly 25 numbers out of 31.

[bines53]

Hello friends,

I need help in the macro, I want to get all possible combinations, of six lines that cover exactly 25 numbers no matter which, I want output as in the attached file in J-O columns, the example has 2 combinations that I made manually.
Also in reality there are much more than 32 lines, and sometimes more lines are added.

Is it possible to build such a macro?

Thank you !

 

[Marc L]

Hi !

As a procedure just follows a logic so
any procedure can be built if its logic / algorithm is well explained …
​

 

[NARAYANK991]

Hi ,

How have you entered 13 as a number in your Combination 2 ?

Narayan

 

[bines53]

Hi Narayan ,

4 ,6 ,13 ,19, 24 ,31,
They are the six lines that cover exactly 25 numbers, the test is in the cell U2.

David

 

[NARAYANK991]

Hi Narayan ,

4 ,6 ,13 ,19, 24 ,31,
They are the six lines that cover exactly 25 numbers, the test is in the cell U2.

David

Hi ,

OK. It is clear now.

Narayan

 

[p45cal]

You realise that with your 32 rows of data that there are 74k combinations fitting the bill?
The attached has some lazily written brute force code to run through all 906k combinations of rows, to produce the 74k rows of results in about a minute. But increase the row number from 32 to 46 and you increase processing time x10.
It could be significantly leaner and faster with better written code (perhaps with some recursion).
The results start at cell J4. There are a few check formulae in column U. You can add the formula for the whole length of the result data in column U with

'check formula:
Range("U4:U" & Z).FormulaR1C1 = "=SUM(--(FREQUENCY(CHOOSE({1;2;3;4;5;6},INDEX(R1C1:R1000C6,RC[-11],0),INDEX(R1C1:R1000C6,RC[-10],0),INDEX(R1C1:R1000C6,RC[-9],0),INDEX(R1C1:R1000C6,RC[-8],0),INDEX(R1C1:R1000C6,RC[-7],0),INDEX(R1C1:R1000C6,RC[-6],0),),R1C26:R31C26)>0))"

added to the end of the macro.
Click the button at cell Q2 to start the code.
Your two sample results will appear at rows 17920 and 38548 (you can filter for them by font colour).

 

[bines53]

Hi p45cal ,

Excellent, thank you for your help!

David