Populating fields in multiple cells based on criteria in multiple columns

**excelalways** · 02-24-2014, 04:37 AM

To All Excel Gurus out here,

Your help will be greatly appreciated! Im struggling to find a formula to populate cells based on values to be checked in two fields (ICODE, COMPANY) as shown below.
In the example here, all records where ever the ICODE = 49, i need to populate the fields(columns) AD1,AD2,AD3 or AD4 with the product name of the same company where the ICODE <> 49. There could be between 1-7 products per company.

CURRENT SHEET

ICODE PRODUCT COMPANY AD1 AD2 AD3 AD4
89 PENS ABC STATIONERY CO LLC

49 PENCILS ABC STATIONERY CO LLC

49 ERASER ABC STATIONERY CO LLC

94 OVEN WORLD KITCHENWARE

90 CABINETS WORLD KITCHENWARE

49 COOKING RANGE WORLD KITCHENWARE

49 S. S. TABLE WORLD KITCHENWARE

49 DISHWASHER WORLD KITCHENWARE

91 TABLES GENUINE FURNITURE SUPPLIES

49 CHAIRS GENUINE FURNITURE SUPPLIES

89 PIPES RELIABLE PLASTICS CO

90 CHAIRS RELIABLE PLASTICS CO

24 BUCKET RELIABLE PLASTICS CO

93 TABLES RELIABLE PLASTICS CO

24 TOYS RELIABLE PLASTICS CO

49 BOTTLES RELIABLE PLASTICS CO

FINAL REQUIRED SHEET

ICODE PRODUCT COMPANY AD1 AD2 AD3 AD4
89 PENS ABC STATIONERY CO LLC

49 PENCILS ABC STATIONERY CO LLC PENS

49 ERASER ABC STATIONERY CO LLC PENS

94 OVEN WORLD KITCHENWARE

90 CABINETS WORLD KITCHENWARE

49 COOKING RANGE WORLD KITCHENWARE OVEN CABINETS

49 S. S. TABLE WORLD KITCHENWARE OVEN CABINETS

49 DISHWASHER WORLD KITCHENWARE OVEN CABINETS

91 TABLES GENUINE FURNITURE SUPPLIES

49 CHAIRS GENUINE FURNITURE SUPPLIES TABLES

89 PIPES RELIABLE PLASTICS CO

90 CHAIRS RELIABLE PLASTICS CO

24 BUCKET RELIABLE PLASTICS CO

93 TABLES RELIABLE PLASTICS CO

49 TOYS RELIABLE PLASTICS CO PIPES CHAIRS BUCKET TABLES

49 BOTTLES RELIABLE PLASTICS CO PIPES CHAIRS BUCKET TABLES

The attached example file may be a better alternative to view this problem.
Request you Excel gurus out there to please help me with this. Thanks in advance. Attached sample Excel file too.

**soledad** · 02-26-2014, 12:22 AM

You can try this formula for Cell D3

Please Login or Register  to view this content.

You can define the name (click mouse the cell D3)
SP=SUMPRODUCT(($A$1:$A3<>49)*($C$1:$C3=$C3))
Above formula should be :

Please Login or Register  to view this content.

**excelalways** · 02-28-2014, 08:17 AM

Greetings Soledad, You are indeed a Genius! Thanks a million! as you are truly a Guru and have shown me light in the right path. The first formula worked fine for the first instance of the product type. Please, I could not understand the second part of the formula you suggested where SP=sumproduct etc. Also if you can help me populate the AD2, AD3, AD4 Fields accordingly. Your help would be greatly appreciated. Thanks again.