1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Loading a container

Discussion in 'Ask an Excel Question' started by Yuko, Mar 4, 2019.

  1. Yuko

    Yuko New Member

    Messages:
    11
    Hello,

    I'm a Newbee in this forum.

    Is there someone who can help me writing the formula to automate the calculation.
    For the moment I'm doing this manually and I want to do it automatically via Excell.

    Yuko

    Attached Files:

  2. vletm

    vletm Excel Ninja

    Messages:
    4,918
    Yuko
    Where comes 269cm?
    What happen to that 7cm (65-58)?
    Why some fonts are red?
    Is there always three (F,G,H) columns for those values?
    How to work with those A20 & A21?
    ... many things are possible
  3. Yuko

    Yuko New Member

    Messages:
    11
    Dear vletm,

    first of all thanks for your reply. I'll try to explain as good as possible the situation to make it clear to you. It feels to me like a solution is possible, it makes me nervous, because I didn't believe in it anymore.
    I'll attache another document, called Summary, maybe it explains better what I try to achieve.

    1.Where comes 269cm? This is the height of the inside dimension of the container
    2. What happen to that 7cm (65-58)?If you stack 2 x 102 + 1 x 58, the total height is 262cm. So the left over dimension is 7cm, which remains as air in the container
    3. Why some fonts are red?To show what happens when the boxes move from the remaining column to the loaded column and in the container table the height is added and in the right table it indicates which boxes are changed. This is just for this case to show the action. In realtime it won't be written in RED.
    4. Is there always three (F,G,H) columns for those values? Yes, we Always have 3 boxes on 1 row
    5. How to work with those A20 & A21?In case a pallet or loose material is in the project, but this can be handled manually, not to worry about this too much.
    ... many things are possible

    Attached Files:

  4. vletm

    vletm Excel Ninja

    Messages:
    4,918
    Yuko
    ... many things are possible
    ... gotta think positive
    Test this the 1st version:
    There is a [button] ... press it!
    There are two 'options'
    - animation (cell E18 is bold) or faster (cell E18 isn't bold)

    I didn't test other 'Summary'-values ... You'll do it testings.
    There could be some '[not so] interesting' features
    ... just let me know.

    Attached Files:

  5. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    709
    I tried Solver's Simplex method and got a solution for 30 box stacks but failed for 29. The objective was to minimise the height of any remaining boxes.

    upload_2019-3-5_23-13-9.png
  6. Yuko

    Yuko New Member

    Messages:
    11
    Dear vletm,

    that's exactly what I had in mind.That's great WOW.
    I runned some tests and it was doing exactly what I was looking for.
    This ball is running, what a great feeling, now we can start running it and step by step, maybe a bit more fine tuning.
    Suddenly I came to this situation, see here below.
    Anyhow already many thanks. Now I can link my 3D stacked Column Chart and I have a dynamic loading document.

    What I didn't understand yet is the cell E18 BOLD or not, but I was already so happy that it is working.
    It is a great big step forward for me.

    Is there a possibility to become the way how you did it, so I can change to different containers, because not all containers haveth sme height of 269cm. I can use this also for calculating the loading of a truck.
    Actually this program has a double function.
    1. In the sales fase we need to calculate the volume of the products for the project, to calculate a transportprize.
    2. Once the materials for the project are ready, tere is Always some difference between what was quoted and what is produced. So we can recalculate the volume and show our warehouse people how to load the containers.

    Thanks a lot.
    upload_2019-3-6_6-31-55.png
  7. vletm

    vletm Excel Ninja

    Messages:
    4,918
    Yuko
    As written, the 1st version.
    cell E18 bold or not - the way how those results would come (slower/faster).

    Height is possible to adjust. I can make a sample.

    Double Function
    ... send needed details
    ... eg for volume, it needs also need more than height or how?
    ... many things are possible.

    >> There were some unwanted features ... some has modified <<

    Attached Files:

  8. Yuko

    Yuko New Member

    Messages:
    11
    Dear vletm,

    Double Function What I meaned is we can use for sales and Logistics purpose, but for both cases the products are the same. Only the qty of the boxes can variate a bit, depending on how the materials will be packed in production. But we stay with our 8 or 10 boxsizes as I mentioned before. So no worries about that.

    Hereby attached the 4 possibilities we are using.
    It is an older document I was using to prepare my worksheet.
    The tables are not correct anymore, but the pics of the different containers anddmensions ae still OK.


    ... send needed details
    ... eg for volume, it needs also need more than height or how?
    ... many things are possible.

    Attached Files:

  9. vletm

    vletm Excel Ninja

    Messages:
    4,918
    Yuko
    Yes ...
    Now, focus ...
    There can be MAX 10 boxsizes.
    I modified layouts - gotta be fixed.
    1) Modify 'Summary'-values B21:B30 as needed
    2) Modify 'Height'-value F19 as needed
    3) If F19 is bolded then slower else faster mode
    4) Press above 'photo' to run calculation.

    Ideas? - Questions? ...

    Attached Files:

    Thomas Kuriakose and sathishsusa like this.
  10. Yuko

    Yuko New Member

    Messages:
    11
    Dear vletm,

    when the last row is full, and there are still boxes left over.
    Is there a possibility to mention I need a 2nd container.
    Sometimes it happens we need 10 or more containers for 1 project.
    It even happens that we need sometimes 5 containers all with only the same product, so 5 containers with only 1 type of boxes.
    Now when I have more than 18 rows in a 40" container the calculation is just going on, actually outside the container.
    For a truck it are 21 rows and for a 20" container 9 rows.
    It would be more realistic when there is a sign that tells me that a 2nd or 3rd container is needed.
    Making a % filling in each cell is another approach of visualisation.
    It's a nice idea aswell.
  11. vletm

    vletm Excel Ninja

    Messages:
    4,918
    Screenshot 2019-03-06 at 20.50.47.png Truck has 105 rows ...hmm?
    20" has 45row ... hmm?
    Explain...
  12. Yuko

    Yuko New Member

    Messages:
    11
    Dear vletm,

    how do you get 150 rows??
    When you devide the inside length of the truck by the width of a box, which is always the same, 13,6m / 0,64m = 21,25 ===> 21 rows
    For a 40" container it is 12,031m / 0,64m = 18,79 ===> 18 rows
    For a 20" container it is 5,89m / 0,64m = 9,20 ===> 9 rows
  13. vletm

    vletm Excel Ninja

    Messages:
    4,918
    I can 'know' only details which You've written!
    Maybe there could be single tulips or something ;)
    'Truck'-sheet:
    below snapshot from row 21 to71 -- already 31 rows and that heavier line border continues to row 125 ... totally 105 rows!
    Screenshot 2019-03-06 at 21.38.05.png

    Of course ... that range could split like below ...
    if those cells presents something .. now 21 rows?
    Screenshot 2019-03-06 at 21.43.55.png
    ... then there can see 'many' containers....
  14. Yuko

    Yuko New Member

    Messages:
    11
    Dear vletm,


    sorry if I came over rude.
    It wasn't meant like that.
    Now I see why there is the misunderstanding.
    Because I used the old truck table, where I was using for 1 row 15 cells.
    I wanted to use each cell as 1 position of a box. That results in this 105 rows.
    I was trying something out to get my solution set up.
    Sorry I mislead you with this.
    Hereby attached a sample of what happens when there are more boxes than the container can have.
    When I use a High Cube Container(269) there are remaining just a few boxes.
    When I use a Dry Van 40" you see the result, the counting contineous in the same table.
    I still need to update the axis of the chart to the height 270,269 or 239 depending on wich transport unit I use, t haveit as real as possible.
    I'm going to integrate this document into my Cockpit.

    Just an idea, because it is not possible in the 3D stack chart.
    I see you created the %filling of each cell. Is it possible to link the color of each box used in this cell. That's what I wanted to do in the3D chart but that is not possible. It is only possible in a 2D Stack Chart, but than I only see 1 row and I want to see the 3 rows in the container or truck.

    It is working fine already, don't worry

    Attached Files:

  15. vletm

    vletm Excel Ninja

    Messages:
    4,918
    Yuko
    Why You don't want to use that other 'older' file?
    There are more possibilities!
    eg as my previous snapshot shows or how?
    ... then You'll have more preset 'containers' ready to use!
    ... and it works better!

    '3D stack' chart is possible ... (it can do eg manually)
    Do You remember? ... many things are possible.

    Even every single box can have those own colors too.
    One challenge is 'inside' boxes (= boxes which are 'center')
    if 'wireboxes' are okay then even those could see
    ... if there could left spaces between boxes...
  16. Yuko

    Yuko New Member

    Messages:
    11
    Dear vletm,

    hereby something I found on the internet, already some time ago.
    The problem is that it doesn't work and I don't understand how it works.
    But the 4. visualization I liked. It need some seconds to open.
    Actually due to this I wanted to have it in my worksheet.
    But because it didn't work as I wanted, I started to look for something else.
    Which I found now with your excellent help and I'm happy with it.
    But if we can upgrade it or make it better with another solution, I'm always open for it to see.
    When I look closer to it, it looks like you are applying the same program.

    Attached Files:

  17. vletm

    vletm Excel Ninja

    Messages:
    4,918
    Yuko ...hmm?
    Someone has copy&pasted formulas ... no way!
    ... and that won't work well ... at all!
    So call - visualization ... there will be same challenge,
    which I wrote ... if there are more 'boxes' than those two then mess!
    Would it more useful to see eg 'boxes' row-by-row'?
    You gotta try to image --- what? and how would it look like?
    You know rubic-cube
    ... how many 'boxes' do it have?
    ... how useful would something like that be?
    How would You use something? ... what to see? ... and so on...
    These are not any one-minute-codings!
  18. Yuko

    Yuko New Member

    Messages:
    11
    Dear vletm,

    maybe we were playing a bit around and letting our fantasies free.
    For me it is already more than OK like it is now.
    It is strange that last night, all worked fine with my 3D graph included in my document. And today nothing is working anymore.
    I keep it with my 3D Stack graph, that's OK.
    Only if we can get in the sign that when the container is full it switch to another container, that would be great.
    It is easy to switch from a High Cube to a Dry Van simply by changing the inside height, that works great.
    In the beginning I supposed to have it automatically, but now I just prepare the materiallist and when all is ready, I just push the button an run the calculation and the visualisation with the 3D Stack Graph. That's more than OK for me, because it is working.
    Thanks a lot for that.
    In case you want to play with it more, to make it even more attractive, I'm always cureous to see the result .
    I saw that the document is read only, and I think that's the reason why it doesn'work anymore. Because in the forum it works and when I copy it to my worksheet, than it doesn't do anything anymore.
    That's a pitty.
    Also what I'm missing is I can't see how you did it, only I see it working. Actually this is OK, but when later on something isn't running anymore as I want, I can't fix it.
    This problem has it something to do with the size 250 as heightof the axis of the graph??

    Attached Files:

  19. vletm

    vletm Excel Ninja

    Messages:
    4,918
    Yuko
    3D ... it's seems to be soon(er or later) ready ...
    I found way to do it ... still testing...
    Now, only one fixed height layer, but with colors ... soon more details.
    Screenshot 2019-03-07 at 12.29.01.png

    As I've written ...
    I would use that other version, it would work 'better'!
    I even tried to test those values and ...
    Screenshot 2019-03-07 at 12.37.00.png
    Both files has some checks
    eg is that possible to solve ...!
  20. vletm

    vletm Excel Ninja

    Messages:
    4,918
    Yuko
    Check 'Truck'-sheet
    Press [ CUBE ]-button ...
    This works better ... or how?
    ... and ...
    I found one unwanted feature too.
    Seems that those calculations have to check manually ... by YOU.

    Attached Files:

    Last edited: Mar 7, 2019
    Thomas Kuriakose likes this.
  21. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    709
    From a programming viewpoint does it really matter that the stacks form a 3x18 arrangement within the container. Wouldn't simply designing stacks within the limit of 54 do the same job?

    When it comes to the 3D layout within the container the 3D charts give some overall impression but the actual height and gap (paid for but unused) cannot be seen with any degree of precision. A stacked chart showing the individual boxes in each stack using a set of key colours associated with each box type would convey more information and could include the gap and its height. The stacks could, if needed, be grouped in threes with missing category in-between to give some impression of the overall layout.

    By the way, in post 5, I had to remove the unused box types because the free version of solver does not go beyond 200 variables. What the optimisation also showed was that a few specific stacking sequences recurred throughout the packing sequence and, possibly, have been used as a basis for the packing plan.
  22. Yuko

    Yuko New Member

    Messages:
    11
    Thank you Mr Bartholomew,

    for your comments. But for me the most important issue is a visualisation of the loading. In our case there are always gaps between the boxes, because we only have 8 - 10 boxsizes. For our purpose it is excellent work.
    It matters when you have 50 - 100 different boxsizes and you want to fill the container or truck as efficient as possible. Than you are looking for the last mm of space. This is not the case with our products.
    Once I had a customer complaining that we told him to use 1 x 40" + 1 x 20" container for the shipment. He told me that the volume of all the boxes was a little less than 1 x 40" container. I told him that he was correct, when our product was water. But taking into account the boxsizes it was not possible to get them all in 1 x 40", without squeezing the boxes.
    Peter Bartholomew likes this.
  23. Yuko

    Yuko New Member

    Messages:
    11

    Dear vletm,

    great job, amazing what you created. Unbelievable.
    I'm feeling like a young kid standing in a candybar, that can't believe his eyes what he all sees.
    Is it possible to turn the graph 90° counterclockwise, so the boxes are not through the text of the loading.
    And when it is possible to reduce the size of the graph to keep it within the space of the 21 rows, that would be awesome. So each truc has it's own graph.
    When I've a project and need 2,7 truck, the graph will show it me immediately.
    I added 1 column " loadmeter". Sometimes we only need just a part of a truck and by this column when we do the calculation and the partial filling. I see immediately how many loadingmeters are needed for the shipment. This info I need to tell the transportcompany to get the prize for the shipment.
    Also the last boxes of a project, might not be a full load. So I need to know how many loading meters I need in the truck and I get this info instantly.
    The whole purpose of all the work we did sofar, is just to tell me how many loadingmeters I need in a truck orhow many containers I need to order.
    And by this tool it works perfect and super quick.

    Attached Files:

Share This Page