Excel batch combination optimiser

dsmcl71
dsmcl71 used Ask the Experts™
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
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Shaun VermaakSenior 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

Open in new window

Author

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.
Shaun VermaakSenior 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

Open in new window

Author

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)
Shaun VermaakSenior 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

Open in new window

Lengths.xlsm
Shaun VermaakSenior Consultant
Awarded 2017
Distinguished Expert 2018

Commented:
Busy with point one. Misunderstood initially
Shaun VermaakSenior 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").RemoveDuplicates Columns:=1, Header:=xlYes
'                            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").RemoveDuplicates Columns:=1, Header:=xlYes
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

Open in new window


1) Removes same items (different order)
excell.jpg2) Add sum then sort from large to small
excell2.jpgLengths.xlsm
Bill PrewTest your restores, not your backups...
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
Test your restores, not your backups...
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

Author

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

Author

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?
Bill PrewTest your restores, not your backups...
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

Author

Commented:
Thanks Bill,

Using all inventory would be the main driver, maximising the size of each load would be next highest priority.
Bill PrewTest your restores, not your backups...
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
Bill PrewTest your restores, not your backups...
Top Expert 2016

Commented:
dsmcl71

Take a look at this article, it should help you with the question close process.


For future reference here some other items related to closing questions in case they are helpful.


And don't forget you can always "request attention" on a question if you are not sure how to proceed and a support person will be in touch to help you out.

~bp

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial