Help with logic formulas with array

[davidfredc]

Ok this is somewhat tough, I consider myself to be handy with Excel and can't figure it out.

I have one sheet with 3 columns: Product name, raw material name, weight of raw material.

So in the first column you have the product listed several times for every raw material, then the weight of that raw material. I want to create another sheet with all the products as the header for the columns, and the raw materials on the side...I've done this but now I need a formula to populate this matrix with the weight values that correspond to the product and raw material.

I've tried a bunch of different 'if' / 'and' statements using array formulas. Pls help.

 

[golfnut324]

Ok this is somewhat tough, I consider myself to be handy with Excel and can't figure it out.

I have one sheet with 3 columns: Product name, raw material name, weight of raw material.

So in the first column you have the product listed several times for every raw material, then the weight of that raw material. I want to create another sheet with all the products as the header for the columns, and the raw materials on the side...I've done this but now I need a formula to populate this matrix with the weight values that correspond to the product and raw material.

I've tried a bunch of different 'if' / 'and' statements using array formulas. Pls help.

Newbie here so...

Would a pivot table help?

 

[Jonmo1]

Welcome to the board...

Here is a very basic example, both tables on the same sheet, but you should get the idea..

Personal.xls
	A	B	C	D	E	F	G	H	I
1	Product	RawMaterial	Weight				Product1	Product2	Product3
2	Product1	A	5			A	5	15	25
3	Product1	B	10			B	10	20	30
4	Product2	A	15
5	Product2	B	20
6	Product3	A	25
7	Product3	B	30
Sheet1

 

[davidfredc]

Pivot table works, I guess that was a really dumb question then.

 

[jyk]

I would also recommend trying a pivot table, and seeing if that gives you what you want.

Using a pivot table, you can create (for example) a matrix with the products listed along the top and the materials listed along the side. The cells of the matrix would then show the weights for each product-material pair. You can also set up the table to include whatever summary information you want (for example, the total weight of all the materials for one product, and/or the total weight of a single material for all products).

 

[golfnut324]

Wow! I got one right! Not bad for my first suggestion. Here's a link I used to try and learn about setting up a pivot table. Sounds like you got it down all ready though.

http://www.metacafe.com/watch/784047/how_to_create_a_pivot_table_in_excel_2003/