Finding out Multiple scenarios and options based on set requirements and combinations of data set

[mnty89]

Example data below to explain a little further-

Basically what I need to do is figure out a macro or a formula column which will calculate out the possible outcomes based on the data set that will find the consolidated versions possible.

Maximum weight for this example is 20,000
Maximum volume will be 100
Maximum spaces would be 10

I need to use the data below to find out all of the possibilities based on these requirements that I could combine the "Loads" to make sure they fall under the maximum or meet it. I just added the remaining based on the original column, but if that's not needed that's fine as well.

Any ideas or has anyone done this in a similar way for another application?

I'm hoping for something I can apply to a real life scenario on a broader scale for something currently being done manually.

Load	Weight	Volume	Spaces	Remaining Weight (Max 20000)	Remaining Volume (Max 100)	Remaining Spaces (Max 10)
1	10000	50	5	10000	50	5
2	5000	15	3	15000	85	7
3	11000	60	6	9000	40	4
4	9000	40	4	11000	60	6
5	3000	30	1	17000	70	9
6	500	6	1	19500	94	9
7	6000	25	2	14000	75	8
8	8000	35	4	12000	65	6
9	700	8	8	19300	92	2
10	900	7	5	19100	93	5

<tbody>
</tbody>

 

[jackd]

I suggest you give us a sample of what exactly you mean by

I need to use the data below to find out all of the possibilities based on these requirements that I could combine the "Loads" to make sure they fall under the maximum or meet it. I just added the remaining based on the original column, but if that's not needed that's fine as well.

For example in Load 2, it currently has weight 5000, volume 15 and spaces 3.
So as I read your post, your maximum load has weigh 20000 or volume 100 or space 10.
It would depend on the characteristics of your "to be loaded Items" which would count against your maximum load parameters.

 

[mnty89]

Sorry- to clarify.. basically all of the list of "loads" are unique values where each row gives information on one specific load. The load column as a whole are individual ones which I need to get the combinations for the possibilities on to meet the requirements.

To compare it to current functionality I essentially need to use a solver type scenario but with a range not just a single targeted value and with three target constraints.

Or it could also be seen as the SUMMATCH addon but I would want it to output a list of the scenarios it gives, not just highlight them once they are identified, and also based on three constraints not just the one range.

 

[xenou]

Looks like an Excel Solver problem to me.

 

[mnty89]

From my understanding solver only works to define to an exact goal, and with 200 variables. This would likely have much more than 200 (upwards into the thousands) and I would want all outcomes to have a range goal, not just one set value. The weights will be very erratic so unless it can define to a range of values it will not be beneficial since it will never add to a clean single value..

Any ideas?

 

[xenou]

How many final outcomes do you expect? What would you do with the answer (let's say there's 2000 possible outcomes and you get those combinations)?

 

[mnty89]

In practice it will essentially be a list of a few thousand "loads" with unique weights, volumes, and spaces. The goal is to determine how many combined loads could be built within a min max range for each constraint of weight, volume and spaces.

Ideally I would like the outcome to be a list of the most "full" based on constraint values and no duplicates to be used within the output. So each load is only accounted for once in the outputted optimization of combinations...