Optimise pallet packing

[CarlW1179]

Hi

I've been tasked with finding a way to optimizing some distribution.

in a nut shell we can fit 4 pallets onto a van 2 hold 32 Cases and 2 hold 40 cases and i need to find a way to work out how much of each product will go on each pallet.

Pallet 1 Holds = 32
Pallet 2 Holds = 32
Pallet 3 Holds = 40
Pallet 4 Holds = 40

Product 1 12 = Pallet 1
product 2 14 = Pallet 1
Product 3 36 = x6 Pallet 1 & X30 Pallet 2
Product 4 29 = x2 Pallet 2 & X27 Pallet 3
Product 5 38 = X13 Pallet 3 & X25 Pallet 4

Sorry if this looks like a GCSE question.

Cheers

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Need a formula to work out optimum pallet fills
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[felixstraube]

How about something like this?:

Pallets.xlsx
	A	B	C
1	Pace in Van
2		144
3	Pallet	Cases per pallet
4	1	32	12x Product 1 | 14x Product 2 | 6x Product 3
5	2	32	30x Product 3 | 2x Product 4
6	3	40	27x Product 4 | 13x Product 5
7	4	40	25x Product 5
8
9	Shipment
10		129
11	Product	Cases
12	1	12
13	2	14
14	3	36
15	4	29
16	5	38
Hoja1

Cell Formulas
Range	Formula
B2	B2	=SUM(B4:B7)
C4:C7	C4	=LET( CasesPallets,TRANSPOSE(VALUE(TEXTSPLIT(CONCAT(MAP($A$4:$A$7,$B$4:$B$7,LAMBDA(p,c,REPT(p&";",c)))),";",,1))), nCasesByPallets,COUNT(CasesPallets), Product,FILTER($A$12:$A$32,$A$12:$A$32<>""), Cases,FILTER($B$12:$B$32,$A$12:$A$32<>""), tCasesProducts,TRANSPOSE(VALUE(TEXTSPLIT(CONCAT(MAP(Product,Cases,LAMBDA(p,c,REPT(p&";",c)))),";",,1))), nCasesByProduct,COUNT(tCasesProducts), CasesProducts,EXPAND(tCasesProducts,nCasesByPallets,,""), tProductPerPallet,FILTER(CasesProducts,CasesPallets=A4), uProductPerPallet,UNIQUE(FILTER(tProductPerPallet,tProductPerPallet<>"")), IF(nCasesByPallets<nCasesByProduct,"More cases (" &nCasesByProduct&") than places on pallets ("&nCasesByPallets&")", TEXTJOIN(" | ",,(BYROW(uProductPerPallet,LAMBDA(x,COUNTA(FILTER(tProductPerPallet,tProductPerPallet=x))&"x Product "&x)))) ) )
B10	B10	=SUM(B12:B32)

 

[CarlW1179]

felixstraube​

Thats awsome thank you for taking the time to have a look. I have just tried it and its coming up with the formula problem text box saying there is an issue

MAP($A$4:$A$7,$B$4:$B$7,LAMBDA(p,c,REPT(p&";",c))) in this part of the formula.

 

[felixstraube]

c are the numbers in column B. They have to be numbers.

here is the working book:

Pallets.zip

If you still have problem, try uploading your workbook to a cloud storage and i'll have a look.