PIVOTTABLE: Group relevant data from across multiple rows under a single row label.

[albatr0n]

EDIT: I've figured out a partial solution. I still need some help to automate the whole process. Please look at the attached sheet, if you'd like to help.


Hey!

I am trying to summarize the following data from a table:

Value----To-------From

100------John-----Lisa
50-------Mike-----John
30-------Sandra--John
20-------Lisa------Mike
...

to look like this in a PivotTable

Agent----Total To----Total From----Balance

John-----100----------80--------------20
Lisa-------20---------100------------(-80)
Mike------50----------20--------------30
Sandra---30------------0--------------30

Basically, I want to sum all values under the same name that are spread across multiple rows into a single row.

Filtering doesn't help since it is row-based and can't be applied to multiple columns simultaneously. The PivotTable builder doesn't allow me to merge data from multiple columns either, and I don't know if consolidation ranges can help resolve this.

The most elegant solution would be a flexible PivotTable which can combine relevant data from multiple rows into a single row without resorting to complex formulas or VBA scripts.

Please note that the data set is a table where data will continue to be entered this way.

Hope you wizards can figure this one out.