Best box

[Morglisn]

Thanks for any help in advance!!

I'm fairly new to using VBA, and hope you guys can help me. I work for a manufacturing facility, and I'm trying to update our estimating spreadsheet by supplying some automated functions.

We use eight different box sizes for packaging our product, all at various costs. I'm trying to supply an input area for the estimator to enter the three dimensions of the part to be packaged, then have the software calculate the total number of pieces than can fit in each box size, then the most cost-effective box to use.

I've managed to get it to work, to a point. My problem is, this is only with the part turned one way from four possible positions within the box. This leaves empty space in the box, in which a few more parts might be packaged (turned in a different direction). It's a bit like packing books. A row fits fine lined vertically along the bottom, but you can always fit two-three across the top horizontally.

Can anyone think of a way to calculate that empty space, and determine how many pieces can fit? Or is there an easier way of doing all this that I've completely missed?

Thanks!!

 

[MechEng13]

If your company uses a 3d solids MCAD system, they could utilize that to find the best fit for pieces in a box...

Not much help but a thought.

Alan M. Etzkorn
Hoffco/Comet Industries Inc.

http://www.hoffcocomet.com

 

[Morglisn]

We use Solidworks for our drafting, but we're looking for something automated, where our estimators don't have to sit down w/ a calculator or a drafting program to figure out the best packaging solution.

 

[MechEng13]

Well...I can't say I know of any way then...if you get anything...I'd be interested in knowing as well. We have something like 4000 finished goods part numbers and that would be something handy for us to have.

Alan M. Etzkorn
Hoffco/Comet Industries Inc.

http://www.hoffcocomet.com

 

[Morglisn]

I've got it working for turning the part ONE way within the box, and determining which of our eight box sizes would be cheapest, but... for calculating the empty space into the equation, I'm getting beaten up. Badly.

I've got another idea, though, I'm working on it now.. I should know something within a day or three.

 

[MechEng13]

That would be nice if we only had 8 boxes....I couldn't even count how many we have...plus the variety of returnables either our own or customers...can be a mess.

Let me know how it works out.

Alan M. Etzkorn
Hoffco/Comet Industries Inc.

http://www.hoffcocomet.com

 

[Morglisn]

Well, I never did get the empty space accounted for, but management decided it was negligible (we're talking MAYBE one extra part in each box). We did, however, put in a few different material types along with their compression ratios, to account for our workers squeezing parts into each box (the pieces don't deform).

I don't know how to attach a file to the forum, though.

 

[MechEng13]

I don't know how to post in the forum either...but if your willing to share, my email is posted in my profile, you can send it there. I'm trying to learn more about the power of using Excel or any spreadsheet package, I have a lot of potential uses in what I do.

Alan M. Etzkorn
Hoffco/Comet Industries Inc.

http://www.hoffcocomet.com

 

[prex]

This is one of those problems that might appear simple at first sight, but that requires a considerable programming effort, even under the simplifying condition, assumed by both posters, that only pieces of the same size will have to fit into each box.
If I had to start programming (but it's not my intention) I would try first a brute force algorithm, that is the program tries many combinations, chosen in a given sequence or randomly generated, stopping when a sufficiently good result is obtained or a given maximum time has elapsed.
An easier approach would be an extension of what already done by Morglisn:
first fill the box as much as possible with boxes turned into one of (three or four?) possible positions, then for each of these tentatives continue by filling the remaining (three) spaces, taken one at a time as if it was a box, with more (or zero) boxes.
These procedure is not perfect, as the pieces could be alternately turned into different positions to get the best packing density. However it is also possible to determine the maximum possible number of pieces that could fit (by a simple ratio of volumes) so as to decide whether the result obtained so far is sufficiently good.

prex

http://www.xcalcs.com

Online tools for structural design

 

[smcadman]

Something you might look into is if you Google "cut optimizer", "sheet optimizer", or "nesting software", you come with several programs that are used for cutting sheetmetal or plywood sheets. These are only 2D/flat, meaning they do not handle the z axis, but one of these could help or lead you into the right direction. I know these were not designed for packing a truck or what have you, but they could serve a dual purpose, and some are free.
Just a thought.

Flores

 

[Mutt]

Just for kicks tried simple approach of filling up box with inserts in various positions and then filling in the blanks spaces with inserts in other positions. Seems to work but not rig checked. Could be converted to Vbasic function if approach works. Have look at spreadsheet on

http://www.langleyventures.co.za/boxes.xls

Is only for 4 boxes at moment but easily extended to 8 or more.

Have mercy if you see logic bugs!! Was a quickie programme.

I would be interested to know how compared to morlisns answers and if my answers are actually correct by testing in reality!

 

[mgp]

I posted a question some time ago about optimising pipe cutting and I got a reply which might help you also.

Your problem is called a " 3 dimensional bin packing problem"

The "bin packing problem" may also be 1-dimensional (e.g. for pipe cutting which I was after in my thread) or 2 dimensional (e.g. for cutting shapes out of a plate)

Here's the reply I got:

"What you are referring to is a very common problem and referred as "one dimensional bin packing problem/ algorithm". Do an internet search, you will fing both the algorithm as well as some programs. This name apparently came from packing industry where they wanted to optimise number of differenly sized bins for packing odd sized pipes and this is very relevent for you also.
Hope it helps.
flame"

I also hope this helps

Regards
Mogens