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.

Data worksheet tracking trades from one person to another:

+------+----+-----+
| From | To | Amt |
+------+----+-----+
| A    | B  | 10  |
+------+----+-----+
| B    | C  | 5   |
+------+----+-----+
| A    | C  | 3   |
+------+----+-----+
| C    | B  | 2   |
+------+----+-----+

How do I create a pivot table (using Calculated Items, Calculated Fields, etc.) that would look like the following?

+--------+-----+ 
| Person | Net |
+--------+-----+ 
| A      | -13 |
+--------+-----+ 
| B      |   7 |
+--------+-----+ 
| C      |   6 |
+--------+-----+
| Total  |   0 |
+--------+-----+
share|improve this question
add comment

1 Answer

This solution is based on using a converted source table of data.

Assuming your data is in a clean worksheet and your table of data starting from A1:

Type these labels and functions:

E1: Person

F1: Net

E2: =INDEX(A:B,IF(ROW(E2)>COUNTA(A:A),ROW(E2)-COUNTA(A:A)+1,ROW(E2)),IF(ROW(E2)>COUNTA(A:A),2,1))

F2: =INDEX(C:C,IF(ROW(E2)>COUNTA(A:A),ROW(E2)-COUNTA(A:A)+1,ROW(E2)))*IF(ROW(E2)>COUNTA(A:A),1,-1)

then drag to fill with these two functions down, until you see lots of zeros.

Start a Pivot Table with source data range: E:F and target positioning within the same worksheet at H1.

Drag the Person field to the Row Labels area, and the Net field into the ∑ Values area and change its summarization function into Sum.

Filter the zeros and blanks, and this is it.

I hope it helps.

share|improve this answer
add comment

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.