Excel batch combination optimiser

on
Hi,
I've got  packs of timber that are loaded into a fixed length chamber. The chamber can hold a maximum length of 19.8m and has a minimum length of 16.2m (these could change in the future)
There are currently 7 standard pack lengths (again could change in the future) and the batch can be made up of any of these pack lengths.
The attached file shows some manually entered combinations that suit the chamber constraints. I'm looking for a solution that can build a list of all the possible combinations that fit within the chamber length and suit the standard pack length constraints.
The idea is to provide operators with a quick tool to evaluate the "best fit" combinations that fill the chamber in the least number of batches.
Thanks.
C--Users-dalemcla-Desktop-Book1.xlsx
Comment
Watch Question

Do more with

EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Consultant
Awarded 2017
Distinguished Expert 2018

Commented:
``````Sub GetCombinations()

Dim inputRange As Range
Dim min As Double
Dim max As Double

Set inputRange = Range("I3:I9")
min = Cells(2, 5).Value
max = Cells(1, 5).Value

For Each cell1 In inputRange
For Each cell2 In inputRange
For Each cell3 In inputRange
For Each cell4 In inputRange
For Each cell5 In inputRange
totalLength = cell1.Value + cell2.Value + cell3.Value + cell4.Value + cell5.Value
If totalLength >= min And totalLength <= max Then
MsgBox "[1]=" & cell1.Text & " [2]=" & cell2.Text & " [3]=" & cell3.Text & " [4]=" & cell4.Text & " [5]=" & cell5.Text & " [Total]=" & totalLength
End If
Next cell5
Next cell4
Next cell3
Next cell2
Next cell1

End Sub
``````

Commented:
Hi Shaun,

That looks good, but is it possible to store all the possible solutions in a table format? Also, there could be some solutions where a batch could be more than 5 individual packs (e.g. 3.6; 3.6; 3.0; 3.0; 3.0; 3.0). Does the macro account for those in anyway?

Cheers.
Senior Consultant
Awarded 2017
Distinguished Expert 2018

Commented:
That looks good, but is it possible to store all the possible solutions in a table format?
See attached file

Also, there could be some solutions where a batch could be more than 5 individual packs (e.g. 3.6; 3.6; 3.0; 3.0; 3.0; 3.0). Does the macro account for those in anyway?
Can be extended, probably with recursion

``````Sub GetCombinations()

Dim inputRange As Range
Dim min As Double
Dim max As Double
Dim counter As Integer
Set inputRange = Range("I1:O1")
min = Cells(2, 5).Value
max = Cells(1, 5).Value

counter = 6

For Each cell1 In inputRange
For Each cell2 In inputRange
For Each cell3 In inputRange
For Each cell4 In inputRange
For Each cell5 In inputRange
totalLength = cell1.Value + cell2.Value + cell3.Value + cell4.Value + cell5.Value
If totalLength >= min And totalLength <= max Then
Cells(counter, 2).Value = cell1.Value
Cells(counter, 3).Value = cell2.Value
Cells(counter, 4).Value = cell3.Value
Cells(counter, 5).Value = cell4.Value
Cells(counter, 6).Value = cell5.Value
counter = counter + 1
End If
Next cell5
Next cell4
Next cell3
Next cell2
Next cell1

End Sub
``````

Commented:
A couple of things I noticed after running the code:

1) a combination sequence like 6.0; 6.0; 3.0; 2.4; 2.4 is effectively the same as 6.0; 6.0; 2.4; 2.4; 3.0 and 3.0; 6.0; 6.0; 2.4; 2.4 (i.e. it does not require repeating)

2) the macro shows no sequences for shorter combinations that still meet the min/max criteria (e.g. 5.4; 5.4; 5.4 or 6.0; 6.0; 6.0)
Senior Consultant
Awarded 2017
Distinguished Expert 2018

Commented:
1) a combination sequence like 6.0; 6.0; 3.0; 2.4; 2.4 is effectively the same as 6.0; 6.0; 2.4; 2.4; 3.0 and 3.0; 6.0; 6.0; 2.4; 2.4 (i.e. it does not require repeating)
See new file

2) the macro shows no sequences for shorter combinations that still meet the min/max criteria (e.g. 5.4; 5.4; 5.4 or 6.0; 6.0; 6.0)
This can be address by sorting by total and using higher "scoring" ones

``````Sub GetCombinations()

Dim inputRange As Range
Dim min As Double
Dim max As Double
Dim counter As Integer
Set inputRange = Sheets("Input").Range("D1:D8")
min = Sheets("Input").Cells(2, 2).Value
max = Sheets("Input").Cells(1, 2).Value

counter = 2

For Each cell1 In inputRange
For Each cell2 In inputRange
For Each cell3 In inputRange
For Each cell4 In inputRange
For Each cell5 In inputRange
totalLength = cell1.Value + cell2.Value + cell3.Value + cell4.Value + cell5.Value
If totalLength >= min And totalLength <= max Then
Sheets("Output").Cells(counter, 1).Value = cell1.Value
Sheets("Output").Cells(counter, 2).Value = cell2.Value
Sheets("Output").Cells(counter, 3).Value = cell3.Value
Sheets("Output").Cells(counter, 4).Value = cell4.Value
Sheets("Output").Cells(counter, 5).Value = cell5.Value
counter = counter + 1
'                        If counter > 10 Then
'                            Exit Sub
'                        End If
End If
Next cell5
Next cell4
Next cell3
Next cell2
Next cell1

Sheets("Output").Range("\$A\$1:\$E\$10").RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5), Header:=xlYes

End Sub
``````
Lengths.xlsm
Senior Consultant
Awarded 2017
Distinguished Expert 2018

Commented:
Busy with point one. Misunderstood initially
Senior Consultant
Awarded 2017
Distinguished Expert 2018
Commented:
``````Sub GetCombinations()

Dim numbers(4) As Double
Dim inputRange As Range
Dim min As Double
Dim max As Double
Dim counter As Integer
Set inputRange = Sheets("Input").Range("D1:D8")
min = Sheets("Input").Cells(2, 2).Value
max = Sheets("Input").Cells(1, 2).Value

counter = 1

For Each cell1 In inputRange
For Each cell2 In inputRange
For Each cell3 In inputRange
For Each cell4 In inputRange
For Each cell5 In inputRange
If counter = 1 Then
Sheets("Output").Cells(counter, 1).Value = "1|2|3|4|5"
counter = counter + 1
End If

totalLength = cell1.Value + cell2.Value + cell3.Value + cell4.Value + cell5.Value
If totalLength >= min And totalLength <= max Then
numbers(0) = cell1.Value
numbers(1) = cell2.Value
numbers(2) = cell3.Value
numbers(3) = cell4.Value
numbers(4) = cell5.Value
Sheets("Output").Cells(counter, 1).Value = SortArray(numbers())
counter = counter + 1
'                        If counter > 10 Then
'                            Sheets("Output").Columns("A:A").Select
'                                Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
'                                    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
'                                    Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
'                                    :="|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
'                                    1)), TrailingMinusNumbers:=True
'                            Exit Sub
'                        End If
End If
Next cell5
Next cell4
Next cell3
Next cell2
Next cell1

Sheets("Output").Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
1)), TrailingMinusNumbers:=True
End Sub
Function SortArray(inputArray() As Double) As String
Call QuickSort(inputArray(), LBound(inputArray()), UBound(inputArray()))
Dim returnString As String

For i = LBound(inputArray()) To UBound(inputArray())
If returnString <> "" Then
returnString = returnString & "|" & inputArray(i)
Else
returnString = inputArray(i)
End If
Next i

SortArray = returnString

End Function

Private Sub QuickSort(ByRef Field() As Double, ByVal LB As Long, ByVal UB As Long)
Dim P1 As Long, P2 As Long, Ref As String, TEMP As String

P1 = LB
P2 = UB
Ref = Field((P1 + P2) / 2)

Do
Do While (Field(P1) < Ref)
P1 = P1 + 1
Loop

Do While (Field(P2) > Ref)
P2 = P2 - 1
Loop

If P1 <= P2 Then
TEMP = Field(P1)
Field(P1) = Field(P2)
Field(P2) = TEMP

P1 = P1 + 1
P2 = P2 - 1
End If
Loop Until (P1 > P2)

If LB < P2 Then Call QuickSort(Field, LB, P2)
If P1 < UB Then Call QuickSort(Field, P1, UB)
End Sub
``````

1) Removes same items (different order)
2) Add sum then sort from large to small
Lengths.xlsm
Top Expert 2016

Commented:
FYI, pondering this question a little, may have a slightly different approach, if you are not in a huge hurry...

~bp
Top Expert 2016
Commented:
What would you think of a representation like this, basically showing the quantity of each length used to fill the chamber?

How do they decide what to do?  Is it purely driven by trying to fill to maximum, with unlimited quantities of each length, or are there other considerations?  Like would it be better to have more long lengths, or more short ones?  And does the quantity of the various lengths vary so at some point they might want to quickly find 'formulas' that use more of a certain length?

I think the attached shows all the viable permutations, and we could certainly take that and build the table that you showed in your original sheet, just brainstorming about the best way to represent these formulas...

~bp

EE29002678.xlsx

Commented:
Hi Shaun,
I get an error when running the code. Attached is an image of the error and the result of the output.
C--Users-dalemcla-Desktop-Error.JPG
C--Users-dalemcla-Desktop-Output.JPG

Commented:
Hi Bill,

We tend to have  a lot more of the longer lengths (i.e. 6.0; 5.4; 4.8) and very little of the she shorts (i.e. 3.0; 2.4). The initial plan was to just  provide operators with combination of optimum solutions to filling the chambers.

I do like the way you've presented the solution. The one thing you've got me thinking about now, is an option to enter the quantity for each length and then provide an "optimised" batching solution. Hope that makes sense?
Top Expert 2016

Commented:
I'll do a little more work on my approach tomorrow here, just as an alternative.

I'm  a Math guy so I certainly like the thought about optimizing for a portfolio of inventory.  That being said, I can't think of a simple mathematical way to model that solve quickly for optimization.  Other approach would involve brute force of running each "scenario" and finding best.

What would be the criteria to optimize for?  Using all inventory?  Or maximizing the size of each load, trying to get as lose to max as possible?  Or reducing the number of loads?

~bp

Commented:
Thanks Bill,

Using all inventory would be the main driver, maximising the size of each load would be next highest priority.
Top Expert 2016

Commented:
Pondered this a bit, but nothing fairly easy popped into my head.  Not sure if the Excel Solver add-in could help with this task, but I have no experience with it.

You might want to wrap up this question as far as just getting the list or combinations (several good answers provided I think) and then if you want to pursue an optimization or scheduling algorithm ask that as a follow on question, or possibly a Gig since it would likely be more than a small amount of work.

~bp
Top Expert 2016

Commented:
dsmcl71