Optimization of two pieces to make 1 large piece question

[Zstoppa]

Hello, for work I am trying to generate a sheet that will auto populate the best possible combination of panels for us to use.

Essentially, our problem is: we have a panel that needs to have an overall length of 144" L by 48" Tall. To make this panel we have rolls of material that are 48" or 36" wide, and up to 20' in length. The depth part isn't a problem its the width of the material. We have to combine the panels together, again in a combination of 48" and 36" to have the least amount of waste. For example, in this example 3 48" panels would work best. But I wan't a sheet that I put in the overall length we need and it breakdown the most cost effective combination of 48" panels and 36" panels.

Anyone got some advise?

[AliGW]

First of all, please would you edit your thread title (edit the opening post and click Go Advanced) so that it tells us a little more explicitly what you are trying to do. See the forum rules on this. Thanks. Oh, and welcome to the forum!

[MrShorty]

Obviously, this is some kind of cutting stock problem. I did not understand why you would use 3 4x4 pieces instead of 1 4x12 piece, but you should be able to program whatever is controlling that decision into the final spreadsheet -- as long as you understand why you made that decision. There is a lot of discussion around the internet and other computer science circles about this common problem. As simple as the problem seems on the surface, it is an NP-hard problem (https://en.wikipedia.org/wiki/Cutting_stock_problem ), which means that it is surprisingly difficult to program a robust solution (especially if you need to expand to 2D or 3D).

Most first suggestions for these problems use Excel's Solver. Solver's programmers have provided a sample spreadsheet with several different kinds of common optimization problems, including an example for the cutting stock problem: https://www.solver.com/examples-optimization-problems
This guy also has an example using Solver: http://blog.excelmasterseries.com/20...aste-with.html

If you don't like the Solver solutions, here's one that uses VBA: http://dailydoseofexcel.com/archives...stock-lengths/

I recommend you study those (and any other examples you find) until you understand how the cutting stock problem is formulated into the spreadsheet. Then, make your adjustments to those algorithms until you get the specific cutting stock problem that you have.

[Alf]

Being bored I set up a small solver model for this problem. Info in uploaded file about the how and the why.

Alf