Matrix Build from 3 Columns of Data

[shartrich]

I'm looking to build a matrix from three columns of data. The three columns essentially have an a-node value, b-node value, and the result value.

I want to build a matrix where I can input the a-node values as row headers, the b-node values as column headers, and the result value will be populated in the corresponding cell (see attached). I've tried a combination of index and match functions but can't seem to get anything to work.

Eventually, I would like to build a series of matrices from a long set of three columns of data.

If you have any questions regarding the above, please post any follow up questions. Any help would be greatly appreciated!

[Ron Coderre]

Have you considered using a Pivot Table?

From the Excel Main Menu: <Data><Pivot Table>
Use: Excel……Click [Next]
Select your data……Click [Next]
Click the [Layout] button

ROW: Drag the A-Node field here
COLUMN: Drag the B-Node field here
DATA: Drag the Result field here
If it doesn't list as Sum of Result...dbl-click it and set it to Sum
Click [OK]
Select where you want the Pivot Table…Click [Finish].

That will list each intersection of A-Node and B-Node and the sum of Result.

To refresh the Pivot Table, just right click it and select Refresh Data

Is that something you can work with?

Pivot Table Links:
http://www.nickhodge.co.uk/gui/datam...ablereport.htm
http://www.contextures.com/tiptech.html

[mrchoppychops]

Use a pivot table

eta: see attached

[ChemistB]

You can get your results using the SUMPRODUCT Function. For your example, in B19, you'd input the formula

Please Login or Register  to view this content.

Drag it down and over.

This formula sums the values in C3:C14 where the A node is the value in A19 and the B node is the value in B18. Theoretically, this will only occur once in your table and thus the formula finds that occurance. Does this work for you?

[shartrich]

Thanks to all. The sumproduct function worked perfectly! It looks as if Pivot Tables would have done the same. Again, thanks for all of your help!