Calculation of all combinations by taking a min Max quantity from multiple data set

[RIC63]

In the attached file the macro returns all the combinations between the 3 data sets, I would like to modify it so that it returns all the combinations respecting the taking of min 1 max 3 values from each data set so that the summation of data taken is the value specified in G2 (7 in this case)


so for example in column 'I' should return R1 F1 F2 F3 O1 O2 O3


I use Excel 2010 2016 2021


Thanks for any suggestions you can give me

[June7]

I do not understand.

What do you mean by "summation" - what is being summed? Did you mean count?

I do not understand rules for "taking min 1 and max 3 values".

[RIC63]

Hi June7

with summation I mean the total of data taken from the 3 data set, es. 1 from the first + 3 from the second +3 from the third = 7 total ..or 3 from the first + 3 from the second +1 from the third 0 = 7

"taking min 1 and max 3 values" I think is clarified from what write above, anyway each time a minimum of 1 element up to a maximum of 3 can be taken from each data set

Thanks for your help, let me know if I've clarified

[Aussiebear]

So for clarification,

Each of the R,F & O values has the ranking of the values in the same row of Column F. R1 = 1, F4 =4, O6 = 6 etc? Or more simply I guess 2nd character of each value in the cell.

The requirements of 1 Minimum and 2 maximums can come in any arrangement Min,Max,Max or Max,Min,Max or Max,Max,Min?

Since you say you need 1 Min & 2 Max, the minimum value in G2 can not be less than 5 (since 4 would require 2 Mins and 1 Max values)and the maximum value for G2 is 17 (since 18 requires 3 Maximums).

Have a look here for a starter.https://www.ablebits.com/office-addins-blog/find-combinations-that-equal-given-sum-excel/ Here they produce results based on the use of Solver, a function, and a Macro to find the combinations. Just thinking out loud here, if the count of the combinations did not equal 3 we can disregard the combination.

...... its way too early here.

On the other hand. If G2 value = 7, then (G2-Min(F2:F7))/2 will give you the Max value. Then you can either choose R1,F1 or O1 for a minimum and any two of R3,F3 & O3.

[RIC63]

Hi Aussiebear


nice to hear from you


I think I didn't explain myself well


the 6 elements in each of the 3 columns could be any name or string...for example variations of a color e.g. Red1, Red2..Red6 and Green1..Green6 then Black1..black6 I need to create all the possible combinations of these 18 names respecting a rule which is that a minimum of one name and a maximum of three are taken from each column and the total of the elements taken must always be the value specified in G2..in this case 7 names


therefore a line produced by the code could be for example:
Red1 Green1 Green2 Green3 Black1 Black2 Black3 (combination of 7 names)

or in the case that is better produce columns
Red1
Green1
Green2
Green3
Black1
Black2
Black3

For greater clarity, to produce each row of 7 elements (or column) the code can never take 0, 4, 5 or 6 elements from a column.


I hope I have clarified
thank you for your support

[Aussiebear]

I think I didn't explain myself well


the 6 elements in each of the 3 columns could be any name or string...for example variations of a color e.g. Red1, Red2..Red6 and Green1..Green6 then Black1..black6 I need to create all the possible combinations of these 18 names respecting a rule which is that a minimum of one name and a maximum of three are taken from each column and the total of the elements taken must always be the value specified in G2..in this case 7 names'''

in effect rather than a summation = 7 you are looking at a Count of =7. Which is I believe what June7 asked you in post #2.




therefore a line produced by the code could be for example:
Red1 Green1 Green2 Green3 Black1 Black2 Black3 (combination of 7 names)

The number of possible combinations is large to say the least..

[RIC63]

I actually found 8000 combinations and the code to generate them, thanks anyway

[Aussiebear]

Any chance you can show the code, so others can learn from this subject matter?