View Question
Q: Excel - match a product along 3 variables ( No Answer,   2 Comments )
 Question
 Subject: Excel - match a product along 3 variables Category: Computers > Algorithms Asked by: mykemyke-ga List Price: \$10.00 Posted: 11 Sep 2006 19:43 PDT Expires: 13 Sep 2006 13:54 PDT Question ID: 764337
 ```I have list of product categories, with maximum Length, Width, and Height measurements. I then have a list of products with length, width, and height. I want to know what product group each product belongs to, assuming that they fit into the largest allowable category. This has to be done in excel or access, and be replicable for more parts in the same format. Here is some data to help you solve the problem. I've included a set of right answers for these parts, so you know that it's the right answer: Category L W H 8D 527.05 282.58 250.83 4D 527.05 222.25 250.83 4DLT 508.00 207.96 201.61 3ET 490.54 111.13 249.24 3EE 490.54 111.13 225.43 17TF 433.39 176.21 201.61 16TF 420.69 180.98 282.58 49 381.00 174.63 192.09 93 381.00 174.63 174.63 50 342.90 127.00 254.00 29H 333.38 171.45 231.78 30H 333.38 171.45 231.78 31 330.20 173.04 239.71 29NF 330.20 139.70 227.01 53 330.20 119.06 209.55 27F 317.50 173.04 227.01 65 306.39 190.50 192.09 48 306.39 174.63 192.09 27 306.39 173.04 225.43 64 296.86 161.93 225.43 41 293.69 174.63 174.63 6 TL 285.75 266.70 242.89 91 279.40 174.63 174.63 46 273.05 173.04 228.60 24F 273.05 173.04 228.60 74 260.35 184.15 222.25 78 260.35 179.39 195.26 24 260.35 173.04 225.43 34 260.35 173.04 200.03 34R 260.35 173.04 200.03 58 255.59 182.56 176.21 58R 255.59 182.56 176.21 56 254.00 153.99 212.73 47 246.06 174.63 190.50 42 242.89 173.04 173.04 22F 241.30 174.63 211.14 45 239.71 139.70 227.01 22NF 239.71 139.70 227.01 51 238.13 128.59 223.84 51R 238.13 128.59 223.84 75 230.19 179.39 195.26 25 230.19 174.63 225.43 35 230.19 174.63 225.43 86 230.19 173.04 203.20 62 225.43 161.93 225.43 26 223.84 173.04 196.85 26R 223.84 173.04 196.85 55 219.08 153.99 212.73 21 207.96 173.04 222.25 21R 207.96 173.04 222.25 U1 196.85 131.76 185.74 U1R 196.85 131.76 185.74 54 185.74 153.99 212.73 12T 179.39 176.21 201.61 Products Part L W H Right Category 1 599.44 128.6 222.3 51R 2 527 282.5 250.8 8D 3 527 222.3 250.8 4D 4 352.4 173 204.8 49 5 330.2 172.7 239.7 31 6 330.2 172.7 239.7 31 7 330.2 172.7 239.7 31 8 330.2 172.7 239.7 31 9 327 173.8 228.6 4 10 317.5 173 225.5 27 11 306.4 173 190.5 48 12 300 184 194 65 13 293 173 190.5 41 14 273 171.5 203 - 15 260 179 184 78 16 260 173 204 34 17 259 172 232 24 18 259 172 232 24R 19 254 184 174.5 58R 20 254 152.4 174.5 58 21 241.3 173 190.5 47 22 241.3 173 173 42 23 236 128.6 222.3 51 24 230 174.6 222 25 25 230 173 222.3 35 26 230 171.5 184 75 27 222.3 184.5 181 70 28 222.3 178 203 - 29 197 130 187 U1 30 563.9 279.4 251.5 8D 31 546.1 190.5 241.3 - 32 528 214 247 4D 33 527 279 254 8D 34 527 277 246 8D 35 527 277 246 Atlas 36 527 216 258 4D 37 508 206 206 4DLT 38 490.2 101.6 223.5 2E 39 489 108 254 3EH 40 489 108 248 3ET 41 489 108 223 3EE 42 400 178 237 7D 43 394 175 190 95 44 354 175 190 49 45 354 175 190 49 46 354 175 175 93 47 354 175 175 49/ 93 48 354 174 191 49 49 345.4 172.7 226 30H 50 340 181 235 5D 51 337 140 220 50 52 330.2 172.7 238.8 31 53 330.2 172.7 238.8 31 54 330.2 172.7 238.8 31 55 330.2 172.7 238.8 31 56 330.2 172.7 238.8 31 57 330.2 172.7 238.8 31 58 330.2 172.7 238.8 31``` Clarification of Question by mykemyke-ga on 11 Sep 2006 20:20 PDT ```I copied the wrong sample data in: Category Product L W H 51R 1 599.44 128.60 222.30 8D 2 527.00 282.50 250.80 4D 3 527.00 222.30 250.80 49 4 352.40 173.00 204.80 31 5 330.20 172.70 239.70 31 6 330.20 172.70 239.70 31 7 330.20 172.70 239.70 31 8 330.20 172.70 239.70 4 9 327.00 173.80 228.60 27 10 317.50 173.00 225.50 48 11 306.40 173.00 190.50 65 12 300.00 184.00 194.00 41 13 293.00 173.00 190.50 - 14 273.00 171.50 203.00 78 15 260.00 179.00 184.00 34 16 260.00 173.00 204.00 24 17 259.00 172.00 232.00 24R 18 259.00 172.00 232.00 58R 19 254.00 184.00 174.50 58 20 254.00 152.40 174.50 47 21 241.30 173.00 190.50 42 22 241.30 173.00 173.00 51 23 236.00 128.60 222.30 25 24 230.00 174.60 222.00 35 25 230.00 173.00 222.30 75 26 230.00 171.50 184.00 70 27 222.30 184.50 181.00 21 28 197.00 130.00 187.00``` Request for Question Clarification by maniac-ga on 12 Sep 2006 19:33 PDT ```Hello Mykemyke, I am not quite sure what you are asking for. You say in part: I want to know what product group each product belongs to, assuming that they fit into the largest allowable category. ... Category L W H 8D 527.05 282.58 250.83 4D 527.05 222.25 250.83 4DLT 508.00 207.96 201.61 Does this mean an item longer / wider / higher than 4DLT fits into category 4D unless it exceeds either the width of 4D & then it would fit into category 8D? [something like matching the smallest volume that contains the object - please confirm] I'll also echo the question related to the differences between categories 51 and 51R as well (they appear identical in the reference table). --Maniac```
 ```I dont see how the product 1 can fit into the categorie 51R if the L max of 51R is 238.13 and the product 1's L is 599.44?? On top of it, what the difference between 51 and 51R```
 ```Hi, I've since realized that there's a lot more problems with the data, as you guys have pointed out, so I'm going to withdraw the question. Thanks for your time```