I'm working in Excel and I have 2 separate lists. On the first is final length of goods to be cut, and how many of a given length. And on the second is manufactured lengths of goods (the lengths we received them at). I'm trying to find a way to efficiently calculate what cuts to take from what manufactured goods to result in little or no waste, but all I can think of is a lot of guessing and checking. There has to be a faster way to process all of this?
Side note: I'm not good at math beyond highschool, only ok at excel, and dont know any programming... So laymans terms me, please.
Example:
Final Cuts (Inches): Qty: Mfg Length (Inches): Qty:
280 7 1804 7
49 3 1796 3
79 6 1776 1
174 11 1416 4
330 7
169 18
287 4
146 9
You can phrase this as a Linear Optimization Problem, and then use a LP program solver (many languages have packages to do so, I like cvxpy in python) to solve this. I'm pretty sure that a pure Excel solution doesn't exist.
The way I would phrase it as a LP problem is as follows: let $q_{ij}$ represent the quantity of pieces of type $i$ that are being made out of piece $j$. In the example you've presented, $i=1$ represents pieces of length $280$, $i=2$ of length $49$, etc, and $j=1\to 7$ represent pieces of length $1804$, $j=8\to 10$ pieces of length $1796$, etc.
Maximize $$q_{1,1}+q_{2,1}+\ldots+q_{8,1}+q_{1,2}+\ldots+q_{2,2}+\ldots+q_{8,2}+\ldots+q_{1,15}+\ldots+q_{8,15}$$
Under the constraints of the form:
$$280\cdot q_{1,1}+49\cdot q_{2,1}+79\cdot q_{3,1}+\ldots+146\cdot q_{8,1}\leq 1804$$ $$280\cdot q_{1,2}+49\cdot q_{2,2}+79\cdot q_{3,2}+\ldots+146\cdot q_{8,2}\leq 1804$$ $$\vdots$$ $$280\cdot q_{1,15}+49\cdot q_{2,15}+79\cdot q_{3,15}+\ldots+146\cdot q_{8,15}\leq 1416$$ $$q_{1,1}+q_{1,2}+\ldots+q_{1,15}\leq 7$$$$q_{2,1}+q_{2,2}+\ldots+q_{2,15}\leq 3$$$$\vdots$$$$q_{8,1}+q_{8,2}+\ldots+q_{8,15}\leq9$$