Help with grouping values

[Scott M.]

I have 5 manufacturing lines. I have 10 products to run down the
lines but the product volume is not all the same. Some products can
take up a whole line and others can take up just a fraction of the
line. What I need to do is group the fractions into a whole line a
spit out what products to run down what line. Below is some sample
data I have in excel.

A 1.8
B 0.9
C 0.6
D 0.4
E 0.3
F 0.2
G 0.2
H 0.1
I 0.1
J 0.1

Looking at this data, the output I want is:

Line 1: A
Line 2: A
Line 3: B
Line 4: C, D
Line 5: E, F, G, H, I, J

The kicker is that the product mix can change from week to week. What
I am trying now is just a bunch of "If" statements and it is quickly
getting out of control. If anyone has a better solution, please help.

Thanks,
Scott M.

 

[Tom Ogilvy]

Sounds like a classic problem for the Solver Addin. Look in the samples or
examples directory for a sample workbook for solver. It should have
examples for this type of problem.