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!
Best Answer
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.