Pallet calculations

[Sherianne20201]

I am trying to calculate pallet specs based on different sized boxes. I am using standard sized pallets 48 x 40 - maximum height around 72 (I am using inches)

For instance, if I have the following items, how many can fit on a pallet using a 48 x 40 x around 72 inch pallet?

 

[James006]

Hi,

By no means ... a simple problem ... !!!
see:

Bin packing problem - Wikipedia

en.wikipedia.org

In my humble opinion, you should searching for a dedicated add-in ...

 

[Georgiboy]

In simple terms - all of them:

Book1
	A	B	C	D	E	F	G	H	I
1	Master Pack QTYS	Length	Width	Height	Weight	Volume		Pallet Width	48
2	2	13.4	13.2	13	15.1	4598.88		Pallet Length	40
3	6	12.8	9.9	11.1	11.1	8439.552		Pallet Height	72
4	6	12.8	9.9	11.1	11.1	8439.552		Pallet Cube	138240
5	6	12.8	9.9	11.1	11.1	8439.552
6	6	22.9	19.1	10.1	38.9	26505.83
7	6	25.4	12.2	10.1	19.3	18778.73
8	6	21.9	9.2	11.1	13.8	13418.57
9	6	24	12	10.9	17	18835.2
10	6	17.5	9.3	11.2	10.1	10936.8
11	12	22.3	5.9	10.1	5	15946.28
12						134339
Sheet2

Cell Formulas
Range	Formula
F2:F11	F2	=(B2:B11*C2:C11*D2:D11)*A2:A11
I4	I4	=I1*I2*I3
F12	F12	=SUM(F2:F11)
Dynamic array formulas.

 

[Bill Rockenbach]

Hello Sherianne,
There are many ways to stack a pallet; carton length to pallet length, carton length to pallet width, carton width laid flat, carton height laid flat, carton laid standing up, pallet layers alternated for stability, pallet weight limits, layers separated by paper or cardboard or wood, various brick pattern layers, etc, etc

This code will give you layer quantity by carton laid length to pallet length and length to pallet width with width laid flat. If your pallet patterns are always the same you could use a simple excel formula on the sheet instead of using vba code for different layer patterns.

Sub CalculateLaodQty()
'Calculate maximume arton load on pallet
'only a few of these potential variables are used
    Dim PalletWidth As Double
    Dim PalletLength As Double
    Dim PalletHeight As Double  'Maximum allowed
    Dim PalletWeight As Double  'Maximum allowed

    Dim CartonWidth As Double
    Dim CartonLength As Double
    Dim CartonHeight As Double
    Dim CartonWeight As Double

    Dim WidthQty As Double
    Dim LengthQty As Double

    'LayerBrick Patterons
    Dim LengthToLengthLayerQty As Integer
    Dim LengthToWidthLayerQty As Integer
    Dim AlternateDirection As Boolean

    Dim Basketweave As Double
    Dim HalfBasketweave As Double
    Dim Herringbone As Double
    Dim JackOnJack As Double

    Dim Length_RowWidthQty As Double
    Dim Length_RowLengthQty As Double
    Dim Width_RowWidthQty As Double
    Dim Width_RowLengthQty As Double

    'gather data
    PalletWidth = Range("C2")
    PalletLength = Range("C3")
    PalletHeight = Range("C4")
    PalletWeight = Range("C5")

    CartonWidth = Range("C7")
    CartonLength = Range("C8")
    CartonHeight = Range("C9")
    CartonWeight = Range("C10")


    'Determine quantity for one row Length
    Length_RowWidthQty = Application.WorksheetFunction.RoundDown(PalletWidth / CartonLength, 0)
    Length_RowLengthQty = Application.WorksheetFunction.RoundDown(PalletLength / CartonLength, 0)

    'Determine quantity for one row Width
    Width_RowWidthQty = Application.WorksheetFunction.RoundDown(PalletWidth / CartonWidth, 0)
    Width_RowLengthQty = Application.WorksheetFunction.RoundDown(PalletLength / CartonWidth, 0)

    'Calculate layer quantity laid Length to Length
    LengthToLengthLayerQty = Length_RowLengthQty * Width_RowWidthQty

    'Calculate layer quantity laid Length to Width
    LengthToWidthLayerQty = Length_RowWidthQty * Width_RowLengthQty


    MsgBox "Layer Laid Length to Length QTY " & LengthToLengthLayerQty & vbLf & _
           "Layer Laid Length to Width Qty " & LengthToWidthLayerQty
End Sub