Add right value's and cut- paste it together to use the optimal space of a container

[Mahir Yagimli]

Hi Everyone,

This question I have already posted to vijay per e-mail. So I'd like to mention that this is a cross post.

Here is a tough one at least for me.

In my business we got a list of different stones with m3 and tons. Now many times we have to check which stones can solely be loaded into one container and which one we have to add together.

There are two conditions:

The added up length of the stone shall not exceed 5,80 m.
the added up weight of the stone shall not exceed 26,5 tons.

Once I have found the stones, which have an added value of <=26,5 tons, I put them together in a line. Ofcourse, only if the added length does not exceed 5,80 m. On the very right side I merge the amount of empty cells to write the container number(a serial number).
Now I thought is it possible to do this with a macro?

I'd be helpful for any tips and hints.

What I did, is that I used first conditional formatting to order the sizes of stones and then filter them according to their formation. Helps me to get an overview, but it's not a solution yet.

Later I tried to copy the tons columns and paste it right beside it. Then I sorted the whole table from the smallest value at tons to the largest and with the copy cells I sorted solely from the largest to the the smallest value. Then I added the tons values in a third column of tons and filtered it conditionally with only <=26,5 . The weakness of this method is I have to put cut and paste the rows together and also it is not an optimal solution.

The goal is to use the space in the container as good as possible.

For your referrence I put one table into the attachment.


Thanks to all that I have bothered with this question.

Greetings from turkey.

Mahir

 

[Luke M]

This is actually a pretty complex type problem, trying to find optimum schedule/solution for different sizes. One quick question before trying to tackle this, what to do with stones that have a length > 5.8m. In your example, rows 55:69 appear to be too long.

 

[NARAYANK991]

Hi Mahir ,

Which of EN , BOY , YUKSEKLIK is the length ?

Secondly , why is the length the only constraint ? Why not a limit on the width and height ? Or are the stones not to be kept one on top of the other ?

Narayan

 

[Mahir Yagimli]

@ Luke: I know it is a very complex issue. Nevertheless, if you can find something or give me a tip. Hopefully we can find a close or even near solution. Added Stones that have a value of 5,80 m length should be marked as false or not added or error.

@Narayan:Good question. Sorry, that is my fault. I should have translate it. Boy stands for Length. En for width and Yukseklik for height.

Regarding to your second question: you've guessed already they are not to be kept on top of each other that would explain why there is no need to limit the height and for the width there is also no need since the stones normally do no exceed the limit of the container. But ofcourse there are the dimensions of the Container itself. The size of the container would be:

length= 5,89 m - thus limit 5,80
width= 2,35 m - thus the limit would be - 10 cm 2,25 m
height= 2,39 m - thus the limit would be 2,29 m

I want to thank you both for trying to help me with this and spending your time and effort.

Have a nice day!

 

[Debraj]

Hi Mahir,

Welcome to the forum..

If you want something you've never had, you need to do something you've never done...

So first start reading those material..
Found a lot of algo.. but finally ends with this simplified 3 page.. & 3 pdf..

http://cgi.csc.liv.ac.uk/~epa/surveyhtml.html
http://www.blackpawn.com/texts/lightmaps/
http://www.codeproject.com/Articles/210979/Fast-optimizing-rectangle-packing-algorithm-for-bu

http://www.ijcai.org/papers09/Papers/IJCAI09-092.pdf
http://www.cs.ukzn.ac.za/publications/erick_dube_507-034.pdf
http://clb.demon.fi/files/RectangleBinPack.pdf

But, query is looking very interesting..
I still not able to realize which one is WEIGHT.. still..
here is my first draft..
If I am going in right side.. we can create a macro for this also..

Please have a look and confirm..

 

[NARAYANK991]

Hi Mahir ,

Is the example you have given a realistic example , or is it just a made-up example ?

If it is realistic , as a first step , we can go through the following steps , but before that , can you confirm one point ?

The total weight of the stones is 731.87 tons ; given that each container can take a maximum of 26.5 tons , is it correct that we need to have at least 28 containers ?

If so , then we need to see how close we can come to this optimal number.

1. Starting from the heaviest stone , see if the individual stone's weight is greater than or equal to the cutoff weight of 26.5 tons ; if yes , then that stone alone goes into a container. Is this correct ?

2. If not , combine this stone with the lightest available stone , and see if the combined total is greater than the cutoff weight of 26.5 tons ; if yes , then the heavy stone alone goes into a container. Is this correct ?

3. If not , combine the heavy stone with the next lightest available stone , and repeat the check. If at any stage , the heavy stone + Stone A is greater than 26.5 , but the heavy stone + Stone B is less than 26.5 , then the combination of the two stones goes into another container.

4. As each stone or combination of stones is taken into a container , its data is removed from the table.

5. This process continues till we find that the combination of every two stones is less than 26.5 tons ; now we go on to a combination of 3 stones , and repeat the entire process once more.

The easy thing about this is that we can use VBA ; if you confirm that your example data is realistic enough , I can try coding the above procedure , and we can see how optimal the resulting solution is.

If you do the entire exercise manually , what is the number of containers you get ?

Narayan

 

[Mahir Yagimli]

@Debraj: Thank you for precious advice. My goal was to learn something from the experts here and get some help. Great Links that you've send me. I'll honestly will work myself through. The weight column is under the header tons. The other tons1 and tons2 headers are just to help me to get an overview. I tried to find a solution with conditional formatting, that obviously didn't work. Your table I have to study after work. Thank you for your time and effort you've put in.

@Narayank: All Good questions.

To your first question: This I have took from a realistic example, but for me it should be just an example used to learn.

The total weight of the stone are 731,87 tons, that's right. Then we should at least have 27 containers.

to 1. First we check the individual weights of the stones, whether they >=26,5 tons. If they exceed the Max ton, then they are to heavy and can thus be marked red. If they are with in a range of 25- 26,5 tons, then they can be loaded solely into one container. (why the range starts at 25, the reason is there is normal no stone under 1,5 tons).

to 2. Correct

to 3. That's right. Additionaly we have to check the length of the added stones. The limit is 5,80 m. If they exceed this length, they can not be loaded together into one container.

to 4. Once we have found a combination e.g. A + B+ C= then we cut the rows and paste them in a line and merge three cells with the text "Container 1".

to 5. Correct, but still we have to check the length limit of 5,80 m.

Normally we calculate with an average of 25 tons to get the estimated number of containers that are to be loaded. In our case that would be 29 containers.

Thank you Narayank for your great help and the good questions. Now I got a better picture of the whole procedure.

To all of you have a nice day!

 

[Mahir Yagimli]

@NARAYANK991 : I did it now manually and indeed I got 29 Container's. What I did was to subtract the given stone weight with 26,5 and then I was looking for the nearest weight to add. After I have found a weight that could be a fit, I check the added length. If the length is Ok, then I cut the rows and put them together in one line in another sheet. If have more then one stone to add, then I do the same procedure with every stone that I possibly could add.

For your referrence I've uploaded the table. the first sheet is my checksheet the second sheet ("sayfa3") is the result of the container list. There was a sheet called "sayfa1", too, but I have deleted that one, after I have cut and paste all the values into sheet("sayfa3").

Greetings and hopefully that might be answering your questions.

 

[NARAYANK991]

Hi Mahir ,

I have not yet gone through your file , since I was working on trying to write a macro to carry out the procedure I had described in an earlier post. I think the uploaded file will show you what this macro does.

On the main data page , I have listed the remaining stones which have not been taken care of by the macro ; given the few numbers , I think you can manually allot them to 2 containers , thus giving the optimal 29.

Can you try out the macro with other data ? I have defined one named range , which is just a convenience , not a necessity.

I have not put in any check for the length , since the maximum length of any stone in your workbook is only 1.80 m , and even with 3 stones together , we will not cross the limit of 5.80 m.

Narayan

 

[Mahir Yagimli]

Hello Narayank,

Thank you very much. That is fantastic! The Macro is working.

The only thing is I have troubles to implement your excel formula to another table.

I have tried to type it, but somewhere I did a mistake.

I have typed:

= Eğer(Eyoksa(Kaçıncı(table1[[#bu satır];[Musteri no]];sayfa4!$B$2:$B$61;0));table1[#bu satır];[tons]];"")

That's how I saw the formula in your example. I have changed sheets22 to Sayfa4, which contains the values of the Macro.

Once I try to implement this formula excell shows me an error and marks table1.

Can you give me a hint, what I did wrong?

Thank you again.

Best regards

Mahir

 

[Mahir Yagimli]

@NARAYANK991 :

Hello Narayank,

I have adjusted the macro a little to how I need it.

I have realised that you have taken the width for length. Ofcourse again, that's my fault, since I have send you the turkish version. If we take the added "boy" column (length) as orientation, then the added stone length are to long. How can we limit the length as well?

I would be happy, If you can help me on this.

BTW, I'll be on a business trip for two weeks, so my response could be a little slow. Hopefully you don't Mind.

I've uploaded the updated of the WB.

Best regards

Mahir

 

[NARAYANK991]

Hi Mahir ,

Sorry , it's my mistake ; you had mentioned that Boy stands for length in your earlier post , but I wrongly took En. Since the maximum length is 302 , this means we do need to take the length into account while considering the various combinations.

This is much more challenging now , though we may be able to use the same logic. Let me see if I can put the check for length into the existing code. Give me some time.

Narayan

 

[Mahir Yagimli]

@Narank: Hello,

Thank you very much for your help on this and take your time. Meanwhile I try to study your code as good as I can.

I wish you a good day.

Greetings

Mahir