Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

2 Answers 2

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. –  ekaj Jan 7 at 7:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.