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!