"Approach" to calculate needed quantities from standard length material

[Ruthanne]

I am stumped as to "how to approach" the calculation my co-worker has asked me about: "Do you know if there is way to make excel determine an efficient method to cut multiple lengths of beams from a quantity of standard lengths? Or have you heard of any other program or method to do this?
E.g. We are ordering 600 LF of beam, in 240" lengths, and need to cut these pieces out of it.

Qty*** Length
10***** 76
6****** 44
2****** 45
2****** 36
6****** 32
2****** 20
4****** 31
1****** 54
4****** 82
3****** 82
1****** 54
--------------------------
I jumped right to the "round up" formula but this won't take advantage of the excess material from other pieces/cuts. I don't think there is a "simple" approach or formula for this especially because the "multiple" quantities for each length.

We are trying to take advantage of the "leftover" pieces from the cuts: for example, only two each 82 inch pieces can be cut from the standard 240 inch length but the leftover piece will be 76 inches long which will provide one of the ten 76" pieces needed on the first line (or two of the 36" pieces). Ouch, my head hurts!

Any ideas on how to approach this...if even possible? Thank you all so much in advance and for the great help everyone is past, present & future!

 

[AnAnalyst]

Hi there.

Do you need to take into acount the effects of tooling? i.e. how much material is lost each time you make a cut?

 

[Ruthanne]

If it's of any help, I found a similar problem discussed in another forum (although not Excel):
Length cutting optimization in a nut-shell:
http://techboard.nemetschek.net/ubbthreads.php?ubb=showflat&Number=132500

The hands on approach I think is perfect (but how to translate to Excel is the question now!):
- get a list from somewhere
- sort it from longest to shortest
- get the first part length

- look in the scrap bin for the shortest piece that'll do the job
- cut the piece of scrap if you find one, else cut a piece of stock
- if a length of stock is used then up the tally of lengths required
- put the cut-off in the scrap bin
- get the next length needed from the cut-list
- repeat the above 5 steps until list is finished"

Thanks!

 

[Ruthanne]

Hi Mr. AnAnalyst,
I am so sorry that I missed your reply before my 2nd post (I guess my browser had not been refreshed!).
You are very wise to think of cutting wastage! I actually do not need to figure cutting wastage for this situation.
Thanks so much for your reply and help!

 

[AnAnalyst]

Hi there,

OK, this is easily doable in Excel, although it might take a little time!

The method you describe does not necessarily, however, provide the best optimization of stock length usage and may result more wastage than might be achieved by other methods - those other methods add to the complexity though, so I would be inclined to play safe and accept that you might not achieve the best optimization, rather an optimization that will be acceptable.

Is there a reason this has to be done in Excel?

There are commercial add-ins available that will give better results! Have you considered one of these?

There are a few freeware one dimensional cutting optimization programs in the wild. Might one of these provide an alternative for you?

If you want me to build this in Excel I can, but only on the understanding that it would strictly be a "spare" time thing, so i can't guarantee when it would be finished.

Let me know which direction you decide on.

 

[High Plains Grifter]

not as simple as it may seem to start with... take a look here http://www.mrexcel.com/pc04.shtml

I have been looking at this as well on and off for a while and the more you think about it the more obstacles there are. Whether there is some mathmatical solution to this problem or not, I don't know. It would be nice if this problem or type of problem had a standard name so they could be searched for on the internet...

 

[Herbert Seidenberg]

Excel 2007
Using the algorithm you found.
With macro and Tables.
http://c0718892.cdn.cloudfiles.rackspacecloud.com/06_23_10.xlsm
Pdf preview (2 pgs):
http://www.mediafire.com/file/zizwmzcwix0/06_23_10.pdf

 

[Ruthanne]

A huge, enormous thank you to the brilliant people on this forum. Many thanks for the help, effort and solution! I am blown away....just hearing the word algorithm makes my head spin!
Thank you so much to all of you! You're so smart!!!!!!!

 

[AnAnalyst]

What the heck - I couldn't sleep, so here's an alternative to Herbert's method:

Cut Optimizer.xls

Should you want to think about cut wastage, this sheet gives you that opportunity!

All the best

 

[Ruthanne]

Hi all,
I wanted to add that my co-worker checked out this download as an "Excel alternative" (free, I believe) and said it worked fine:
http://www.1d-solutions.com/home.htm?gclid=CMeCt5DhtaICFYYtpAodHFZU8Q
I think the Excel approach was preferred because it doesn't require a download which is forbidden by some/our company's computer policies so we can share & e-mail between our other departments.
Many sincere thanks again!