Complicated Packing Instructions

[Monkeywrenchman]

Hi all and thanks for any help in advance

I am trying to create a bespoke packing list for our despatch department telling then which product variants and which boxes to put onto which pallet without splitting the variant over more that 1 pallet for example

Type 1 (qty 24) on Std Pallet 1 - Type 2 (qty 54) on Euro Pallet 2 - Type 3 (qty 56) on Std Pallets 3 & 4 - Type 4 (qty 7), Type 5 (qty 9), Type 6 (qty 10), Type 7 (qty 18) all on Std Pallet 5 etc

The client requires a maximum of 48 boxes per standard palate although we can occasionally use a euro pallet of 54 boxes if required but not the preferred option and only used if necessary.

Variants cannot be split across pallets (unless their total is greater than 48 see Type 3)
Variants can share a pallet but only if the entirety of each variant is on the same pallet and not split over multiple pallets - see Types 4 -7
Pallets obviously can't be full but a minimum of 10 boxes per pallet or look to use Euro Pallet - see types 54 - 68 on spreadsheet and notes for reference only

The spreadsheet attached has my manual workings in column C for reference to the required result

I am struggling to workout either in formula or most likely javascript (novice at this too I'm afraid) a way to automatically calculate a pallet number based on the rules above - thanks in advance

 

[pecoflyer]

Cross post https://www.excelforum.com/excel-pr...omplicated-packing-list-help.html#post5897100

 

[vletm]

Monkeywrenchman​

As You have read from both Forum Rules:

• Cross-Posting. Generally, it is considered poor practice to cross post. That is to post the same question on several forums in the hope of getting a response quicker.
• If you do cross-post, please put that in your post.
• Also if you have cross-posted and get an Solution elsewhere, have the courtesy of posting the Solution here so other readers can learn from the answer also, as well as stopping people wasting their time on your answered question.

 

[Monkeywrenchman]

Sorry very new here and was unaware - are you able to assist with putting it in my post - sorry - yes I am just after the quickest answer as I appreciate it a tricky one... Thanks

 

[vletm]

Monkeywrenchman​

Did You reread Forum Rules?
... have You read #2 reply?

About Your I am just after the quickest answer as I appreciate it a tricky one
... The quickest should mean that You have had explained Your challenge clear.

Haven't You already got it ... column C for reference to the required result ?
If something else then ...
> what are inputs?
> where is the required result?

 

[Monkeywrenchman]

Monkeywrenchman​

Did You reread Forum Rules?
... have You read #2 reply?

About Your I am just after the quickest answer as I appreciate it a tricky one
... The quickest should mean that You have had explained Your challenge clear.

Haven't You already got it ... column C for reference to the required result ?
If something else then ...
> what are inputs?
> where is the required result?

Hi vletm

Sorry I can't find the rules are you able to post a link?

Regarding your questions column C and D are references to the required result column B is the data to evaluate (inputs)? these are calculated elsewhere and posted to this sheet from and Index match formula against the Variant type from column A - this is not the actual document just paste special values so as not to interfere with the main master doc...

cheers

 

[vletm]

Monkeywrenchman​

You can find it from

Chandoo.org Excel Forums - Become Awesome in Excel

Learn Excel, Charting Online - Discuss with Experts - Chandoo.org Excel Forum

chandoo.org

As You have checked New Users - Please Start Here



Your Regarding your questions ...
Please, reread Your original writing and #6 writing
Answer after that
> Is it clear for You?
> Could it be clear for someone else?

for one sheet - What can see after inputs?
for other sheet - What can see after solution?
... including - how should it do?

 

[Monkeywrenchman]

Hi vletm

I have read the New Users - Please Start Here thank you

I hope the spreadsheet speaks for itself

Regarding your questions column C and D are references to the required result. Column B is the data to evaluate (inputs)? as in the quantity of boxes required to pack the Variant (Column A) -these are calculated elsewhere and posted to this sheet from an Index match formula against the Variant type from column A - this is not the actual document just paste special values so as not to interfere with the main master doc...