Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-11-2018, 03:59 AM
japonica42 japonica42 is offline Compare sizes of different boxes and pick the correct size to put another box in to! Windows XP Compare sizes of different boxes and pick the correct size to put another box in to! Office 2007
Novice
Compare sizes of different boxes and pick the correct size to put another box in to!
 
Join Date: Jan 2018
Posts: 3
japonica42 is on a distinguished road
Default Compare sizes of different boxes and pick the correct size to put another box in to!

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
File Type: xlsx Box problem.xlsx (8.8 KB, 210 views)
Reply With Quote
  #2  
Old 01-11-2018, 06:05 AM
NBVC's Avatar
NBVC NBVC is offline Compare sizes of different boxes and pick the correct size to put another box in to! Windows 10 Compare sizes of different boxes and pick the correct size to put another box in to! Office 2013
The Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 215
NBVC will become famous soon enoughNBVC will become famous soon enough
Default

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.
Reply With Quote
  #3  
Old 01-11-2018, 08:06 AM
japonica42 japonica42 is offline Compare sizes of different boxes and pick the correct size to put another box in to! Windows XP Compare sizes of different boxes and pick the correct size to put another box in to! Office 2007
Novice
Compare sizes of different boxes and pick the correct size to put another box in to!
 
Join Date: Jan 2018
Posts: 3
japonica42 is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 01-11-2018, 08:23 AM
NBVC's Avatar
NBVC NBVC is offline Compare sizes of different boxes and pick the correct size to put another box in to! Windows 10 Compare sizes of different boxes and pick the correct size to put another box in to! Office 2013
The Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 215
NBVC will become famous soon enoughNBVC will become famous soon enough
Default

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:
Code:
=Product(B2:D2)
, 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.
Reply With Quote
  #5  
Old 01-11-2018, 09:44 AM
japonica42 japonica42 is offline Compare sizes of different boxes and pick the correct size to put another box in to! Windows XP Compare sizes of different boxes and pick the correct size to put another box in to! Office 2007
Novice
Compare sizes of different boxes and pick the correct size to put another box in to!
 
Join Date: Jan 2018
Posts: 3
japonica42 is on a distinguished road
Default

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 😁
Reply With Quote
  #6  
Old 01-11-2018, 01:53 PM
NBVC's Avatar
NBVC NBVC is offline Compare sizes of different boxes and pick the correct size to put another box in to! Windows 10 Compare sizes of different boxes and pick the correct size to put another box in to! Office 2013
The Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 215
NBVC will become famous soon enoughNBVC will become famous soon enough
Default

You're welcome. Glad to save you all that time.
Reply With Quote
  #7  
Old 01-12-2018, 09:56 PM
ljtjr ljtjr is offline Compare sizes of different boxes and pick the correct size to put another box in to! Mac OS X Compare sizes of different boxes and pick the correct size to put another box in to! Office 2011 for Mac
Novice
 
Join Date: Jan 2018
Posts: 7
ljtjr is on a distinguished road
Default Box problem

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
File Type: xlsx Box problem.xlsx (28.2 KB, 145 views)

Last edited by ljtjr; 01-12-2018 at 10:02 PM. Reason: add spreadsheet
Reply With Quote
  #8  
Old 01-15-2018, 05:58 AM
NBVC's Avatar
NBVC NBVC is offline Compare sizes of different boxes and pick the correct size to put another box in to! Windows 10 Compare sizes of different boxes and pick the correct size to put another box in to! Office 2013
The Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 215
NBVC will become famous soon enoughNBVC will become famous soon enough
Default

I hope the OP can use your work, ljtjr. I like it.
Reply With Quote
  #9  
Old 01-15-2018, 08:26 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Compare sizes of different boxes and pick the correct size to put another box in to! Windows 7 64bit Compare sizes of different boxes and pick the correct size to put another box in to! Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

If NBVC is impressed this solution/thread is worth at least 5 stars !
__________________
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
Reply With Quote
  #10  
Old 02-26-2022, 10:00 AM
F1~1 F1~1 is offline Compare sizes of different boxes and pick the correct size to put another box in to! Windows 10 Compare sizes of different boxes and pick the correct size to put another box in to! Office 2019
Novice
 
Join Date: Feb 2022
Posts: 1
F1~1 is on a distinguished road
Default

Thank you, this is great very helpful.
Reply With Quote
  #11  
Old 06-20-2023, 11:06 AM
dstone dstone is offline Compare sizes of different boxes and pick the correct size to put another box in to! Windows Vista Compare sizes of different boxes and pick the correct size to put another box in to! Office 2016
Novice
 
Join Date: Jun 2023
Posts: 3
dstone is on a distinguished road
Default

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
File Type: xlsx Box_Problem_Multiple_Qty_Per_Box.xlsx (17.2 KB, 11 views)
Reply With Quote
  #12  
Old 06-20-2023, 10:59 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Compare sizes of different boxes and pick the correct size to put another box in to! Windows 10 Compare sizes of different boxes and pick the correct size to put another box in to! Office 2021
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Hi
please start a new thread eventually adding a link to this one. Thx
__________________
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
Reply With Quote
  #13  
Old 06-20-2023, 11:52 PM
dstone dstone is offline Compare sizes of different boxes and pick the correct size to put another box in to! Windows Vista Compare sizes of different boxes and pick the correct size to put another box in to! Office 2016
Novice
 
Join Date: Jun 2023
Posts: 3
dstone is on a distinguished road
Default New thread added, Thank you

https://www.msofficeforums.com/excel...tml#post175662
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Compare sizes of different boxes and pick the correct size to put another box in to! Word template: fixed size text boxes apophis Word 8 10-12-2015 01:48 AM
Compare sizes of different boxes and pick the correct size to put another box in to! How to correct the different font sizes of bullets in Word 2007 Mac265 Word 4 01-23-2015 11:27 AM
Pick up Style tool (eye dropper) ParreDL Word 7 03-20-2012 06:40 AM
Font size in dialog boxes nannycheryl Word 0 07-10-2010 09:26 AM
Compare sizes of different boxes and pick the correct size to put another box in to! Pick up data from Word documenta jmarin Excel 1 12-05-2008 02:31 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:59 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft