Excel macro required to calculate a cutting sequence

[rbeech23]

Hi – I have a conundrum, we have to cut 7500mm bars of steel down for orders that vary in size and quantity.
The latest example is:
Component size (mm) Component quantity
980 6
3180 24
2380 12
3780 6
The aim is to minimise the waste on each bar of steel. We can also incorporate stock sizes as follows:
Stock sizes
1380
1580
1780
1980
2180

The saw will take two bars of steel at a time, what I’d like is for an excel macro to work out a cutting sequence of the component sizes and quantities. If a combination has a lot of waste the algorithm can incorporate stock gate sizes. I’d like the waste to be under 100mm for each bar.
Preference is for one size to be cut as many times as possible which might mean loading and unloading bars often but that is okay due to set up time. A sample spreadsheet is attached. There wouldn’t be any more than 10 different component sizes at a time although six would probably be workable if the combinations are too vast.
Any help appreciated, thanks in advance, Rob

[Kaper]

Have you already checked similar problems on this forum, like: https://www.excelforum.com/excel-pro...th-orders.html

There are also available commercial solutions for such problems.

The one non-obvious part in your description is the "stock" part. Does it mean that if one can cut from one 7500 bar just one required piece and a combination of say 4 stock lengths giving total waste of 10mm it is acceptable way of cutting?


in other words the order could be treated as
980 exactly 6
3180 exactly 24
2380 exactly 12
3780 exactly 6
1380 >=0
1580 >=0
etc.

[rbeech23]

Hi Kaper
Thanks for responding. I've had a look at the thread you sent and sample files but they don't quite give me what I need. In answer to your question about stock, yes any quantity of stock can be applied, apologies should have made that clear.
That hopefully makes for more options for minimising waste. Any further help with this appreciated

Regards
Rob

[Kaper]

So I did a first step and reused and adopted macro from another thread on this forum: https://www.excelforum.com/excel-pro...-in-range.html to list all possible ways of cutting 7500 bar (with waste not larger than 100mm) into pieces of required lengths and allowed spare lengths.
Note that there is no way to cut 3780mm pieces. with 100 mm waste criterion.

The next step could be optimization (not done yet)
For instance probably first way of cutting shall not be used as it produces all needed 980mm pieces (and one stock piece) and all other usefull cuts like listed in row 6
980 980 980 3180 1380
which gives 3x980 + 1x3180 + 1 stock piece

But before I start with opitimization/selection which "paths" shall be used, I'd like to know what is ""Total bars in saw" - does it mean that there is always a pair of bars cut at the same time? Then first way of cutting is obviously wrong as we take 2 7500 bars and end up with 12 980 pieces.

And what we do if there is no allowed way to cut 3780 piece?

The code is:

Please Login or Register  to view this content.

See attached file (with macros enabled)