My company makes pieces of tubing from our standard stock lengths of 20ft, 40ft and 60ft. We get a daily order request of tubing pieces of different lengths (less than 20ft) and my job is to figure out what combination of individual pieces can be combined to make up a stock piece with minimal waste (we ideally want to use 40ft stock length tubing to cut up for orders as it is easy to handle.)

I have a numeric list that represent lengths of piece order (in mm). The number of pieces in the order changes all the time. I need to find the combination of numbers that comes closest to 12,192mm (40ft). Then once I find that, I need to discard those piece orders (as they have been used to make up a stock piece) and then I need to find the next combination, etc until all pieces in the order have been accounted for. If there is a great deal of waste (ie more than 15%) the combination is not ideal and I would want to try to fit pieces to 18,288mm (60ft) or 6,096mm (20ft)

For example the list below is an order, each row is a piece order request in mm [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

[tr]

[TD="width: 64, align: right"]2254[/TD]

[/tr]

[tr]

[TD="align: right"]3694[/TD]

[/tr]

[tr]

[TD="align: right"]2254[/TD]

[/tr]

[tr]

[TD="align: right"]3131[/TD]

[/tr]

[tr]

[TD="align: right"]3131[/TD]

[/tr]

[tr]

[TD="align: right"]2254[/TD]

[/tr]

[tr]

[TD="align: right"]2253[/TD]

[/tr]

[tr]

[TD="align: right"]3225[/TD]

[/tr]

[tr]

[TD="align: right"]2254[/TD]

[/tr]

[tr]

[TD="align: right"]3225[/TD]

[/tr]

[tr]

[TD="align: right"]4402[/TD]

[/tr]

[tr]

[TD="align: right"]1174[/TD]

[/tr]

[tr]

[TD="align: right"]1174[/TD]

[/tr]

[tr]

[TD="align: right"]1174[/TD]

[/tr]

[tr]

[TD="align: right"]1174[/TD]

[/tr]

[tr]

[TD="align: right"]1174[/TD]

[/tr]

[tr]

[TD="align: right"]1174[/TD]

[/tr]

[tr]

[TD="align: right"]3225[/TD]

[/tr]

[tr]

[TD="align: right"]2254[/TD]

[/tr]

[tr]

[TD="align: right"]3225[/TD]

[/tr]

[tr]

[TD="align: right"]4402[/TD]

[/tr]

[tr]

[TD="align: right"]4402[/TD]

[/tr]

[/TABLE]

I could get these broken out into the following groupings: [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

[tr]

[TD="width: 76"]ACTUAL LENGTH[/TD]

[TD="width: 329"]Combinations used from list above[/TD]

[/tr]

[tr]

[TD="align: right"]11333 > [/TD]

[td]

2254, 3694, 2254,3131

[/td]

[/tr]

[tr]

[TD="align: right"]11552 > [/TD]

[td]

2254,2254,1174,1174,1174,1174,1174,1174

[/td]

[/tr]

[tr]

[TD="align: right"]11835 > [/TD]

[td]

3225,3131,2254

[/td]

[/tr]

[tr]

[TD="align: right"]11057 > [/TD]

[td]

4402,4402,2253

[/td]

[/tr]

[tr]

[TD="align: right"]10852 > [/TD]

[td]

3225,3225,4402

[/td]

[/tr]

[/TABLE]

I tried using the SOLVER add-in but couldn't get it to work so wondering if there is another way or in vb script to find what I need it to do?