Finding Unique Combinations until a certain number is reached

[robertscory2]

I work in demand planning have been creating an excel tool to make one of my functions a little easier. It basically looks at all the items a distribution center is requesting/forecasted to request, makes sure current production inventory can cover it, and then suggests rail car/truck combinations to get the items shipped out to the distribution center. Currently I am on the final stage of the project, where it suggests how to get the product shipped.

Attached is a sample of my current setup and thinking. Column C, titled "% of Car" is a calculation of the percentage of a rail car that will be taken up by the quantity demanded of the item listed in column B. My thought was to use solver to look through column C and find unique combinations that added to 1 (with a .05 buffer, so between .95 & 1). This would continue until the # Cars needed value is reached. I am having a hard time getting this to work. I don't know if there is a better way to approach it or if you could help me get it working in the current format but I would appreciate any help.

# Cars Needed = 2

Item	% of Car
22935	0.325347059
26568	0.05
26569	0.0125
40070	0.025
40497	0.0125
41883	0.0125
41884	0.0125
41886	0.0125
A0062	0.05
AX001	0.0125
AX003	0.025
AX016	0.0125
AX025	0.025
AX032	0.0125
AX033	0.1875
AX042	0.2625
AX081	0.0125
AX102	0.05
AX202	0.0125
AX833	0.0125
D0017	0.2875
K0120	0.0875
MP804	0.053758824
OG101	0.053741176
S0601	1.2125
S19	0.0125
W3628	0.025

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[shg]

Welcome to the board.

There's a workbook at https://app.box.com/shared/uhrjy318l1 that will generate this from your list:

Row\Col	B​	C​	D​	E​	F​	G​	H​	I​
10​				Total Qty​	Total Cut​
11​	​	​		27​	26​
12​	Line​	P/N or Desc​	Length​	Qty​	Qty Cut​	Scrap​	0​	0.3​
14​	1​	22935​	0.325347059​	1​	1​	0​	1​	-​
15​	2​	26568​	0.05​	1​	1​	0​	1​	-​
16​	3​	26569​	0.0125​	1​	1​	0​	1​	-​
17​	4​	40070​	0.025​	1​	1​	0​	1​	-​
18​	5​	40497​	0.0125​	1​	1​	0​	1​	-​
19​	6​	41883​	0.0125​	1​	1​	0​	1​	-​
20​	7​	41884​	0.0125​	1​	1​	0​	1​	-​
21​	8​	41886​	0.0125​	1​	1​	0​	1​	-​
22​	9​	A0062	0.05​	1​	1​	0​	1​	-​
23​	10​	AX001	0.0125​	1​	1​	0​	1​	-​
24​	11​	AX003	0.025​	1​	1​	0​	1​	-​
25​	12​	AX016	0.0125​	1​	1​	0​	1​	-​
26​	13​	AX025	0.025​	1​	1​	0​	1​	-​
27​	14​	AX032	0.0125​	1​	1​	0​	1​	-​
28​	15​	AX033	0.1875​	1​	1​	0​	1​	-​
29​	16​	AX042	0.2625​	1​	1​	0​	-​	1​
30​	17​	AX081	0.0125​	1​	1​	0​	1​	-​
31​	18​	AX102	0.05​	1​	1​	0​	1​	-​
32​	19​	AX202	0.0125​	1​	1​	0​	1​	-​
33​	20​	AX833	0.0125​	1​	1​	0​	1​	-​
34​	21​	D0017	0.2875​	1​	1​	0​	-​	1​
35​	22​	K0120	0.0875​	1​	1​	0​	1​	-​
36​	23​	MP804	0.053758824​	1​	1​	0​	-​	1​
37​	24​	OG101	0.053741176​	1​	1​	0​	-​	1​
38​	25​	S0601	1.2125​	1​	0​	0​	-​	-​
39​	26​	S19	0.0125​	1​	1​	0​	1​	-​
40​	27​	W3628	0.025​	1​	1​	0​	-​	1​

Note that item 25 isn't used, because it's > 1 -- the capacity of the car.