Hello,



I have a list of shipping boxes complete with their dimension. I also have a list of finished product packed in their own boxes! I have to chose which shipping box is the most sensible to use for each product - assuming one product per shipping box. It is easy enough choice to make manually - but I have LOTS to do and so want to know if Excel can make the decision for me! I've attached an example of the spreadsheet - I can organise columns by size and even chose max; min and median but I can't then figure out how to make Excel chose the right box! The products can be packed in any orientation.

Any help would be gratefully received!

Attached Files

Box problem.xlsx (8.8 KB, 230 views)

Try this formula:

It will find the first box with all dimensions equal to or larger than your specs.

=INDEX($A$2:$A$8,MATCH(1,INDEX(($B$2:$B$8>=F2)* ($C$2:$C$8>=G2)*($D$2:$D$8>=H2),0),0))

copied down

#N/A means no box can fit your specs.

Hello NBVC,

Than you so much for your reply. It is a brilliant formula and very nearly does everything I want from it. However, I want it with a cherry on top ;-)

Is there any way it can pick the smallest possible box overall? I can put in a volume equation for the product box and the shipping box if that helps at all.

Once again, thank you so much for your work so far

I was afraid you were going to follow up with that question

Ok first insert a column for volume in column E and enter volumn formula: , remove spaces around the colon and then copied down

Then try this Array* formula:

Assuming your inputs have shifted over a column, now in K2:

Code:

=INDEX($A$2:$A$8,MATCH(1,($B$2:$B$8>=G2)*($C$2:$C$8>=H2)*($D$2:$D$8>=I2)*($E$2:$E$8=(MIN(IF(($B$2:$B$8>=G2)*($C$2:$C$8>=H2)*($D$2:$D$8>=I2),$E$2:$E$8)))),0))

*This formula must be confirmed with CTRL+SHIFT+ENTER not just ENTER before copying down.

You’re a star! Thank you so much for this. You’ve saved me hours of work having to manually compare product boxes to shipping boxes 🤗 I really appreciate you taking the time to sort this 😁

You're welcome. Glad to save you all that time.

I am dazzled by NBVC's facility with the INDEX and MATCH functions. As a novice I find them confusing. He or she provided a really good formula (algorithm) for your box problem. I tried out both his formulas and they worked well, especially the second one that better optimizes by adding the volume criterion. I do have one suggestion that will optimize the result somewhat better. It uses NBVC's solution and the fact that orientation of the products in the boxes is arbitrary, which it seems NBVC has not invoked.

Rather than testing box width versus product width, height versus height, etc., it works better if for each box and product the test is short length versus short length, long length versus long length, and mid length versus mid length. Just rearrange the dimension arrays so that the short, mid, and long dimensions are in different columns and then apply NBVC's formula as before. The attached spreadsheet should make clear what I'm stumbling to explain.

The four solutions on the spreadsheet are to examine the consequences of changing the array orders as combinations of order reversals. I used your original data (yellow highlight) and added 2 box sizes to avoid fit fails (#N/A). The rearranged dimensions (min, mid, max) and the corresponding results are shaded blue. For this small test case, the best result was with the min-max,v algorithm (alg4) for which the average box filling was about 82%, thus allowing smaller boxes on average and the least packing filler.

Note that if both arrays are in ascending order by volume, NBVC's two algorithms (with and without the volume criterion) give the same results. Otherwise, alg1 did its best (55% avg fill) with box volumes in ascending order and product volumes in descending order. Worst of all is with the box volumes in descending order because all product sizes are matched to the largest box as it's encountered by the algorithm first.

I pass this on to you on the chance you may not have worked it out yourself. I have no interest in putting things in boxes, but I got into this to learn how experts use the INDEX and MATCH functions.

Attached Files

Box problem.xlsx (28.2 KB, 163 views)

I hope the OP can use your work, ljtjr. I like it.

If NBVC is impressed this solution/thread is worth at least 5 stars !

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Thank you, this is great very helpful.

Hey Everyone,
I'm very impressed by this initial thread in 2018. I haven't been able to find a source within our company to help with this...

I have a similar and more complex problem.

I would like to find the best fit box that would not go over 50% of the current box quantity, give a max head height of .5", give a volume utilization of 90% and return the best fit box to column "H".

My products are cylinders, if I can get more in a box by staggering them in a honey comb pattern then I would prefer to do that, while other items may fit better lined up. The products do sit on the flat side, so the diameter measurement in column "A" can be used as length and width.

We have over 40,000 items internally, so it would be a great help if I can apply an array to help with this.

Attached Files

Box_Problem_Multiple_Qty_Per_Box.xlsx (17.2 KB, 23 views)

Hi
please start a new thread eventually adding a link to this one. Thx

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

https://www.msofficeforums.com/excel...tml#post175662