This is a discussion on Help with logic formulas with array within the Excel Questions forums, part of the Question Forums category; Ok this is somewhat tough, I consider myself to be handy with Excel and can't figure it out. I have ...
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.
Welcome to the board...
Here is a very basic example, both tables on the same sheet, but you should get the idea..
Microsoft Excel - Personal.xls ___Running: 11.0 : OS = Windows XP
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
A B C D E F G H I 1 Product Raw Material 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
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
Add-in for posting ranges as copyable tables (bottom of the page) -Win & Mac
The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
Ferris Bueller A.K.A. John Hughes, 1986
Pivot table works, I guess that was a really dumb question then.
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).
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.