# Microsoft Office Application Help - Excel Help forum > Excel Programming / VBA / Macros >  >  Optimizing a cutting list for least waste

## Mrnuddles

Imagine a whole box of lengths of aluminium sections for making windows.

To make those windows I need to cut these lengths into smaller sizes.

I need to do this economically.

How can I use Excel to help me make this a reality.

Firstly you would take a big size out of it and then another size out of the offcut piece.

And then another size out of the offcut left over.

I need to know how many full stock lengths (6500) long it will take to cut all the pieces listed above.

Is it possible?

You need to take into account the quantity of each size and fit it all in together for the most optimal outcome.

See attached example of possible layout.

----------


## JBeaucaire

How did the teacher suggest you approach the problem?  What functions are you supposed to include?  Or is this a free form exercise?

----------


## royUK

Nice picture, but a spreadsheet would be better!

----------


## MarvinP

Hi Mrnuddles and welcome to the forum,

I've been noodling on your problem a while and I really like it.  I've written some code to solve it.  
See the attached spreadsheet.  Click the button and watch it guess your answer 20 times.  It will copy to the range on the right the best guess.

I took a math class on "How to Solve It" http://en.wikipedia.org/wiki/How_to_Solve_It which said you need to make a guess for an answer as a start.  If your guess is wrong then you make another guess, until/if you can come up with a formula.  

The attached never gets to the formula part but simply makes guesses and keeps track of how many pieces of stock are needed with random cutting of slices.  If you ever come up with a number of 49 or 48 I'd stop and use that as my answer.  

My formula might be called answer by exhaustion but computers don't get tired, do they?

I got a 49 peices after about 4 tries!!!  :EEK!:   :Smilie:   :Cool:   :Cool:  

hope this helps

----------


## Mrnuddles

> Hi Mrnuddles and welcome to the forum,
> 
> I've been noodling on your problem a while and I really like it.  I've written some code to solve it.  
> See the attached spreadsheet.  Click the button and watch it guess your answer 20 times.  It will copy to the range on the right the best guess.
> 
> I took a math class on "How to Solve It" http://en.wikipedia.org/wiki/How_to_Solve_It which said you need to make a guess for an answer as a start.  If your guess is wrong then you make another guess, until/if you can come up with a formula.  
> 
> The attached never gets to the formula part but simply makes guesses and keeps track of how many pieces of stock are needed with random cutting of slices.  If you ever come up with a number of 49 or 48 I'd stop and use that as my answer.  
> 
> ...



Hi there,
Wow I am impressed.
It works well, I have a downloaded but ugly shareware optimizing program which I cannot integrate into my spreadsheet that gets 51 lengths as a best result, so well done.
I really like how this runs, my only question is-
How do I see what calculations are taking place so I can recreate this within my existing spreadsheet?
I am assuming you used the Solver add in?
Can I see a screenshot maybe of what you input into it?
I have been using Excel for many years and I have some huge workbooks (60 sheets) that I use for estimating in my line of work but, I am new to using these types of complicated equations, my knowledge stops at What-If-Statements.
Thanks a lot.
Regards

Mrnuddles

----------


## Mrnuddles

Hi, this is not a teaching thing at all, I am needing this problem solved for my work to help me create a larger workbook for optimizing cutting lists.
Regards

Mrnuddles

----------


## Mrnuddles

OK, so sorry to say, but I have hit a snag.
The spreadsheet crashes if you increase the quantities of each item by 100 or so.
I did drag down the columns G and R to help calculate the larger numbers.
Help.

----------


## shg

There's a simple formula-based cut list at http://www.box.net/shared/uhrjy318l1

----------


## MarvinP

Yes - I was afraid you would want a generalized solution.  The workbook and code I gave you was just a quick example of how I'd do that specific problem.  I need to look at the code and my absolute ranges and numbers to make it more generalized.

I did not use the Solver in any way!

I simply started chopping your 6500 mm stock by random lenghts that you had noted.  When I cut one I'd subtract one of that lenght from the list.  I'd put that length in a Stock# row and Cut# Column based on if it had hit the 6500 number yet.  

I did this problem more for a proof of concept than a generalized solution.  To make it more generalized, I'd need to know what stock length(s) you have and how many different lenghts are needed by a customer (maximum for all customers, I guess)

The best answer (the one using the least number of Stock Lengths) is copied to Column I for your view.  It stays there until another try gives a less stock length number.  

If you can supply a more generalized sheet that had the above, I'd look at it.

It is interesting that another program gave 51 for the best answer.  I wonder what shg's linked program would give?

----------


## shg

> I wonder what shg's linked program would give



I just looked at that -- it's bad for these lengths, because they are large compared to the stock length.

----------


## Alf

This is a solver example of a cutting list. Perhaps you can modify it to fit your needs?

Alf

----------


## Alf

With time on my hands I set up a solver solution with cut length as stated by the OP.

Don't know if I found all possible combinations for cutting but managed to find 63 possible ways.

The optimal solution of 47 lengths of aluminum stock is probably a theoretical solution because in real life one must probably take in account the "thickness" of each cut. 

For example cut 55 produces 2 length of 1750 mm and 2 lengths of 1500 mm this all adds up to 6500 mm. But what if cut "thickness" is 0,5 mm? That is a "length" loss of 1,5 mm.

Can this be ignored or should the cut length of the different pieces be modified?

Alf

----------


## vandango05

I am also needing a speadsheet in cutting aluminium door profiles for multiple doors of different sizes.  The first spreadsheet looks very promising although I get an error when running the macro.

I get runtime error 438

When I press debug the following line of code is highlighted 

extCutRow = WorksheetFunction.RandBetween(2, 9)

I wonder if you came up with a solution that you would be able to share with me, would be greatly appreciated.

Thanks

----------


## MarvinP

I believe you need to have the Analysis Tool Kit add-in for Excel 2003 to perform the RandBetween.  The function was included in 2007 and 2010 as standard.

----------


## vandango05

Thank you for the quick reply.  The addin is activated although still the same error, i've tried on another computer with Excel 2007 and it works fine though.

----------


## MarvinP

Try to just put



```
Please Login or Register  to view this content.
```


in any cell as a formula and see if the old version knows what to do with it.  
That would simplify and solve the mystery.

----------


## SarahMascara

This is exactly what I was looking for online! I'm not nearly as advanced at Excel as most of you, but I was hoping to be able to figure out a way to do the exact same thing for handle lengths using metal 78" long.
I'm just not sure how to alter that spreadsheet to work for me. I have 151 pieces needed and we're trying to figure out how to optimize it as well as know which pieces get cut from the same length.

----------


## vandango05

Hi Sarah.

If you want to Private message me your email address I can send you a sheet I have made with some instructions.

----------


## arlu1201

vandango05,

You can upload the file here. 

To Attach a File:

   1. Click on Go Advanced
   2. In the frame Attach Files you will see the button Manage Attachments
   3. Click the button.
   4. A new window will open titled Manage Attachments - Excel Forum.
   5. Click the Browse... button to locate your file for uploading.
   6. This will open a new window File Upload.
   7. Once you have located the file to upload click the Open button. This window will close.
   8. You are now back in the Manage Attachments - Excel Forum window.
   9. Click the Upload button and wait until the file has uploaded.
  10. Close the window and then click Submit.

----------


## Alf

@ SahraMascara: Hi

Since this thread contains several suggested solution you must tell which solution you think should work for you. I would also suggest you upload a file describing stock length and the size and number of the pieces you need.

Alf

----------


## SarahMascara

Basically the one that MarvinP posted for Mrnuddles was almost perfect, except that it didn't give me enough spaces for all the different sizes I have for my lengths in the top section, and for some reason the cuts listed do not keep it within my 78" length limit, which is what the metal we're ordering for our handles comes in.

----------


## fahim.mohmd

Dear Alf,

This seems super efficient. Could you please tell me how you found the 63 possible combinations?

Thanks,
Fahim.





> With time on my hands I set up a solver solution with cut length as stated by the OP.
> 
> Don't know if I found all possible combinations for cutting but managed to find 63 possible ways.
> 
> The optimal solution of 47 lengths of aluminum stock is probably a theoretical solution because in real life one must probably take in account the "thickness" of each cut. 
> 
> For example cut 55 produces 2 length of 1750 mm and 2 lengths of 1500 mm this all adds up to 6500 mm. But what if cut "thickness" is 0,5 mm? That is a "length" loss of 1,5 mm.
> 
> Can this be ignored or should the cut length of the different pieces be modified?
> ...

----------


## FDibbins

fahim, welcome to the forum  :Smilie: 

Perhaps you missed the thread immediately above yours?

Unfortunately _your post does not comply with Rule 2 of our Forum_ RULES. *Do not post a question in the thread of another member -- start your own thread.* 

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread. 

Old threads are often only monitored by the original participants.  New threads not only open you up to all possible participants again, they typically get faster response, too.

----------


## bugdroid

I am hoping this is posted in here correctly.  I am having a similar problem with teh calculations for cutting material.  I am planning out making some wooden patio furniture this spring. I want to lay out all the board and cut them for my project.  It would halp to know what needs to be cut from what to reduce waste as i will make it the furniture from cedar.  

I have attached what i could come up with so far. I want the spreadsheet to cut parts from the drop pieces. Or if there isnt enough to cut from the stock length boards.   The area highlighted in red is where io tried to use the IF statements and such.  I have tried the spreadheets posted on here. but I cant get them to work right for me. Any help would be very appreciated.

https://www.dropbox.com/s/3je6k8s07i...%20NEEDED.xlsx

----------

