Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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!

share|improve this question

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.

share|improve this answer
    
Awesome @Mike Fitzpatrick and @tumchaaditya; works like a charm. – user193332 Jan 28 '13 at 7:08

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

share|improve this answer
    
Hey tum, you may want to write out your answer incase that link eventually dies. – cutrightjm Jan 7 '14 at 7:49

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .