Create data matrix from three columns in MS Excel

Create data matrix from three columns in MS Excel

I have three columns with data, for example:
 A     B            C          D
 1   [From]       [To]      [Total]
 2   Vancouver   Shanghai    100,000
 3   Vancouver   Melbourne    50,000
 4   Shanghai    Melbourne    26,000
 5   Vancouver   Shanghai    150,000
 6   Cape town   Vancouver     5,000
 7   Cape town   Vancouver    50,000
 8   etc         etc          etc

And this continues for up to 350 rows, with multiple [From] and [To] destinations often repeating themselves for different Totals. I also frequently have to add new destinations and tweak the totals. (meaning I cannot really sort or change the original data)
I would like to create an output matrix elsewhere in the spreadsheet, that finds and lists the [From] vs [To] destinations and sums the grand totals for each "leg", to look somewhat like this: 
 A     B            C          D           E           F    
 1               Shanghai     Melbourne  Vancouver    etc   
 2   Vancouver   250,000       50,000        0         
 3   Melbourne      0            0           0
 4   Shanghai       0          26,000        0
 5   Cape town      0            0          55,000
 6   etc          

Basically the opposite of this:
Transpose matrix-style table to 3 columns in Excel, but be able to add new rows/destinations and have the matrix update itself.
Any guidance is highly appreciated!


Answer 1:

Use a Pivot Table. Put [From] in the Row Lables, [To] in the Column Labels and [Total] in the Values. Depending on how you want to treat duplicate routes you might want to use Sum, Average or Max on the Values aggregation.

If you turn your data range into a List before you create the pivot table then you ought to be able to add data to the list and then refresh the pivot table to incorporate the new data.

You’ll probably want to turn off the row and column totals in the pivot table as well for the sake of clarity.

Related:  Place a command button in a cell MS Excel vba

Answer 2:

Yes, a pivot table is the way to to this. There is a nice tutorial about pivot tables here.

Our Awesome Tools