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

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

Do more with

EXPERT OFFICE^{®} is a registered trademark of EXPERTS EXCHANGE^{®}

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

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

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

This can be address by sorting by total and using higher "scoring" ones

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)

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

Lengths.xlsm

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

EE29002678.xlsx

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?

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

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.

**The experts told me "you can not do that". What do I do now?****How do I accept a comment as my solution?****How do I accept multiple comments as my solution?****I answered my own question. How do I close the question?****I answered my own question with help. How do I close the question?****How do I delete a question?****What grade should I award?**

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.

## Premium Content

You need an Expert Office subscription to comment.Start Free Trial