Need to reduce waste/scrape

Hello,

I have been tasked to try and reduce the amount of waste/scrap of unistrut in sales orders. We order two specific lengths that we cut down based on the customers orders 10' and 20'. Below is an example of an order that I am trying to solve. Any help would be phenomenal.

Size and qty

16' 2
20' 2
13' 1
20' 3
8' 8
5' 6
7' 1
20' 1
10' 1
4' 2
2' 3

Thank you

I have attached a mock spreadsheet that you can turn into your own.
Column C Formula: Takes the size x quantity.
I then sum these amounts up at bottom of Column C

Order summary Takes the total of column C and Disperses the amount over 20' and 10' orders. Assuming 20' are cheaper based on buying in bulk is usually cheaper.
Round Down function was used for 20'
Round Up function used for the difference in total 20' less the total of column C.

Thank you for taking the time to look into this first of all. Does this model take into account using smaller already cut pieces of scrap to fill the shorter orders?

it pretty much is just taking the total sum of fabric you need and ordering just the right amount. The example you gave worked out perfectly.

There is a total of 290' that needs to be ordered. So you order 14 pieces of 20' and 1 piece of 10'
14x20=280
1*10=10
total = 290


Lets say your total order needed to be 291 instead.
that would be:
14 pieces of 20'
2 pieces of 10'
This would be a total of 300' which would mean you would have an extra 9' of scrap from the order. (300-291=9)

You could keep track of the extra.
the formula would be:
=+(F3*G3)+(F4*G4)-C13