PDA

View Full Version : Need help loading a truck, can you help???



infinity
10-21-2006, 11:41 PM
Hello VBaxers,

I need a macro that will help me load a truck of products. I have found, for me, it is easier to attach a workbook rather than try and explain what I am trying to do in the body of the thread. Hopefully this makes it easier for you all to understand also. Due to my companies privacy, I cannot attach the actual workbook that this is going into, but I have attached a mock workbook with the pertinent portion of it. In the attachment I have got a text box explaining exactly what I am trying to accomplish. Please see the attachment and thank you sooooooo much for your time. You all have helped me so much in the past and I am very appreciative for your time, expertise and effort.

Scott

johnske
10-22-2006, 12:55 AM
I can't see any mention of it in the instructions - shouldn't the distribution of weight of the various pallets relative to the trucks axles be considered here also?

infinity
10-22-2006, 10:13 AM
No, this is for foil, wax paper and plastic wrap. These products cube out the truck before it meets the weight capacity of the truck of 45,000 lbs. Gross weight will almost never be a factor. The typical gross weight of a truckload will be between 28,000 and 35,000 lbs.

SamT
10-28-2006, 06:34 PM
Do you have any pallet heights that are outside the 34"-52" Range?

Do you ever stack three pallets high?

Is any Item ever packed in odd height palets, ie; 3 @ 52" and 1 @ 23"?

Finally, how DO you get an odd number of stacks on a truck?

lucas
10-28-2006, 07:16 PM
From the excel file:

I need it to take the value in the "PALLET HEIGHT" column in the graph below for each product and "paste special & add" it to the first pallet space that has enough clearance avaliable in it, always starting at pallet space "1A BOTTOM" and moving to "1B BOTTOM", then to "1A TOP", then to "1B TOP" and so on. After it adds the value to the pallet space, I need it to "paste special & subtract" that value from the "TOTAL" column for that product and then subtract "1" from the "PALLET COUNT" column. When the "TOTAL" column reaches 0 (at the same time the "PALLET COUNT" will also reach 0), then I need it to move on to the next product line. If for what ever reason there is no pallet space that can accommodate the height of that product any longer, then I need it to move on to the next product line and so on.
I need this to loop always beginning the loading of each pallet at space 1A BOTTOM all the way through 15B TOP (so I can maximize the space on the truck) finding the first space that can accommodate the height of the pallet until it has gone through all of the pallets of products listed in the graph below, rows 1 - 12 or until it has filled the truck to capacity which ever comes first. Thank you for your effort and time, I know this will be easy for you but unfortunately not for me. I really appreciate it.
This is an awful lot of automation to be asking for all at once and I don't think a lot of people will have time to take on a task of this size.
My suggestion:
You should start by trying to work out each step and then at the end when you can get it to do all of the things correctly you can start to combine it. Ask the questions here that will get you started and go from there.

malik641
10-28-2006, 09:37 PM
Do you want to see the item number (like your 1-12 that you showed) for each pallet space? I'm assuming you want the taller items on the bottom and the shorter items on top, right?


I'm wondering if you could do this using formulas...:think: I bet there's a way.

Shazam
10-28-2006, 09:51 PM
Do you want to see the item number (like your 1-12 that you showed) for each pallet space? I'm assuming you want the taller items on the bottom and the shorter items on top, right?


I'm wondering if you could do this using formulas...:think: I bet there's a way.

I agree with malik641.


Maybe this could be done with formulas. Can you post a sample worksheet with the expected results?

infinity
10-28-2006, 10:42 PM
SamT:
1. Yes, pallet heights will vary, that is why I need it to be based on the clearance that is left in that pallet space (the number to the right of the pallet space).
2. No, pallets will either be stacked 1 or 2 pallets high but never 3 pallets high.
3. Each pallet on each individual line item will have the exact same pallet height, other line items will have different pallet heights.
4. That is why I need it to evaluate the clearance left on the trailer for each pallet space (ie. if the total clearance is 104 inches and there are 3 pallets that are 68 inches high loaded on the first 3 bottom pallet spaces, and the next pallet that is being loaded is 36 inches high or less then it can go on top of those pallets that are already loaded, until it fills the truck or the line items are all at 0 quantities. I hope this helps.

Malik641:
That would be great if it could show the item # next to each pallet space. As far as the taller items being on the bottom, that will not be necessary, I can put the taller items on top or on bottom. It will make no difference.

Shazam:
The sample workbook is attached on the initial post at the top of the thread. Is this what you are looking for?

Thank you all for your time. I know it is very valuable!!!

johnske
10-28-2006, 10:59 PM
...Maybe this could be done with formulas...Without looking too closely at the problem - I don't think so. This is most probably a case where there may be (much) more than one possible solution, requiring you to iterate (loop) through all possibilities, list them, then make some kind of arbitrary choice from all of the possible solutions :dunno

Aussiebear
10-29-2006, 12:36 AM
SamT:
1. Yes, pallet heights will vary, that is why I need it to be based on the clearance that is left in that pallet space (the number to the right of the pallet space).
2. No, pallets will either be stacked 1 or 2 pallets high but never 3 pallets high.

Is that 2 pallets high per row( with a maximum of 120 pallets per truck - if gross did not exceed max or 1 pallet per row, (with a maximum of 60 pallets per truck)?



3. Each pallet on each individual line item will have the exact same pallet height, other line items will have different pallet heights.


Your figures suggest for line item 1 that it consists of 10 pallets. Will this mean that 1/6 of the truck pallet space is now filled or can you break down the order into a single pallet?



4. That is why I need it to evaluate the clearance left on the trailer for each pallet space (ie. if the total clearance is 104 inches and there are 3 pallets that are 68 inches high loaded on the first 3 bottom pallet spaces, and the next pallet that is being loaded is 36 inches high or less then it can go on top of those pallets that are already loaded, until it fills the truck or the line items are all at 0 quantities. I hope this helps.


Container truck has fixed floor heights, with 104 inches being fixed right?

So if I understand this right....

Max Pallet units (60) - pallet units per line order = truck load ( until zero reached if possible)

Ted

SamT
10-29-2006, 08:43 AM
Infinity,

Verify the Goals and Constraints notes I put on Sheet1.

VBAXers,

See the Ideas Sheet.

infinity
10-29-2006, 02:14 PM
Aussiebear:

1. There are 30 floor spaces available on a truck. The absolute maximum number of pallets that can be loaded on a truck, provided that all floor spaces have enough clearance in the height to double stack the products is 60. The minimum number of pallets that can be loaded is 30 if all the products can only be single stacked due to constraints in the height of the pallets as compared to the available height on the truck.

2. Line item 1 has 2 pallets, line item 2 has 10 pallets, line item 3 has 32 pallets and line item 4 has 11 pallets for a total of 55 pallets to go on this truck. The height of each pallet is 52 inches or lower so all the products on this order can be double stacked because the height available is 104 inches.

3. The useable height of the truck can vary, this is based on the value that is in Range D3. This value can change but when it does it will also change the value in each of the pallet spaces to match. (ie. if the value in Range D3 is 94 inches, the values in each of the pallet spaces will be the same. This part is already done. On the sheet that I posted, when a value is entered in one of the pallet spaces the value to the right of it (clearance for that space) should go down accordingly showing the new available clearance for that space.

4. There is the possibility that there will be more products on the summary than can fit on the truck, that is the purpose of this sheet to evaluate whether all the products that are listed will fit on the truck or if there are too many pallets and it will exceed truckload capacity. This would be easy to figure out if either all products could only be single stacked or if they all could be double stacked, but because the heights of each the products are different, it will depend on how much clearance is available. Thank you!

infinity
10-29-2006, 02:37 PM
The goals and constraints are correct except where you said "for each pallet count in item description must use all or none." It would load it in full pallet quantities. For ease of understanding, lets say that there is only 1 product line on the order with a pallet height of 72 inches (because this pallet height will vary depending on what product is on the order) and lets say that there are a total of 42 pallets on this order with a total clearance available of 104 inches, in this case only 30 of the 42 pallets would be able to be loaded on this truck because this product cannot be double stacked due to the restriction in the height. Maybe a message box could notify if that is the case. I hope this all makes sense. Thank you so much for your help!

SamT
10-29-2006, 05:59 PM
I was talking as if each numbered Line held one Item, and that each Item had a collection, pallets, and 2 properties, Pallet Height and cumulative height (Pallet height times Num Pallets).

I think you are saying that it is OK to ship the pallets on 1 line (1 Item) on two trucks to maximise the load even if it would fit one one truck by itself.

Arbitrarily limiting stacks to two pallets and not requiring an Item to be shipped complete makes this easy to solve.


While Stacks.Count < 31
'Or is it 16, anyway Floor.Spaces+1 or use a different comparetor
While Largest.Item.Count And Smallest.Item.Count > 0
If Largest.Item.Height + Smallest.Item.Height =< MaxHeight Then
Stack = Largest.Item & Smallest.Item
Else Stack = Largest.Item
Wend
Wend


SamT