Volume visualization for a truck load planner

PuntingJawa

Board Regular
Joined
Feb 25, 2021
Messages
158
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I'm attempting a visual sheet for volume (Semi/Box truck capacity). I'm hoping this is possible as my aspirations may exceed what excel is capable of, or that there may be a function that can do this for me already that I have not learned yet. As always, I am sure that I am over complicating it.

Below is where Data would be pulled from.
What I am attempting is the following:
B4-B18, C4-C18, D4-D18 is where an "ID" is placed. This is an ID that is set by the user from column O2# via typing or dropdown menu.

Load planner personal project.xlsx
ABCDEFGHIJKLMNOPQR
1Truck 1PackagingIDLengthWidthHeight
2FloorMidTopMax height 108 inchesCrate1424242
3WidthID HERE3rd rowWidthID HERE3rd rowWidthID HERE3rd rowHeightCrate2434343
4 11  42 inches of 108 usedCrate3444444
5 11  42 inches of 108 usedCrate4454545
6 11  42 inches of 108 usedCrate5464646
7 11  42 inches of 108 usedCrate6474747
8 11  42 inches of 108 usedCrate7484848
9 11  42 inches of 108 usedCrate8494949
10 11  42 inches of 108 usedCrate9505050
11 11  42 inches of 108 usedCrate10515151
12 11  42 inches of 108 used
13 11  42 inches of 108 used
14 11  42 inches of 108 used
15 11  42 inches of 108 used
16 11  42 inches of 108 used
17 11  42 inches of 108 used
18Too wide->111 1 1Too tall! Remove Top/Mid
19^^       
20Too LongGood lengthGood length
21Length usedLength usedLength used
226306304242004200
23Length LeftLength LeftLength Left
24-6-6582582624624582624624
Input
Cell Formulas
RangeFormula
M2M2="Max height "&$W$13&" inches"
A4:A18A4=IF(AU4>$W$11,"Too wide->","")
E4:E18E4=IF(AU19>$W$11,"Too wide->","")
I4:I18I4=IF(AU34>$W$11,"Too wide->","")
M4:M18M4=IF(AV3>$W$13,"Too tall! Remove Top/Mid",AV3&" inches "&"of "&$W$13&" used")
B19B19=IF(B22<W9,"","^")
C19C19=IF(C22<W9,"","^")
D19D19=IF(D22<W9,"","^")
B20,J20,F20B20=IF(B19=CHAR(94),"Too Long",IF(C19=CHAR(94),"Too long",IF(D19=CHAR(94),"Too Long","Good length")))
F19F19=IF(F22<W9,"","^")
G19G19=IF(G22<W9,"","^")
H19H19=IF(H22<W9,"","^")
J19J19=IF(J22<W9,"","^")
K19K19=IF(K22<W9,"","^")
L19L19=IF(L22<W9,"","^")
B22B22=AY3
C22C22=AY4
D22D22=AY5
F22F22=AY7
G22G22=AY8
H22H22=AY9
J22J22=AY11
K22K22=AY12
L22L22=AY13
B24B24=W9-B22
C24C24=W9-C22
D24D24=W9-D22
F24F24=W9-F22
G24G24=W9-G22
H24H24=W9-H22
J24J24=W9-J22
K24K24=W9-K22
L24L24=W9-L22
Cells with Conditional Formatting
CellConditionCell FormatStop If True
J19:L19Cell Value=""textNO
F19:H19Cell Value=""textNO
B19:D19Cell Value=""textNO
J20Cell Valuecontains "Good length"textNO
J20Cell Valuecontains "Too long"textNO
F20Cell Valuecontains "Good length"textNO
F20Cell Valuecontains "Too long"textNO
J19:L19Cell Valuecontains "^"textNO
F19:H19Cell Valuecontains "^"textNO
B19:D19Cell Valuecontains "^"textNO
B20Cell Valuecontains "Good length"textNO
B20Cell Valuecontains "Too long"textNO
J24:L24Cell Value>0textNO
J24:L24Cell Value<0textNO
F24:H24Cell Value>0textNO
F24:H24Cell Value<0textNO
B24:D24Cell Value>0textNO
B24:D24Cell Value<0textNO
J23Cell Value<0textNO
F23Cell Value<0textNO
B23Cell Value<0textNO
F21Cell Value<0textNO
A4:A18,E4:E18,I4:I18,E29:E43,I29:I43,E54:E68,I54:I68,E79:E93,I79:I93,M4:M18,M29:M43,M54:M68,M79:M93Cell Valuecontains "Too"textNO
B21,J21Cell Value<0textNO
Cells with Data Validation
CellAllowCriteria
B4:D18List=$O$2:$O$11
F4:H18List=$O$2:$O$11
J4:L18List=$O$2:$O$11


What I want to try is if there is an ID from column O in the above ranged cells (B4-18/C4/18/D4-18) that it'll reference column P (Length) and Q (Width) for the data required.
In this example B4 has a 1.
I would like to add it to a graph of sorts below (Or another function if easier and more viable). Each cell in the graph represents an inch.
ID 1 has a length of 42 (Column P) and width of 42 (Column Q).
I would like these to fill in based on the above information.
In this case 42 cells from left to right for the width and 42 spaces from top to bottom.
It would process B4, then C4, then D4. After which B5,C5,D5, and so on repeating until we finish B18, C18, and D18.
1707664643842.png


If this is too complicated or there is a better/easier way to accomplish what I am attempting, please let me know. Honestly, this is just a "fun" function I am trying to add and is more or less a toy at the moment.

As always, thank you MrExcel board. You've taught me so much so far.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Mmm, nice challenge. I've been puzzling around with formulas a bit, but guess you'd have to fall back to some VBA to get this done easily. There will be space between the boxes etc. If not, these functions might help you:

Excel Formula:
=XLOOKUP(B4,$O$2:$O$10,$P$2:$P$10,0,0)
-> will lookup the length of a crate for a certain ID (in cell B4)
Excel Formula:
=COLUMN()
-> will give back the column number
Excel Formula:
=ROW()
-> will give back the row number
If I focus purely on "length" -> I'd first make a "cumulative table", so using the XLOOKUP to find the length per ID and then adding them up, in your example you'd get a list: 42, 84, 126, etc. With your L/W table: use
Excel Formula:
=XMATCH(64,Input!$B$54:$B$68,1)
-> the second item in that formula is the cumulative table. It'll lookup 64 in the list 42,84, etc and will give back "2", your second item. You can imagine using that in your last sheet to drag down and get per cell the ID/row it belongs to.

As said, that's only a start and I think you'd have to fall back to VBA to get this done. It might be a good starting point for learning it though ;-).
 
Upvote 0
I've never used xmatch before so it'll be a fun function to play with. Thanks for the advice! I'm worried about the impact it may have on the sheet and if i were to go down with the route i visualize in my head. The approach may be overly complicated. There's quite a lot of data to consider with calculation of volumes utilized versus used. I've also looked into the graphs to see if I can use that instead. I'm just having issues with locking the data in properly.

In a nutshell, I'm just trying to give a visualization of what's used and where vs what's available and why an item would or would not fit. From what I can see, there is nothing excel based out there that comes close to what I've made, or there is only paid applications that are overly complicated that have a massive learning curve and price tag. If I can get this to work, I'm going to post it so it's freely available.


Again, thank you for giving me more to work with. I'll see what I can come up with.
 
Upvote 0
Yeah, you've got a challenge on your hands... See this example: I created an area of 8 wide, 18 long. I first added 1 box of 5x5 (grey) and one box of 3x3 (red, both fit), if you afterwards want to add a box of 5x5, you can place that at the right, but you'll have some empty space. So it's quite a challenge to fill it all up :-).

1708432037874.png
 
Upvote 0
Yeah, you've got a challenge on your hands... See this example: I created an area of 8 wide, 18 long. I first added 1 box of 5x5 (grey) and one box of 3x3 (red, both fit), if you afterwards want to add a box of 5x5, you can place that at the right, but you'll have some empty space. So it's quite a challenge to fill it all up :).

View attachment 107152
That is actually what I'm trying to show. With truck loads, you never quite use 100% of space. There will always be some left. As long as I can get it to look like what it should look like visually, then it's the right direction
 
Upvote 0
It may not matter with your product, but you may also need to consider weight distribution and can the product be stacked.
 
Upvote 0
It may not matter with your product, but you may also need to consider weight distribution and can the product be stacked.
Agreed. For now I'm just trying to get the template together for a visual. Most formula are in the first sheet which is the manual input portion that I'll be pulling information from. Weight distribution is simple enough to factor in as (keep in mind that I haven't had to worry about this in a few years) the first 1/3 of the truck (nose) can handle 12,000 to 14,000 lbs and the other 2/3, 32,000. Reefer trucks should subtract up to 250-350 lbs for the fridge unit. This is subject to type of trailer as well. Some can't move their tandems which complicates it a bit more. All this can be added as warnings later. As for items that are stackable. I already have warnings in place of too tall. Items that shouldn't stack due to being too weak would be more of a shipper experience thing.
Did I give enough information or is there any other information that would help? I have a habit of giving more information than required and tend to confuse even myself sometimes.
 
Upvote 0
Its not Excel but, You may want to look at this website to see if it helps.
I think it might be too complicated for Excel to handle. If you search this website and google for container volume calculations you will find a number of posts with people trying to do this in Excel without much luck.

 
Upvote 0
Its not Excel but, You may want to look at this website to see if it helps.
I think it might be too complicated for Excel to handle. If you search this website and google for container volume calculations you will find a number of posts with people trying to do this in Excel without much luck.

It's that site which got me thinking about it and with how complicated it was, got me making my own.
I already have the base for it. This isn't automated for the truck load as the user would still need to place ID's in, but it's a convenient tool no less. As of now, I have the base for it. Just want a visual and that is what I am working toward.
1708463181809.png
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top