Nesting lengths using Excel to determine optimal "multing"

I am required to nest certain lengths of items together to get the smallest physical length I can to keep length required as small as possible and eliminate waste. In the attached file you will see that items are catagorized by column F and then sorted shortest to longest using column N. As in the example of Rows 2 thru 6, I thought the best way for me to figure on the shortest length of beam required would be to take 3 times the longest post length (cell N60) and 3 times the shortest post length (cell N2) and add them together. (For reference, each BEAM will produce six POSTS in this particular situation). This returned a value of 26.01 ft which I rounded up to 26' 4" (all order lengths of beams need to be rounded up to the nearest 4" increment). I then add a determined amount of allowable scrap to the final length to determine what the optimal purchasing length would be. I "thought" this would work fine, however, when I started to nest the three longest with the three shortest posts and so forth, as I approached the middle of the pack, the total length of all six posts was starting to exceed the purchased length of the beams.

Is there a macro that could be writen where I could highlight a specific cell range, run the macro which would promt me for the number of posts per beam, the macro would then automatically group the rows together so as to keep the scrap value as low as possible? Then, maybe highlight every other grouping so as to make the grouping be more visible? I tried using the average, the mean, etc. but couldn't find anything that worked.

Any help would be greatly appreciated.

CheddarThief

Thanks but I already have a cut list. What I need is something that takes a range of numbers and groups them in a set amount of numbers, in this case six, then tells me what the minimum finished length of steel I need to create the largest six piece grouping. Does that make sense? Maybe I'm talking in circles.

If I were buying them from say 40'-0" stock lengths that would work great. However, in my industry (heavy steel fabrication for the bridge industry) we sometimes put in what are called "mill orders". This means we have enough weight to put an order in directly to the mill and cut out the middle man who typically buys material in the stock lengths we spoke about. At the mill, we can order materials in cut length intervals of 4". What I was hoping was there is a formulation that Excel could look at a series of the finished cut lengths I need, group them together so that I could achieve optimal ordering to keep my drop scrap to a minimum.

At first I tried taking the total Net Wt. of all the like pieces (W8x48) and dividing that number by the total number of pieces. This gives me the average net wt per piece. Then I divided that number by the wt/ft, in this case 48, and got the average length per piece. I then multiplied that number by the anticipated pieces I'd be getting per beam to calculate my beam length. However, when I tried to figure the physical nesting, this didn't work out since the length of the posts is not an even curve across the total number of pieces. I thought possibly the MEAN formula could help me but again, that didn't work.

As I said, if I were buying stock pieces, this whole exercise would be a moot point. I would just use a nesting program (which I already have) and find the optimal cutting settup to eliminate the most amount of drop waste. What I have is a situation where I get to control the ordering length to some degree to help in keeping the waste low. Maybe this is something beyond Excel's capability or even someone's program writing but I thought I'd just ask.

I've tried to find these programs you are referring to but all of them require that I put in the finished lengths that the pieces will be taken from rather than the program giving the best order length to me.

The Excel works perfect for the initial Nesting but what about the remaining Scrap

how can this be used ?????

I tried with Stock Length 12000 and found there are some Scrap 4500 balance as the Qty are completely nested

next time when i need to Nest for Other Part how can i use this 4500 balance material ??


is it possible to have one more cell where the user can input the balance scrap length and the VBA program use this scrap first and than start using the New stock length