I have some CAT6 cable that comes in 500' or 1000' spools.
Hi Travis. Did you ever find a solution to your cable length problem?
Search the Excel groups here for using Solver. What you are looking for is
the setting of constraints to either "Integer" or "Binary," and the use of
Sumproduct.
Here are some observations on your problem:
Your data is re-listed below.
One problem with cutting stock problems is that one technique usually
requires "another program" to generate all feasible cut patterns.
I slightly modified one I already had, and it generated 76 possible
patterns.
This is usually the hard part, and there are all kinds of techniques. There
is even an advanced technique for generating patterns that looks at Solver's
solution report, adds a pattern from the report, and loop again until a
certain condition in the answer report is meet. This generates a smaller
list of possible patterns. It works pretty well in Solver. However...
For example, my pattern #1 was to cut (250,250,300) with waste of 200.
# 46 (300,310,380) [10 waste]
# 34 (250,340,410) [0 waste]
#35 (250,375,375) [0 waste]
#54 (310,310,380) [0 waste]
# 68 (250, 250) [0 waste from 500' length]
Now, you use Solver to pick the number of each pattern to use that meets
your constraints.
Another problem you have, that may not be apparent, is that you have
multiple solutions.
You can have solutions that use 500' lengths, and some solutions that do not
use 500' lengths.
This brings up another problem.
There is no cost mentioned for either 500 or 1000'.
Sometimes it is cheaper to buy 1000 lengths, vs 500 lengths. (bulk
purchases)
On the other hand, it may be more expensive to buy 1000 lengths. (more
expensive to make, more expensive to ship/store, may need a bigger
truck, .etc)
(a 100 inch plasma tv costs more than two 50-inch plasma tv concept)
We first note that the total length you need is 9,076.
One step would be to see if you can re-wire your project down by 76' to
9,000.
We see that the best case is to buy 10 items.(9-1000, 1-500)
When we look at the rest of the required cut lengths, we get a feel that we
will need at best 10 items, with about 700-1000 feet of waste.
Here are 3 runs to show different situations.
All have total waste of 924.
#1. (No 500 ft)
(250,375,375) (2 of these)
(300,310,375)
(300,310,380)
(300,332,332)
(300,332,340)
(375,410) (3 of these)
(405,405)
#2 (Use a 500 ft length, again with 924 waste)
(250,375,375) ( 2 of these)
(300,300,380)
300,332,332)
(300,332,340)
(310,310,375)
(375,405) (2 of these)
375,410)
(410) (2 of these from 500 length)
#3. This next one is an interesting technique that gives the same waste as
those above.
In the above 2 examples, the remaining pieces are relatively small, and not
useful.
When we know we have waste, sometimes it may be better to "maximize" the
size of a wasted piece. This allows us to have an extra piece available
should the need arise.
Why not, since the waste is the same anyway.
But, it all depends on the cost ratio of 500 vs 1000.
Here, we use 2-500 ft blocks. Out cut pattern below leaves us with 1 extra
250 ft piece which we could use in this project, another project, or sell.
(250,340,410)
250,375,375) (2 of these)
(300,300,375)
(300,310,375)
(300,310,380)
(332,332,332)
(375,410) (2 of these)
(405) (2 of these from 500')
In addition, it may be better to pay "a little more" and have an extra 410
piece available that can be further cut to any of the other sizes.
Here is your date requirements re-written and sorted:
{250,250,
300,300,300,300,
310,310,
332,332,332,
340,
375,375,375,375,375,375,375,375,
380,
405,405,
410,410,410}
Anyway, I hope I copied everything correctly, and that this gives some
"feel" to the problem.
As far as the problem of maximizing file space for storage, I don't think
Solver is the way to go.
The number of files is usually too large for Solver. The best routine is
probably to do the best you can, and then use smaller files to "fill in" any
remaining disk space.
--
HTH :>)
Dana DeLouis
Windows XP & Office 2007
<<snip>>
