VBA or solver? Model for allocation of pipe-lengths with more constraints.

DonKampfello

New Member
Joined
Apr 18, 2022
Messages
13
Office Version
  1. 365
Platform
  1. Windows
Hi Experts

I run a small business where I produce and sell pipes in different widths, made out of a soft glas material which I purchase in the form of "Rolls". I want to automate my purchasing process so I dont waste material. Here is my worksheet: Excel solver exampel MKSO.xlsx

1. In yellow I have the dimensions
2. In Blue i have my forcasted Rolls demand amount I need to purchase. This is the amount of "rolls" of each dimension I have to make.
3. We have the big material rolls in bulk, purchased from my supplier, that I make cuts from to the right widht. And waste from cutting the roll.
4. In the lightgreen Array, is where I want to create my model. I am not sure where to start since im new to Data Science, so I am looking for the easiest tool that Excel can offer. VBA/Solver/functions etc.

Basically, Im trying to find a way to fill out all the
cells and figure out which kind of combination I should cut. This would give me an idea of how many bulk roolls of 4 m I should buy from the supplier, so I end up with an equal sum of "Purchasing rolls" to my demand.

linear2.JPG


Any help would be highly appreciated. Even a pointing finger to what I should research.

I hope this makes sense.


Best from a small business owner.
 

Attachments

  • Linear.JPG
    Linear.JPG
    65.5 KB · Views: 2

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

BSALV

Banned user
Joined
Oct 31, 2010
Messages
1,651
Office Version
  1. 365
  2. 2013
  3. 2007
solver
It's repeating "VBA+Solver".
Each time you solve your constraints for just 1 pipe and you force the biggest dimension into the solution (row12), because that's the most "trouble shooting" one.
Then you look how many times, you can use this solution in cell AK12 for the remaining parts.
Now VBA copies that solution and the cells AK12 (number of duplicates) and the waste to your table
And you can start over until everything is done.

In AK7 and AK8, you see the number of pipes of 4 m and the total waste.

VBA Code:
Sub Oplossen()
     myoffset = 5
     mymax = 100
     With Sheets("Ark1")
          Set c = .Range("C11:AG11")     'the variables of the solver
          Set c1 = .Range("AK12")     'cell with the number of duplicates
          c.Offset(myoffset).Resize(mymax, c.Columns.Count + 2).ClearContents     'reset previous solutions

          Do     'start loop
               Result = SolverSolve(True)
               SolverFinish KeepFinal:=1

               Select Case Result     'what kind of result had the solver
                    Case 0:     ' Result = 0, Solution found, optimality and constraints satisfied
                    Case 1:     ' Result = 1, Converged, constraints satisfied
                    Case 2:     ' Result = 2, Cannot improve, constraints satisfied
                    Case 3:     ' Result = 3, Stopped at maximum iterations
                    Case 4: MsgBox "Solver did not converge", vbCritical: Exit Sub
                    Case 5: MsgBox "No feasible solution", vbCritical: Exit Sub
                    Case 14
                    Case Else: MsgBox "????": Exit Sub
               End Select

               b = (c1.Value > 0 And c1.Value < 1E+99)     'the number of duplicates, is that feasible ?
               If b Then     'okay
                    With c.Offset(myoffset + ptr)     'range to write
                         .Value = c.Value     'write the variables
                         .Offset(, c.Columns.Count).Resize(, 2).Value = Array(c1.Value, c1.Offset(-1).Value)     'write the number of duplicates and waste
                    End With
               End If
               ptr = ptr + 1     'pointer+1

          Loop While b And ptr < mymax     'loop until everything is solved or max 130
     End With
End Sub

Excel solver exampel MKSO.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKAL
1
2
3250200225250300350375380400410450490500515530565600618620670700720721757770800825900927100010301200
4Dimension/Widht (m)0,6600,7420,8250,9901,1551,2371,2531,3191,3521,4841,6161,6491,6991,7481,8641,9792,0392,0452,2102,3092,3752,3782,4972,5402,6392,7212,9693,0583,2993,3983,958
5Lenght (Demand)4895,744379457994284865,874127,521976667,5262,82112939190,13402198480,9909,6388,543,73722,649,4287,4252151,74185,2148,5991,527,22102337118,6
6Rolls (Demand)202318398201188321521213442115122117141921
7>=>=>=>=>=>=>=>=>=>=>=>=>=>=>=>=>=>=>=>=>=>=>=>=>=>=>=>=>=>=>=Total :12.558
8Purchasing rolls1111211211113112222212232323211Total waste:6.369,660
9
10remaining                               
11solver <=44,0000<--------Waste
12biggest                                9E+99<------Count
13Rolls Only entire rolls
14
15CombinationTotalWaste
161112000,042
172110300,602
183112000,042
19418000,701
205112500,117
216112250,200
Ark1
Cell Formulas
RangeFormula
C6:AG6C6=ROUNDUP(C5/$B$3,0)
C8:AG8C8=SUM(C13:C55)
AK7AK7=SUM(AH16:AH130)
AK8AK8=SUMPRODUCT(AH16:AH130,AI16:AI130)
C10:AG10C10=C3-SUMPRODUCT(C16:C130,$AH$16:$AH$130)
AH11AH11=SUMPRODUCT(C11:AG11,C4:AG4)
C12:AG12C12=--(C4=$AH$12)
AH12AH12=MAX(IF(($C$10:$AG$10>0)*($C$4:$AG$4<=$AJ$11),$C$4:$AG$4))
AK11AK11=+AJ11-AH11
AK12AK12=TRUNC(MIN(IF(C11:AG11=0,9E+99,C10:AG10/C11:AG11)))
Named Ranges
NameRefers ToCells
MyResults='Ark1'!$C$16:$AG$130C10, C8
'Ark1'!solver_adj='Ark1'!$C$11:$AG$11AK12, AH11
'Ark1'!solver_lhs1='Ark1'!$AH$11AK11
'Ark1'!solver_lhs2='Ark1'!$C$11:$AG$11AK12, AH11
'Ark1'!solver_lhs3='Ark1'!$C$11:$AG$11AK12, AH11
'Ark1'!solver_rhs1='Ark1'!$AJ$11AK11, AH12
'Ark1'!solver_rhs2='Ark1'!$C$10:$AG$10AK12, AH12
'Ark1'!solver_rhs4='Ark1'!$C$11:$AG$11AK12, AH11
 
Upvote 0
Solution

DonKampfello

New Member
Joined
Apr 18, 2022
Messages
13
Office Version
  1. 365
Platform
  1. Windows
solver
It's repeating "VBA+Solver".
Each time you solve your constraints for just 1 pipe and you force the biggest dimension into the solution (row12), because that's the most "trouble shooting" one.
Then you look how many times, you can use this solution in cell AK12 for the remaining parts.
Now VBA copies that solution and the cells AK12 (number of duplicates) and the waste to your table
And you can start over until everything is done.

In AK7 and AK8, you see the number of pipes of 4 m and the total waste.

VBA Code:
Sub Oplossen()
     myoffset = 5
     mymax = 100
     With Sheets("Ark1")
          Set c = .Range("C11:AG11")     'the variables of the solver
          Set c1 = .Range("AK12")     'cell with the number of duplicates
          c.Offset(myoffset).Resize(mymax, c.Columns.Count + 2).ClearContents     'reset previous solutions

          Do     'start loop
               Result = SolverSolve(True)
               SolverFinish KeepFinal:=1

               Select Case Result     'what kind of result had the solver
                    Case 0:     ' Result = 0, Solution found, optimality and constraints satisfied
                    Case 1:     ' Result = 1, Converged, constraints satisfied
                    Case 2:     ' Result = 2, Cannot improve, constraints satisfied
                    Case 3:     ' Result = 3, Stopped at maximum iterations
                    Case 4: MsgBox "Solver did not converge", vbCritical: Exit Sub
                    Case 5: MsgBox "No feasible solution", vbCritical: Exit Sub
                    Case 14
                    Case Else: MsgBox "????": Exit Sub
               End Select

               b = (c1.Value > 0 And c1.Value < 1E+99)     'the number of duplicates, is that feasible ?
               If b Then     'okay
                    With c.Offset(myoffset + ptr)     'range to write
                         .Value = c.Value     'write the variables
                         .Offset(, c.Columns.Count).Resize(, 2).Value = Array(c1.Value, c1.Offset(-1).Value)     'write the number of duplicates and waste
                    End With
               End If
               ptr = ptr + 1     'pointer+1

          Loop While b And ptr < mymax     'loop until everything is solved or max 130
     End With
End Sub

Excel solver exampel MKSO.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKAL
1
2
3250200225250300350375380400410450490500515530565600618620670700720721757770800825900927100010301200
4Dimension/Widht (m)0,6600,7420,8250,9901,1551,2371,2531,3191,3521,4841,6161,6491,6991,7481,8641,9792,0392,0452,2102,3092,3752,3782,4972,5402,6392,7212,9693,0583,2993,3983,958
5Lenght (Demand)4895,744379457994284865,874127,521976667,5262,82112939190,13402198480,9909,6388,543,73722,649,4287,4252151,74185,2148,5991,527,22102337118,6
6Rolls (Demand)202318398201188321521213442115122117141921
7>=>=>=>=>=>=>=>=>=>=>=>=>=>=>=>=>=>=>=>=>=>=>=>=>=>=>=>=>=>=>=Total :12.558
8Purchasing rolls1111211211113112222212232323211Total waste:6.369,660
9
10remaining                               
11solver <=44,0000<--------Waste
12biggest                                9E+99<------Count
13Rolls Only entire rolls
14
15CombinationTotalWaste
161112000,042
172110300,602
183112000,042
19418000,701
205112500,117
216112250,200
Ark1
Cell Formulas
RangeFormula
C6:AG6C6=ROUNDUP(C5/$B$3,0)
C8:AG8C8=SUM(C13:C55)
AK7AK7=SUM(AH16:AH130)
AK8AK8=SUMPRODUCT(AH16:AH130,AI16:AI130)
C10:AG10C10=C3-SUMPRODUCT(C16:C130,$AH$16:$AH$130)
AH11AH11=SUMPRODUCT(C11:AG11,C4:AG4)
C12:AG12C12=--(C4=$AH$12)
AH12AH12=MAX(IF(($C$10:$AG$10>0)*($C$4:$AG$4<=$AJ$11),$C$4:$AG$4))
AK11AK11=+AJ11-AH11
AK12AK12=TRUNC(MIN(IF(C11:AG11=0,9E+99,C10:AG10/C11:AG11)))
Named Ranges
NameRefers ToCells
MyResults='Ark1'!$C$16:$AG$130C10, C8
'Ark1'!solver_adj='Ark1'!$C$11:$AG$11AK12, AH11
'Ark1'!solver_lhs1='Ark1'!$AH$11AK11
'Ark1'!solver_lhs2='Ark1'!$C$11:$AG$11AK12, AH11
'Ark1'!solver_lhs3='Ark1'!$C$11:$AG$11AK12, AH11
'Ark1'!solver_rhs1='Ark1'!$AJ$11AK11, AH12
'Ark1'!solver_rhs2='Ark1'!$C$10:$AG$10AK12, AH12
'Ark1'!solver_rhs4='Ark1'!$C$11:$AG$11AK12, AH11
Sorry for late reply. With some tweaks, I got it to work, thank you very very much.

Can u be of any help with one last question?

You can see in column D the amount of times the combination was used, which is what the solver has outputted (Awsome). I would like to "Lookup" all the combinations that have been used in the Array (D4:M199). Is their a function that returns multiple columns if the cell has a positive value. If the value posetive = true, then the row should be returned as shown in Q4:Y4.

Can this be done?

Worksheet



2022-05-16 11_21_40-Mappe 2.xlsx - Microsoft Excel Online and 4 more pages - Work - Microsoft​...png
 
Upvote 0

BSALV

Banned user
Joined
Oct 31, 2010
Messages
1,651
Office Version
  1. 365
  2. 2013
  3. 2007
Sorry for late reply. With some tweaks, I got it to work,
that is for the 3 mm you loose every time you cut the pipe ??? You didn't mention, so i didn't care ...

Your new question, I don't understand what you want to achieve.
 
Upvote 0

DonKampfello

New Member
Joined
Apr 18, 2022
Messages
13
Office Version
  1. 365
Platform
  1. Windows
You code was very helpfull, so thank you for that. My "Excel-English" is not the best, so maybe I did not explain carefully enough the purpose of the Solver. But thanks anyway.

As for my question. The column D marked in red is the amount of times i have used the combination in yellow. The spreadsheet information is copied from the solver where I got the correct output.
I am trying to find a function to put in Q4 that returns the cells in column D,E,F,G,H,K and L if D4 has a positve value (if the combination was used). Is this possible?
 
Upvote 0

BSALV

Banned user
Joined
Oct 31, 2010
Messages
1,651
Office Version
  1. 365
  2. 2013
  3. 2007
i have no clue, can you show several lines of what you expect as result and why ?
I think you want every unique value of for example "Dim 1", but what do you want in columns X and Y ?
 
Upvote 0

DonKampfello

New Member
Joined
Apr 18, 2022
Messages
13
Office Version
  1. 365
Platform
  1. Windows
i have no clue, can you show several lines of what you expect as result and why ?
I think you want every unique value of for example "Dim 1", but what do you want in columns X and Y ?
I have tried to illustrate with the blue rows, which entries I want to return. The reason for this, is that I want a summery of which combinations are used, how many times and the total sum of the waste.

blue lines.JPG
I don't need to see the combinations that has not been used. So if a cell in column D is 0, I dont need it returned.

Im planning on adding many more combinations, and it take alot of time copying them over manually. Again thanks for your time.
 

Attachments

  • blue lines.JPG
    blue lines.JPG
    220.4 KB · Views: 0
Upvote 0

DonKampfello

New Member
Joined
Apr 18, 2022
Messages
13
Office Version
  1. 365
Platform
  1. Windows
Got it to work. Thanks for your time.

=IFERROR(INDEX(AR$12:AR$207;SMALL(IF($AQ$12:$AQ$207>0;ROW(AR$12:BA$207)-ROW(AR$12)+1);ROWS(BD$12:BD12)));"")
 
Upvote 0

BSALV

Banned user
Joined
Oct 31, 2010
Messages
1,651
Office Version
  1. 365
  2. 2013
  3. 2007
this is an advanced filter, so see the condition in O3:O4 and the macro

Mappe 2 (1).xlsb
CDEFGHIJKLMNOPQRSTUVWXYZAA
1
2Combination
3Ammountdim1dim2dim3dim4dim5dim6TotalWasteWaste IFAmmountAmmountdim1dim2dim3dim4dim5dim6TotalWaste IF
413.958 3.9580.0420.042>013.958 3.9580.042
523.398 3.3980.6021.20523.398 3.3981.205
693.2990.660 3.9580.0420.37493.2990.660 3.9580.374
723.0580.825 3.8830.1170.23523.0580.825 3.8830.235
803.0580.742 3.8000.2000.00042.9690.825 3.7930.826
903.0580.660 3.7180.2820.00012.7211.237 3.9580.042
1002.9690.990 3.9580.0420.00012.6391.253 3.8920.108
1142.9690.825 3.7930.2070.82612.5401.319 3.8590.141
1212.7211.237 3.9580.0420.04222.4970.8250.660 3.9810.037
1312.6391.253 3.8920.1080.10812.3781.616 3.9950.005
1402.6391.237 3.8760.1240.00012.3780.8250.742 3.9450.055
152.6391.319 3.9580.04212.3751.616 3.9910.009
1602.6390.6600.660 3.9580.0420.000192.3091.649 3.9580.790
Ark1



VBA Code:
Sub MyAdvancedFilter()

     With Sheets("Ark1")
          .Range(.Range("D3"), .Range("D" & Rows.Count).End(xlUp)).Resize(, 10).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=.Range("O3:O4"), CopyToRange:=.Range("Q3:Y3"), Unique:=False
     End With
End Sub
 
Upvote 0

BSALV

Banned user
Joined
Oct 31, 2010
Messages
1,651
Office Version
  1. 365
  2. 2013
  3. 2007
you have excel365, so you can use the new filter-functions (i think you're using another range as source
Rich (BB code):
=IFERROR(INDEX(FILTER(D4:M1000,D4:D1000>0),SEQUENCE(COUNTIF(D4:D1000,">0")),TRANSPOSE({1,2,3,4,5,6,7,8,10})),"-")
see AA4
mappe2
 
Upvote 0

Forum statistics

Threads
1,186,802
Messages
5,959,865
Members
438,453
Latest member
NRG909

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top