How can I create an Excel PivotTable to calculate the net position of trades?

Question

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 |
+--------+-----+

Answer 1

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.