How to calculate a number of boxes on pallet

Need some help with calculating the following in Excel.

I have boxes in a format like 19 x 25cm, to fit in pallet.


How can I calculate the total number of boxes on pallet size 80 x 120cm with boxes of 19x25cm.?

I need the highest result between 80/19 or 80/25 and as well for the other side 120/19 or 120/25.

Is there a way to draw in Excel a rectangle to show the size of the pallet and then small rectangles for the boxes with the size of each object?

Thanks for your help. Kind Regards

John

Answer
Answer

I've merged JL's and TasosK's examples into one workbook

Here is a link to the merged file in skydrive: DrawPackedPallet_v002a.xlsm

"Stay together, by staying apart"
Covid19
.
Computer Haiku
Chaos reigns within.
Reflect, repent, and reboot.
Order shall return.
.
ꙨပꙨ

7 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

Answer
Answer

Here's what I believe is my final cut at it all.  I've modified the code provided by TasosK to 'blend' better with my own process:

Neither delete layout processes will remove the TextBoxes now used as command buttons, and each only deletes the layout that was created by its process.  You don't have to select any cells for the TasosK delete layout to work properly - but you do still have to pick the starting cell for a new layout drawing.

You can mix or match measurements for box sizes/pallet size - they all get converted to the same units of measurement as required.  The 'scrap' space on the pallet is reported in the same unit of measure as based on the unit of measure for the boxes.

As I said, barring finding some significant bug or flaw in the logic, I think this is it for me on this one.

The file is here:

http://www.mediafire.com/view/zt15ej42u86hc97/excel-_DrawPackedPallet_v004.xlsm

and a version specifically for Excel 2003 - only the pallet drawing code is changed to work with that graphic engine.  The actual logic of calculating the layouts is unchanged:

http://www.mediafire.com/view/vaoidrdfwrnr5mv/OptimizedPalletPacking_v004_xl2003.xls

I am free because I know that I alone am morally responsible for everything I do. R.A. Heinlein

8 people were helped by this reply

·

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.

 
 

Question Info


Last updated June 30, 2020 Views 23,180 Applies to: