Roll Stock Optimization

hmmm.....i think something is missing in your explanation
if you want to limit the number of reel widths and waste is not the problem

can you not just say set one or two widths and everyone needs to conform to this width?

it would seem you have some that is an open ended variable otherwise?

I am curious why you think that the cutting stock problem (remember that this is just a variation on the knapsack problem) is not applicable to your problem. Sure, your problem has a minor detail (optimize reel width) that is not present in the typical example you will encounter on the internet or in text books, but the overall concepts and procedures still seem the same. I think if I were solving a problem like this, I would start from a generic cutting stock problem program, and expand it to somehow include reel width in the objective function/constraint function/decision variables.

Without reel width, your problem looks like a standard 2D cutting stock problem, am I seeing it right? If so, adding reel width may, in effect, make it a 3D cutting stock problem. Often time, even though expanding something from 2D to 3D is conceptually straightforward, adding a dimension dramatically increases the computational effort needed to find the solution, and makes it more likely that something like Solver will give up before finding the solution. I might suggest some research into how different cutting stock problems are handled -- especially as the number of "dimensions" increases. I note that Wikipedia's cutting stock problem entry (https://en.wikipedia.org/wiki/Cutting_stock_problem ) mentions 3D cutting stock problems, gives no details, but points the reader to the 3D packing problem (remember that cutting stock is essentially the same as the knapsack problem). Perhaps research in that direction will help you see how to expand your current cutting stock problem into something that can incorporate reel width.

As one who is aware, but never had to solve, cutting stock problems, I cannot really make any specific suggestions about the generic task of solving a cutting stock (maybe 3D cutting stock) problem.

Without knowing specifically what you need help with, I would probably proceed like this (assuming I had a working spreadsheet for the 2D cutting stock problem at a fixed reel width):

1) I might try simply adding reel width to Solver's "by changing cells". If I get lucky, then it will find a good solution with only that change. If I am unlucky, I am only out the time waiting for Solver to run (have a max time of 600 seconds or something so that it will not be more than that).

2) A lot of these NP hard type problems really require trying every possible combination anyway before knowing that you have the solution. I might do like Alf suggests and use my 2D cutting problem spreadsheet to solve multiple copies of the problem at different reel widths. Compile the results and see which reel width(s) provide the least waste overall.

3) My time is valuable. If this is going to take a lot of effort to program, search for 3rd party preprogrammed solutions for this kind of 3D cutting stock problem (whether it uses Excel or not). Even if it costs a weeks wages to buy a software license, I could easily come out ahead.