Complex - Have Excel Tell me What Packaging to Use Based on Certain Item Qty

[fulfillmgt]

Hi guys - I have a complex situation. I need excel to tell me what packaging (3 types) to use based on the quantity of 4 different items.

Each type of packaging contains different "shapes" in which we place our items. We call these palletts.

Each item is of different size and this affects which shape/pallett they will/ can be placed in.

http://imgur.com/dy0fr3t

In spreadsheet 1, I list the item quantity and in the subsequent columns it iwll tell me the percent of area taken up per shape. The QTY column changes depending on what the order will hold and as such, is changed manually by the user.Ex: 4 of item 1 will take 2 small squares to pack.

In spreadsheet 2, it details how many shapes exist in each pallett. Ex: Pallett 3 contains 1 large square and 1 column, but no small squares.

Spreadhseet 3 details the area in each shape/

Spreadsheet 4 details the max number of items a shape can hold, per type.

Below that are mockups of what each pallett looks like via a birdeye's view.

Please let me know if I should provide anymore details and I appreciate ANY help in the matter. Im dyin'!

Thanks!

 

[nick323f]

hi there,

I've tried to understand you puzzle a little. I think there is a rule that you've not explained:
So a "large column" can hold up to 11 "item 1"'s or up to 2 "item 2"'s, yes?
so if you put "4 "Item 1"'s into a column does that mean there is enough space left for 1 "Item 2"?
i.e. is this purely area driven?

if its purely area driven then just add up the total area of the order and look at the total area available for each pallet until you find a pallet with more area than the order area. but that seems too simple so I think Ive missed something?

I think your solution would be to write a set of rules that compares the order content with pallet 1 first, if this doesn't fit then compare to pallet 2, if this doesn't fit then compare to pallet 3.

 

[fulfillmgt]

hi there,

I've tried to understand you puzzle a little. I think there is a rule that you've not explained:
So a "large column" can hold up to 11 "item 1"'s or up to 2 "item 2"'s, yes?
so if you put "4 "Item 1"'s into a column does that mean there is enough space left for 1 "Item 2"?
i.e. is this purely area driven?

Yes, you are correct. Apologies for not explaining that well enough!

 

[nick323f]

OK then you want to find the total area for the order you are working with:
cell D6 enter =SUM(D2:D5)

then find which pallet total area is less than this:
=if(D6<G8,"Pallet1",if(D6<G9,"Pallet2",if(D6<G10,"Pallet3","Too much for a single pallet")))

Perhaps I've missed something?

 

[fulfillmgt]

<g8,"pallet1",if(d6<g9,"pallet2",if(d6<g10,"pallet3","too much="" for="" a="" single="" pallet")))
No, you havent. The whole thing is very complex and I do not think that what my superiors want is feasible through Excel.

I really appreciate your help though.</g8,"pallet1",if(d6<g9,"pallet2",if(d6<g10,"pallet3","too>

 

[nick323f]

=if(D6<g8,"pallet1",if(d6<g9,"pallet2",if(d6<g10,"pallet3","too big="" for="" a="" single="" pallet")))[="" php]<="" html=""></g8,"pallet1",if(d6<g9,"pallet2",if(d6<g10,"pallet3","too>

 

[nick323f]

=if(D6 is less than G8,"Pallet1",if(D6 is less than G9,"Pallet2",if(D6 is less than G10,"Pallet3","too big for a single pallet")))

sorry I'm struggling with the forum editor, I cant use the less than symbol it seems to make all the text after it disappear?the formula above but replace the words less than with the symbol above the comma (on a UK keyboard)