42 thoughts on “Cutting Stock Lengths”

1. Dick, this could be very useful, but what about the amount of wood you lose with each cut? Needs to have a place to put how wide the saw blade is to account for this in each cut you make on a board. As you said it would be better to have the results put on a sheet and maybe even the “waste” that is left in each board, just a thought

2. The width of a saw blade? What are you making, Scandinavian furniture? It might be good to have a generic waste factor in there. The user could enter either a percentage or a hard measurement and that could be added to each cut, or rather subtracted from the remaining stock after each cut.
   I thought about tracking the waste, but never got around to doing it.

3. No am not making Scandinavian furniture but with a 1/8? saw blade and if you have 36? stock and want three 12? boards you would end up with two 12? and one 11 3/4?. If you are making anything, except maybe “rough” framing a house that is a big deal. And of course the longer the stock and the more cuts you make or a wider saw blade, this becomes more of a problem.

4. Measure once, cut twice, swear three times…

5. Back in my University days our Operational Research Mathematics class (Dr Lynn was our lecturer if I remember correctly) was set the task of using linear programming and/or trim loss theory to work out how to minimise waste for a carpet manufacturer.

   Obviously they sold rolls of carpet in various lengths and widths but cut from one roll.

   I did come up with a neat solution in Algol but have never had the need to use it again (or translate it into any other language).

   Professor George B. Dantzig invented the the “simplex method” and “linear programming” whilst at Stanford, so if you need more information look it up on the web!

   Alternatively, email me and I might be able to find the Algol program!!!

6. Hi Simon. I would be interested in your Algol solution. I happen to like OR when I was in school and am curious to see your solution. I hope you can find it and share it. Thanks for your time.

   CA

7. Hello, first time here. Dick Kusleika – I work in the steel fabrication industry and could use your stock cutting VBA to some degree. What I would like to know is would it take much to have it tell me which pieces of which length to cut from each stock length. i.e., 2 pieces 18′ 6? long to cut from a 40′ 0? stock length piece. I would be dealing with hundreds of pieces of varying lengths from 1′ 0? to 58′ 0? being cut from 20′ 0?, 40′ 0? and 60′ 0? (various quantities of each) stock lengths as well as several random stock lengths left over from previous jobs.

   Thank you, Ed

8. Hello, first time here. Dick Kusleika – I work in framing industry and am very happy to find your cutting stock length program. Would you consider adding a few modifications to better fit my needs? I would be grateful and more than happy to pay you for your time.

9. good day. i cant identify which cells or group of cell you renamed. like wshcuts, rinpstok, rinpcuts. please advice.

10. Looks like we all like this example and all of us want something more.

    :)

    Thanx anyway for this….

11. Very Useful. I’m curious if it would be easy to insert a 3rd row in addition to quantities and sizes that shows where the specific cut is to be installed. I.E. the first cut would have 4 cuts for window 1 and 3 for window 2…

12. Hello Dick Kusleika,

    I am 16 year and as student that love VBA i follow all your steps… and see you as my master.
    I am for years very interesting in the Cutting Stock problem and find yours one of the best i ever saw!
    But i could never figure out how to make it working by more stock with different lengths.
    If one day you got little time… can you please show it?

    Thank you to read this and for your hard work!

    Christina

13. Hi there,
    I was wondering if you have a functioning copy of this you could send through, the zip download is no longer working. I would love to see the construction of the excel file.
    Cheers.

14. @Johnathan

    The link is fixed. Thanks for letting me know.

15. That takes me back many years to a time when I was working with a mainframe Linear Programming system (MPSX) with mixed inter options (solving problems for Roger Govier amongst others!). The optimisation of cut stock can be formulated as an Integer programming problem.
    See https://en.wikipedia.org/wiki/Cutting_stock_problem
    You could probably find the optimum solution using Solver

    There seems to be at least one Excel addin that targets the problem:
    http://www.optimalon.com/length_cutting_excel.htm

16. Hola, segun veo este metodo no funciona para unos cortes de 1 de 4000,1 de 3000, 2 de 1500 y 2 de 1000 . largo de barra 6000.

    Si tienes la solucion a tal fin agradeceria que me la pases.

17. Funciono para mi ¿Qué error estás viendo? (It worked for me. What error are you seeing?)

    

18. A esos cortes los puedes sacar en 2 barras.
    4000,1000,1000= 6000
    3000,1500,1500= 6000

19. Sí, ya veo. El algoritmo de optimización no es muy bueno. Siempre lleva el corte más grande que se ajuste.

20. I made a new macro that solves this particular problem. But there are a few caveats. It’s not very well tested, so don’t use it in production. I didn’t do a lot of the error checking that it’s in the original. And it tests every possible combination so it will be really slow if you have more than a few cuts.

    Hice una nueva macro que resuelve este problema en particular. Pero hay algunas advertencias. No está muy bien probado, así que no lo uses en producción. No cometí muchos errores al comprobar que está en el original. Y prueba todas las combinaciones posibles, por lo que será muy lento si tiene más de unos pocos cortes.

    BoardFeetBestFit.zip

21. La verdad que te pasaste Dick Kusleika , esta muy bien…. Te consulto es posible hacer algo que en verdad tome muchos cortes y optimicen en un stock con medidas dinámicas? He estado buscando mucho este tema y parece que es algo muy complejo de realizar,por lo que me gustaría se es posible que me des una mano.
    Desde ya muchísimas gracias y un abrazo enorme.

22. He visto un metodo muy bueno hecho en Python pero no lo puedo pasar a visual por lo que si tu sabes te lo agradeceria.
    http://ibmdecisionoptimization.github.io/docplex-doc/mp/cutstock.html

23. Hi Dick,
    This is a shot in the dark but seeing that this thread has had so much success I thought you might have some advice for me with a wood board I’m trying to cut. I know that excel must be able to do this but I have no idea how.

    I have a number of wood boards that I want to cut into a lot of different sizes that I need for my project. I have a list of the exact sizes I need to cut but want to cut them out as efficiently as possible so as not to waste any board.

    Any tips on how to get this done?

    Thanks so much in advance,
    Lulu

24. You can try the BoardFeetBestFit file I posted a few comments ago, but be sure to read the caveats. When the code test every possible combination, it gets really slow.

25. How would I flip the column A to B and B to A? My length has to be in Column A and qty of that length is is Column B.

    Thanks

26. Everywhere there’s a CutArr, you have to change the 0 to 1 and 1 to 0. So CutArr(i,0) would have to be CutArr(i,1), for example.

    Visual Basic

    Sub ComputeStockRev() Dim CutArr() As Double, DetStk() As Double Dim lRowCount As Long Dim i As Long, j As Long, k As Long Dim temp As Double, temp2 As Double Dim TotStk As Double, TmpStk As Double Dim MinCut As Double, TotCut As Double Dim dStk As Double Dim rInpStk As Range Dim rInputCuts As Range Dim rLastEntry As Range Dim AllZero As Boolean Dim sMsg As String, sTtl As String Dim cell As Range Set rLastEntry = wshCuts1.Range("A" & wshCuts.Rows.Count).End(xlUp) Set rInpStk = wshCuts1.Range("InpStock") 'Make sure cuts have been entered If rLastEntry.Address = "$A$1" Then Exit Sub Else Set rInputCuts = wshCuts1.Range("A2", rLastEntry.Address).Resize(, 2) lRowCount = rInputCuts.Rows.Count End If 'Check for non-numeric data and negative numbers For Each cell In rInputCuts.Cells If Not IsNumeric(cell.Value) Then MsgBox "Your selected range contains non-numeric data" Exit Sub End If If cell.Value < 0 Then MsgBox "All values must be positive" Exit Sub End If Next cell 'Make sure stock lenght was entered If IsEmpty(rInpStk.Value) Or Not IsNumeric(rInpStk.Value) Or rInpStk.Value <= 0 Then MsgBox "Stock length must be a positive number" Exit Sub Else dStk = rInpStk.Value End If ReDim CutArr(lRowCount - 1, 1) 'Fill array with cuts For i = 0 To UBound(CutArr, 1) For j = 0 To UBound(CutArr, 2) CutArr(i, j) = rInputCuts.Cells(i + 1, j + 1) Next j Next i 'Sort array descending on cut length For i = 0 To UBound(CutArr, 1) - 1 For j = i + 1 To UBound(CutArr, 1) If CutArr(i, 0) < CutArr(j, 0) Then temp = CutArr(j, 0) temp2 = CutArr(j, 1) CutArr(j, 0) = CutArr(i, 0) CutArr(j, 1) = CutArr(i, 1) CutArr(i, 0) = temp CutArr(i, 1) = temp2 End If Next j Next i 'Make sure all cuts can be made with stock lenght If CutArr(0, 0) > dStk Then MsgBox "At least one cut is greater than the stock length." Exit Sub End If 'Initialize variables MinCut = CutArr(UBound(CutArr), 0) TmpStk = dStk TotCut = 1 'set > 0 to start loop, TotCut is 'recalced within loop i = 0 k = 0 'TotCut is sum of first dimensions in array Do While TotCut > 0 'MinCut is smallest 2nd dimension where 1st 'dimension is > 0 Do While TmpStk >= MinCut If CutArr(i, 0) <= TmpStk And CutArr(i, 1) > 0 Then 'Reduce current stock length by cut length TmpStk = TmpStk - CutArr(i, 0) 'Reduce number of current cut by 1 CutArr(i, 1) = CutArr(i, 1) - 1 'Store current cut length ReDim Preserve DetStk(1, k) DetStk(0, k) = TotStk + 1 DetStk(1, k) = CutArr(i, 0) k = k + 1 Else 'Move to next cut length i = i + 1 End If 'Reset MinCut AllZero = True For j = LBound(CutArr) To UBound(CutArr) If CutArr(j, 1) > 0 Then MinCut = CutArr(j, 0) AllZero = False End If Next j 'If there are no cut pieces remaining, get out If AllZero Then Exit Do End If Loop 'Reset TmpStk and add one to TotStk TmpStk = dStk TotStk = TotStk + 1 'Reset i to row of largest 2nd dimension whose '1st dimension is not zero For j = UBound(CutArr) To LBound(CutArr) Step -1 If CutArr(j, 1) <> 0 Then i = j End If Next j 'Reset TotCut to sum of all 1st 'dimensions TotCut = 0 For j = LBound(CutArr) To UBound(CutArr) TotCut = TotCut + CutArr(j, 1) Next j Loop 'Output totals to a message box sTtl = "Total stock at " & dStk & " = " & TotStk sMsg = "Board No." & vbTab & "Cut Lenght" & vbCrLf For k = LBound(DetStk, 2) To UBound(DetStk, 2) sMsg = sMsg & DetStk(0, k) & vbTab & vbTab _ & DetStk(1, k) & vbCrLf Next k MsgBox sMsg, vbOKOnly, sTtl End Sub

    1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159

    Sub ComputeStockRev()       Dim CutArr() As Double, DetStk() As Double          Dim lRowCount As Long     Dim i As Long, j As Long, k As Long          Dim temp As Double, temp2 As Double          Dim TotStk As Double, TmpStk As Double     Dim MinCut As Double, TotCut As Double     Dim dStk As Double          Dim rInpStk As Range     Dim rInputCuts As Range     Dim rLastEntry As Range     Dim AllZero As Boolean     Dim sMsg As String, sTtl As String     Dim cell As Range          Set rLastEntry = wshCuts1.Range("A" & wshCuts.Rows.Count).End(xlUp)     Set rInpStk = wshCuts1.Range("InpStock")          'Make sure cuts have been entered     If rLastEntry.Address = "$A$1" Then         Exit Sub     Else         Set rInputCuts = wshCuts1.Range("A2", rLastEntry.Address).Resize(, 2)         lRowCount = rInputCuts.Rows.Count     End If          'Check for non-numeric data and negative numbers     For Each cell In rInputCuts.Cells         If Not IsNumeric(cell.Value) Then             MsgBox "Your selected range contains non-numeric data"             Exit Sub         End If         If cell.Value < 0 Then             MsgBox "All values must be positive"             Exit Sub         End If     Next cell          'Make sure stock lenght was entered     If IsEmpty(rInpStk.Value) Or Not IsNumeric(rInpStk.Value) Or rInpStk.Value <= 0 Then         MsgBox "Stock length must be a positive number"         Exit Sub     Else         dStk = rInpStk.Value     End If          ReDim CutArr(lRowCount - 1, 1)          'Fill array with cuts     For i = 0 To UBound(CutArr, 1)         For j = 0 To UBound(CutArr, 2)             CutArr(i, j) = rInputCuts.Cells(i + 1, j + 1)         Next j     Next i          'Sort array descending on cut length     For i = 0 To UBound(CutArr, 1) - 1         For j = i + 1 To UBound(CutArr, 1)             If CutArr(i, 0) < CutArr(j, 0) Then                 temp = CutArr(j, 0)                 temp2 = CutArr(j, 1)                 CutArr(j, 0) = CutArr(i, 0)                 CutArr(j, 1) = CutArr(i, 1)                 CutArr(i, 0) = temp                 CutArr(i, 1) = temp2             End If         Next j     Next i          'Make sure all cuts can be made with stock lenght     If CutArr(0, 0) > dStk Then         MsgBox "At least one cut is greater than the stock length."         Exit Sub     End If          'Initialize variables     MinCut = CutArr(UBound(CutArr), 0)     TmpStk = dStk     TotCut = 1  'set > 0 to start loop, TotCut is                 'recalced within loop     i = 0     k = 0          'TotCut is sum of first dimensions in array     Do While TotCut > 0              'MinCut is smallest 2nd dimension where 1st         'dimension is > 0         Do While TmpStk >= MinCut             If CutArr(i, 0) <= TmpStk And CutArr(i, 1) > 0 Then                                  'Reduce current stock length by cut length                 TmpStk = TmpStk - CutArr(i, 0)                                  'Reduce number of current cut by 1                 CutArr(i, 1) = CutArr(i, 1) - 1                                  'Store current cut length                 ReDim Preserve DetStk(1, k)                 DetStk(0, k) = TotStk + 1                 DetStk(1, k) = CutArr(i, 0)                 k = k + 1             Else                 'Move to next cut length                 i = i + 1             End If                          'Reset MinCut             AllZero = True             For j = LBound(CutArr) To UBound(CutArr)                 If CutArr(j, 1) > 0 Then                     MinCut = CutArr(j, 0)                     AllZero = False                 End If             Next j             'If there are no cut pieces remaining, get out             If AllZero Then                 Exit Do             End If         Loop                  'Reset TmpStk and add one to TotStk         TmpStk = dStk         TotStk = TotStk + 1                  'Reset i to row of largest 2nd dimension whose         '1st dimension is not zero         For j = UBound(CutArr) To LBound(CutArr) Step -1             If CutArr(j, 1) <> 0 Then                 i = j             End If         Next j                  'Reset TotCut to sum of all 1st         'dimensions         TotCut = 0         For j = LBound(CutArr) To UBound(CutArr)             TotCut = TotCut + CutArr(j, 1)         Next j     Loop          'Output totals to a message box     sTtl = "Total stock at " & dStk & " = " & TotStk              sMsg = "Board No." & vbTab & "Cut Lenght" & vbCrLf          For k = LBound(DetStk, 2) To UBound(DetStk, 2)        sMsg = sMsg & DetStk(0, k) & vbTab & vbTab _             & DetStk(1, k) & vbCrLf     Next k          MsgBox sMsg, vbOKOnly, sTtl   End Sub

27. Thanks for sharing your code.
    I’ve downloaded the last (?) version of it (this one : http://dailydoseofexcel.com/excel/BoardFeetBestFit.zip ) but while doing a quick reading of your code it seems it’s only “cutting” one dimensional ? I mean only the running meters and quantities are being analysed, and then put as optimized as possible into the different boards.
    I need a 2 dimensional code. Where your (wood) panel is a fixed width & height, and the panels you need to cut out or being placed as optimized as possible.
    They may been rotated or not, depending on what kind of wood / execution you have.
    I know it makes it much more difficult than what you wrote, but do you think it’s do-able starting from your code or it’s too complex ?
    Thank you.

    (and very small tip: it’s length and not lenght ;) )

28. I’ve tried to write the code for area instead of linear a couple times, but never got anywhere. If I were going to do it again, I’d search for “2d cutting stock problem algorithm” and adapt it to VBA.

29. Hey Dick,
    Love this code. Is there a way for it to choose from different lengths and give you the best possible options?
    EG. 6, 9 or 12 metre lengths as stock length available

    Thanks

30. Hello Dick,
    Thank you for sharing.
    Can I use this code in programs I have written in Excel.
    Yours truly.

31. @Sefa. Yes, you can.

32. Hello Dick,
    Everytime when i press the Best Fit button it crashes down (excel turns off).
    Don’t you have idea where the problem could be?
    Thank you.

33. No, I don’t. It doesn’t use any references to libraries (other than the standard ones) so it’s not a corrupt or missing library.

    I ran it through the code cleaner and it works for me without crashing. Try deleting your version and downloading this newly cleaned one.

    http://dailydoseofexcel.com/excel/BoardFeet2021.zip

    If that still doesn’t work, you could set a breakpoint on the first line and see if you can figure out which line is causing the problem.

34. Will be nice if a 3rd column shows the offcut piece.

35. Hi Dick

    Really nice spreadsheet.

    I dont suppose this can be used in ms access?
    Also waste and efficiency rate would be nice to see.

    Thank you in advance.
    Br Kim

36. Hello Dick

    Is it possible for you to make a output in excel cells not in message,
    or at least if you point me in right directions with a peace of VBA code.

    Thank you
    Regards

37. At the bottom of ComputeStick, change the code to this

    Visual Basic

    'sMsg = "Board No." & vbTab & "Cut Lenght" & vbCrLf 'For k = LBound(DetStk, 2) To UBound(DetStk, 2) ' sMsg = sMsg & DetStk(0, k) & vbTab & vbTab _ ' & DetStk(1, k) & vbCrLf 'Next k 'MsgBox sMsg, vbOKOnly, sTtl wshCuts.Range("L20").Resize(UBound(DetStk, 2) + 1, UBound(DetStk, 1) + 1).Value = Application.Transpose(DetStk)

    1 2 3 4 5 6 7 8 9 10

    'sMsg = "Board No." & vbTab & "Cut Lenght" & vbCrLf          'For k = LBound(DetStk, 2) To UBound(DetStk, 2)     '   sMsg = sMsg & DetStk(0, k) & vbTab & vbTab _     '        & DetStk(1, k) & vbCrLf     'Next k          'MsgBox sMsg, vbOKOnly, sTtl          wshCuts.Range("L20").Resize(UBound(DetStk, 2) + 1, UBound(DetStk, 1) + 1).Value = Application.Transpose(DetStk)

    That comments out all the message box stuff and writes the array (DetStk) starting in L20. You can obviously change that starting cell to whatever you want.

38. Hello Dick,

    I have been looking forever for code to be able to optimize an Excel cut list that I had created for our drafting team. We do a significant amount of lineal profile cutting and with getting automated saw, it would be a huge help for us to incorporate this code into our existing cut list.

    My question is, could this code be adapted to include a “mark number” along with the cut sizes? We produce labels for all of our cuts so having the “mark number” listed along with the cut size would be awesome. The qty would always be 1, so ideally we could replace the qty with the “mark number” Is that doable?

39. That seems doable. Can you give me an example of what your input and output would look like?

40. The input would be the same format you have in your files, with the only difference being a mark number in a 3rd column. The output would be the same that you have, but would just need to pull over the mark number with the corresponding length. The qty is always 1, even if there 5 pieces of the same length, they would just be listed as mark # 1,2,3,4,5 and each have the same length. Let me know if you have any additional questions and I can possibly send you an email with an example file I would be trying to incorporate the vba logic in.

41. Say you have this

    Visual Basic

    No. of pieces Cut Length Stock Length 1 8 10 1 6 1 3 1 1

    1 2 3 4 5

    No. of pieces Cut Length Stock Length 1 8 10 1 6 1 3 1 1

    Then you want

    Visual Basic

    1 8 8 1 1 9 2 6 6 2 3 9

    1 2 3 4

    1       8       8 1       1       9 2       6       6 2       3       9

    Add a variable named lMark and change the last For k Next look in the ComputeStock procedure to

    Visual Basic

    For k = LBound(DetStk, 2) To UBound(DetStk, 2) If k = 0 Then lMark = DetStk(1, k) Else If DetStk(0, k) <> DetStk(0, k - 1) Then lMark = DetStk(1, k) Else lMark = lMark + DetStk(1, k) End If End If sMsg = sMsg & DetStk(0, k) & vbTab & vbTab _ & DetStk(1, k) & vbTab & vbTab & lMark & vbCrLf Next k

    1 2 3 4 5 6 7 8 9 10 11 12 13

    For k = LBound(DetStk, 2) To UBound(DetStk, 2)         If k = 0 Then             lMark = DetStk(1, k)         Else             If DetStk(0, k) <> DetStk(0, k - 1) Then                 lMark = DetStk(1, k)             Else                 lMark = lMark + DetStk(1, k)             End If         End If        sMsg = sMsg & DetStk(0, k) & vbTab & vbTab _             & DetStk(1, k) & vbTab & vbTab & lMark & vbCrLf     Next k

42. Hello,

    The Application.Transpose works well.
    But I want to expose the result to limit the number of rows.

    So I wan that all cutted length of a profile as exposed shown in a row and each row starts with the profile. Something like:

    Profile cut1 cut2 cut3 cut4 cut5
    1 4840 1102
    2 4840 1102
    3 2178 2178 1625
    4 2178 2178 1625
    5 2178 2178 1625
    6 2178 2178 1625
    7 1863 1863 1863 240 120
    8 1863 1863 1863 240 120
    9 1863 1863 1863 240 120
    10 1863 1863 1863 240 120
    11 1625 1625 1625 1102
    12 1625 1625 1625 1102
    13 1625 1625 1625 1102
    14 1625 1625 1625 1102

    Is that anyway possible?
    I tried to google, but couldn’t find something.

    Thank you in advance

Posting code? Use <pre> tags for VBA and <code> tags for inline.