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

#### DonKampfello

##### New Member
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.

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
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
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
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
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_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

#### DonKampfello

##### New Member
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
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
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_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

#### BSALV

##### Banned user
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.

#### DonKampfello

##### New Member
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?

#### BSALV

##### Banned user
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 ?

#### DonKampfello

##### New Member
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.

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
220.4 KB · Views: 0

#### DonKampfello

##### New Member
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)));"")

#### BSALV

##### Banned user
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``````

#### BSALV

##### Banned user
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

Replies
1
Views
201
Replies
2
Views
211
Replies
0
Views
156
Replies
2
Views
1K
Replies
16
Views
919

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.

### Which adblocker are you using?

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

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