Spreadsheet: 97443d1296611549-need-help-pulling-catalog-data-into-usable-format-herman-problem-4
Sheet: Problem Worksheet

 |  |  |  | Color Group 1 |  |  |  |  |  | Color Group 2 |  |  |  |  |  | Color Group 3
 |  |  |  | Our Cost |  |  |  |  |  | Our Cost |  |  |  |  |  | Our Cost
 |  |  |  | Youth, ONE and unusual sizes go in S-XL column |  |  |  |  |  | Youth, ONE and unusual sizes go in S-XL column |  |  |  |  |  | Youth, ONE and unusual sizes go in S-XL column
 |  |  |  | S-XL | 2XL | 3XL | 4XL | 5XL | 6XL | S-XL | 2XL | 3XL | 4XL | 5XL | 6XL | S-XL | 2XL | 3XL | 4XL | 5XL | 6XL
 | Widget # | Brand | Catalog Title | Cost | Cost | Cost | Cost | Cost | Cost | Cost | Cost | Cost | Cost | Cost | Cost | Cost | Cost | Cost | Cost | Cost | Cost
 | 20212 | Widget | Product Description -20212 | 1.72 | 2.81 | 2.81 | 2.81 | 2.81 |  | 2.49 | 3.81 | 3.81 | 3.81 | 3.81 |  | 2.49 | 3.81 | 3.81 | 3.81 | 3.81
 | 20215 | Widget | Product Description -20215 |  |  |  |  |  |  | 10.95 | 13.17 | 13.17 | 14.45 | 15.58 | 16.97 | 10.95 | 13.17 | 13.17 | 14.45 | 15.58 | 16.97
 | 10212 | Widget
 | 10213 | Widget
 | 10214 | Widget
 | 10210 | Widget
 | 10211 | Widget
 | AND SO ON AND SO ON
1 | I'd like to enter the Widget Industries Code from column A of the 'Sample Catalog' worksheet (in column B) and have
 |  | the blue cells calculated by this worksheet. (In practice, 'Sample Catalog' will be a separate file)
 |  | Both [NAME] and [NAME] must be used to reference pricing.
2 | The Color Group (1,2, or 3 as blocked out above) will have to be determined from the 'Sample Catalog' worksheet.
 |  | I've included a worksheet called 'All Colors' into which I've pasted data filtered from the actual catalog.
 |  | The calculation for pricing will have to determine the HIGHEST pricing from 'Sample Catalog' for overlapping
 |  |  | [NAME]. For Example: | Item #20212 begins in cell A33 of 'Sample Catalog'
 |  |  |  | The product has Neutrals,Colors,Heathers, and PFD in column D
 |  |  |  | The correct pricing for Color Group 2, [NAME], is $2.49 in cell G44
 |  | NOTE: | As noted in Cell C3 of the worksheet 'All Colors', Color Group 3 also gets its pricing from "Colors" because
 |  |  | there is no "Premium" color designation for this product.
4 | The size groups here (S-XL,2XL,3XL,4XL,5XL,6XL) must be determined from the 'Sample Catalog' worksheet.
 |  | I've included a worksheet called 'All Sizes & Size Ranges' into which I've pasted data filtered from the actual catalog
 |  | My inventiveness and skill failed me as I attempted to create numerical designations (Cols. B & E of 'All Sizes…')
 |  |  | that would make sense of the way Widget Industries lists their sizing so inconsistently.
 |  |  | For Example: | Item 10214 ('Sample Catalog' A24) and Item 20212 ('Sample Catalog' A33)
 |  |  |  | Report the sizes 2XL,3XL,4XL, and 5XL differently. And 6XL is only
 |  |  |  | available on Item 10214.
5 | I've entered the data for rows 6 & 7 by hand to show the results I'd like to get in columns D, E-J, K-P, and Q-V.
 |  | Highlighted in blue.
 |  |  | The empty cells J6, P6, V6, and E7-J7 simply indicate that there is no availabilty of this product in those
 |  |  | size/color combinations.