+ Reply to Thread
Results 1 to 2 of 2

Can Excel solver or other formulas optimize an inventory of empty boxes based on size?

  1. #1
    Registered User
    Join Date
    07-29-2016
    Location
    Seabrook Tx
    MS-Off Ver
    2016
    Posts
    1

    Can Excel solver or other formulas optimize an inventory of empty boxes based on size?

    I have 200+ empty boxes and have entered their size values, both internal and external into a spreadsheet. I was hoping to find a way to find the optimal nesting solution for minimal overall volume. I have done some work manually and I can do some sorting, hiding, and calculations, etc with my data set. But I'm not advanced enough to know what other tools are out there. Before I keep working on it, I thought I'd post the simplified data set and see if anyone had any suggestions to add to the sheet or walk me through a solution.

    The basis premise is that I need the program to take external dimensions of each box and find the appropriate space in another box's internal dimensions, realizing that L W and H can all be interchanged and that unused space in a large box can be used by a second or third box in the nesting process?

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Can Excel solver or other formulas optimize an inventory of empty boxes based on size?

    This is not an optimizer but it is close
    first calculate the volume on each (I did in column 8)
    sort the list by the volume (descending order) biggest on top
    then run the macro

    Column 8 will reference into which box the referred box goes into
    Column 9 will tell wich box does the referred box contains
    Column 10-12 will display how much space is left (theoretically)

    The reason it is not an optimizer is:

    1. It does not consider any rotation of the boxes (sideways maybe)
    2. There is only one run coded

    Please Login or Register  to view this content.
    Last edited by rcm; 07-29-2016 at 06:09 PM. Reason: clarity

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Using Solver/OpenSolver to optimize NORMDIST values (Fantasy Baseball)
    By jackries in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-11-2016, 01:52 PM
  2. Using Solver/OpenSolver to optimize NORMDIST values (Fantasy Baseball)
    By jackries in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-08-2016, 03:52 PM
  3. Extreme Challenge : Solver to optimize a perfect combination/blend of supplier
    By chris1089 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-07-2015, 03:23 PM
  4. [SOLVED] How to optimize cost and material with formula, Solver or any method
    By jackson_hollon in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 11-29-2014, 12:58 PM
  5. Delete Empty Rows of Table And Optimize Code
    By snapfade in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-14-2013, 04:56 PM
  6. Replies: 0
    Last Post: 06-18-2012, 06:35 AM
  7. Optimize without using Solver
    By rint412 in forum Excel General
    Replies: 2
    Last Post: 06-06-2012, 02:23 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1