Tying Container Code to Dimensions - Please Help!

[stoutbn]

I did not know how to title what I am trying to do without showing the data directly. I am trying to use a formula to choose a container code based on the dimensions of a container. This is currently a manual process which takes forever. The data below should give an explanation of my criteria and choices.

Description	Dimensions	Code Printed on Container Tag
Cardboard box / pallet combination Equal (or not to exceed)	48" x 34" x 27"	121
Cardboard box Equal (or not to exceed)	32" X 30" X 25"	126
Cardboard box / pallet combination Equal (or not to exceed)	48" x 39" x 34"	131

<colgroup><col width="305" style="width: 229pt; mso-width-source: userset; mso-width-alt: 11154;"> <col width="190" style="width: 143pt; mso-width-source: userset; mso-width-alt: 6948;"> <col width="109" style="width: 82pt; mso-width-source: userset; mso-width-alt: 3986;"> <tbody> </tbody>

These are just 3 of my choices. In this scenario a container that is 45" x 32" x 25" would classify as a 121 because its length, width, and height do not exceed the dimensions in the 2nd column. All of my length, width, and height options are below. The data being measured would be 3 cells (a length, a width, a height). All of my lengths, widths, and heights available are below. Please help!

Lengths	Widths	Heights
5.5	5.5	5
11	7	6
12	11	7
24	15	8.375
32	22	9
38	30	11
48	32	25
52	32.5	27
54	38	32.5
64	39	34
70	45	36
72	48	39
108	52	43
		44
		48

<tbody>
</tbody><colgroup><col><col><col></colgroup>

 

[kieran]

G'day stoutbn,

If you arrange the data so that the height, width and length are in columns A to C, with the code number in column D then the following formula will spit out the smallest matching code combination.
A few amendments will be necessary to the formula though.

Change references to D17 to be equal to the cell location of the last code number. (there are 3 places you need to fix it.)
Cells G3, H3 and I3 are input cells for the formula, corresponding to length, width & height. You will need to use these cells to enter the dimensions you are checking for.

Put the formula in an empty cell somewhere near G3, H3 and I3 for ease of use.

=MAX(VLOOKUP(G3,A2:D17,4,TRUE),VLOOKUP(H3,B2:D17,3,TRUE), VLOOKUP(I3,C2:D17,2,TRUE))

WARNING : if the size is too small, a #N/A error will result, If the size is too big, the lat code number is returned. (I suggest you add a dummy/warning row at the end of the list with slightly larger dimensions and a code number that is obviously weird - eg 9999

 

[stoutbn]

Kieran,

Thanks for the response! I like your strategy, but unfortunately will have to have a formula that works when the inputs (length width or height) do not match any of my length width height limits. For example a 31" x 30" x 24" would classify with the container code that matches a "Does not exceed 32" x 30" x 25". This is because containers will come to us in a variety of sizes, so if any of the dimensions exceeds one of those limits, then it gets kicked up to the next set of limits and respective container code. Hope this makes sense!

Maybe I need to use buckets similar to how a histogram works? Not sure how to work that in to a formula though.

 

[Rick Rothstein]

Description	Dimensions	Code Printed on Container Tag
Cardboard box / pallet combination Equal (or not to exceed)	48" x 34" x 27"	121
Cardboard box Equal (or not to exceed)	32" X 30" X 25"	126
Cardboard box / pallet combination Equal (or not to exceed)	48" x 39" x 34"	131

<tbody>
</tbody>

Some questions...

1) Do you have the above table (expanded for all your possible size combinations and container codes) located on a worksheet? If so, what is the name of that worksheet and what cells are they in?

2) On what worksheet is your "raw data" (the data that you want to find the container codes for) and in what cells?

3) What does your "raw data" look like? Is it in three columns (one for length, one for width and one for height) or is it presented as in your above dimensions column (that is, a text string that contains numbers, inch symbols and X's)?

4) Can you make use of a VBA macro or UDF (user defined function in VBA) solution?

 

[stoutbn]

Rick,

1) Below is the container code guide I am to use. Should give in full detail the options I have. Generically, they begin in columns A-D and go down 53 cells. The name of the worksheet would be "Container Codes".

2) The raw data will be constantly added to a worksheet called "New Data" and will be in columns Container Length, Container Width, Container Height. I will also have Container material where steel, plastic, cardboard are determined as well as container type where expendable/returnable are determined.

3) Raw data will simply be numbers not strings.

4) I would be happy with a macro or formula. Whichever is easiest!

Description	Dimensions	Code Printed on Container Tag	Type
Corporate owned plastic container (plastic half tub)	38" x 32" x 23"	100	Plastic
Corporate owned plastic pallet ( 48X45 PT Pallet )	48" x 45" x 6"	102	Plastic
Corporate owned half standard steel tote	32.5" x 38" x 27"	120	Steel
Cardboard box / pallet combination Equal (or not to exceed)	34" x 48" x 27"	121	Cardboard
Plastic Knock Down Bulk Container	32" X 30" X 25"	125	Plastic
Cardboard box Equal (or not to exceed)	30" X 32" X 25"	126	Cardboard
Corporate owned standard steel skid	Pallet dimension - 32" x 42" x 8.375" Load height equal (or not to exceed) 27"	127	Steel
Plastic Knock Down Bulk Container	32" x 30" x 34"	128	Plastic
Corporate owned standard steel tote	32.5" x 38" x 39"	130	Steel
Cardboard box / pallet combination Equal (or not to exceed)	34" x 48" x 39"	131	Cardboard
Corporate owned standard steel skid	Pallet dimension 32" x 42" x 8.375" Load height equal (or not to exceed) 39"	139	Steel
Corporate owned jumbo standard steel tote	43" x 45" x 48"	140	Steel
Cardboard box / pallet combination Equal (or not to exceed)	45" x 48" x 48"	141	Cardboard
Plastic Knock Down Bulk Container	48" x 45" x 25"	144	Plastic
Plastic Knock Down Bulk Container	48" X 45" X 34"	145	Plastic
Cardboard box Equal (or not to exceed)	48" X 45" X 34"	146	Cardboard
Corporate owned standard tiering rack	39" x 42" x 39"	150	Steel
Cardboard box / pallet combination Equal (or not to exceed)	45" x 48" x 39"	160	Cardboard
Plastic Knock Down Bulk Container	64" X 48" X 44"	165	Plastic
Cardboard box / pallet combination Equal (or not to exceed)	45" x 48" x 27"	170	Cardboard
Plastic Knock Down Bulk Container	70" X 48" X 25"	175	Plastic
Cardboard box / pallet combination Equal (or not to exceed)	Greater than 48" x 45"	180	Cardboard
Plastic Knock Down Bulk Container	70" X 48" X 44"	185	Plastic
returnable container	1 warehouse spaces (36"x48x52") or smaller	510	Custom Returnable
returnable container	1.5 warehouse spaces (54"x48x52") or smaller	515	Custom Returnable
returnable container	2 warehouse spaces (72"x48x52") or smaller	520	Custom Returnable
returnable container	3 warehouse spaces (108"x48"x52" or smaller)	530	Custom Returnable
returnable container - Yard	Varies by component	550	Custom Returnable
Small 11x4 Film Contianer	4 1/8" x 10 7/8" x 4"	601	Plastic
Medium 11x11 Film Contianer	11"X11"X5"	602	Plastic
Plastic Flow rack container	12" X 7" X 5"	700	Plastic
Cardboard box Equal (or not to exceed)	12" X 7" X 5"	701	Cardboard
Plastic Flow rack container	12" X 15" X 5"	710	Plastic
Cardboard box Equal (or not to exceed)	12" X 15" X 5"	711	Cardboard
Plastic Flow rack container	12" X 15" X 7"	715	Returnable
Cardboard box Equal (or not to exceed)	12" X 15" X 7"	716	Expendable
Plastic Flow rack container	12" X 15" X 9"	720	Plastic
Cardboard box Equal (or not to exceed)	12" X 15" X 9"	721	Cardboard
Plastic Flow rack container	24" X 15" X 5"	725	Plastic
Cardboard box Equal (or not to exceed)	24" X 15" X 5"	726	Cardboard
Plastic Flow rack container	24" X 15" X 9"	730	Plastic
Cardboard box Equal (or not to exceed)	24" X 15" X 9"	731	Cardboard
Plastic Flow rack container	24" X 22" X 7"	735	Plastic
Cardboard box Equal (or not to exceed)	24" X 22" X 7"	736	Cardboard
Plastic Flow rack container	24" X 22" X 9"	740	Plastic
Cardboard box Equal (or not to exceed)	24" X 22" X 9"	741	Cardboard
Plastic Rack container	48" x 15" x 11"	770	Plastic
Cardboard box (hand deliver only) Exceeds all other flow rack box sizes	Exceeds 24" x 15" x 7"	800	Cardboard
Cardboard box Equal (or not to exceed)	5.5" x 5.5" x 5"	809	Cardboard
Cardboard box Equal (or not to exceed)	5.5" x 11"x 5"	810	Cardboard
Cardboard box Equal (or not to exceed)	11 X 11 X 5 PREPACK BOX	811	Cardboard
Cardboard box Equal (or not to exceed)	5.5" x 22" x 5"	911	Cardboard

<colgroup><col width="305" style="width: 229pt; mso-width-source: userset; mso-width-alt: 11154;"> <col width="190" style="width: 143pt; mso-width-source: userset; mso-width-alt: 6948;"> <col width="109" style="width: 82pt; mso-width-source: userset; mso-width-alt: 3986;"> <col width="128" style="width: 96pt; mso-width-source: userset; mso-width-alt: 4681;"> <tbody> </tbody>

 

[Rick Rothstein]

Well, that table of container choices is different than you led on it would be in your first message, that is for sure (any formula or solution anyone might have developed for you based on it would have been worthless). And that table raises several questions as well...

1) Does your Dimensions column actually look the way you showed it? One entry (PREPACK BOX) has no inch-mark symbols attached to its dimension numbers whereas all the rest do? Some of the X delimiters are upper case, some lower case, some have surrounding spaces and some do not, and some have additional text whereas most do not?

2) What does Exceeds 24" x 15" x 7" mean? Your initial message said you wanted the find the dimensions which basically are minimally greater than your individual raw data dimensions... how does that "exceeds" figure in?

3) For an entry like this, Pallet dimension - 32" x 42" x 8.375" Load height equal (or not to exceed) 27", are we supposed to ignore the 8.375" number and use 27" instead?

4) How is this one, Greater than 48" x 45" supposed to be worked into the calculations (it has only two dimensions, not three)?

5) The material shown in the "Type" column does not figure into the calculations at all? In other words, you want the best fitting dimensions no matter if that container is Cardboard, Plastic, Steel, Returnable, Expendable or Custom Returnable?

6) You have one Dimension marks as Varies by component, I presume that one is to be ignored?

I may have missed some questions that might need to be asked, but the above would need to be answered before anyone would be able to start to decide if the kind of solution that this forum is designed to provide (free as opposed to time-consuming requiring professional, paid consultation). I would also note that the table you provide would have to be reworked into a usable format (should be doable once you answer the above questions).

 

[stoutbn]

Rick,

Thank you for taking the time to look at this. The guide I just provided was meant to give a representation of the instructions I have before me, not necessarily the cells that have to be used in this solution to my problem. (I can modify the texts in the guide as much as I want, but the numbers in inches are what I have to stick to.)

I would rather spend less time worrying about the Container Material type and more on the dimension ranges I am trying to fit into. Those are my main constraints and I can figure out the container material later, hopefully with little issue.

So to answer your numbered questions. I would worry less about how those texts are represented since they can be modified, and spend more time focusing on how multiple ranges of dimensions minimums and maxes can be worked with. Hope that helps answer some of your questions.

 

[shg]

If your container dimensions are disposed in three columns, it's easy to write a formula to select which container a given package fits within.

 

[Rick Rothstein]

Rick,

Thank you for taking the time to look at this. The guide I just provided was meant to give a representation of the instructions I have before me, not necessarily the cells that have to be used in this solution to my problem. (I can modify the texts in the guide as much as I want, but the numbers in inches are what I have to stick to.)

I would rather spend less time worrying about the Container Material type and more on the dimension ranges I am trying to fit into. Those are my main constraints and I can figure out the container material later, hopefully with little issue.

So to answer your numbered questions. I would worry less about how those texts are represented since they can be modified, and spend more time focusing on how multiple ranges of dimensions minimums and maxes can be worked with. Hope that helps answer some of your questions.

I think questions 2, 3, 4 and 6 still need to be addressed (you may know what they mean, but to someone not knowledgeable in your business model (like me), they are kind of nebulous specifications.

 

[stoutbn]

Shg,

They will be in three separate columns like you are suggesting. Please expand more if you can, thanks!