Group Shared Expense Calculator using Excel

Question

I want to track the expenses of me and my friends with excel. I have the following table structure:

Date	Amount	Paid By	Split Type	Name 1	Name 2	Name 3	...
16.05.	4.99$	Name 1	Split Equal				...
16.05.	10$	Name 1	Split Unequal by Percentage	50%	50%		...
18.05.	10$	Name 3	Split Unequal by Amount	2$	6$	2$	...

The goal is, to create a matrix-like table where you can easily read which person owes another. By using a simple excel table with big, nested formulas & try and error I was able to successfully accomplish this. However, I'm curious whether this would be doable using Pivot Tables, since they are very powerful and I always feel like "they can do everything" (and the formulas are neither nice nor easy to maintain in the future).

So for the above example, the desired result could look like this (I guess the way you get there should be pretty obvious):

	Name 1	Name 2	Name 3
Name 1	-	-	0.34$
Name 2	6.66$	-	6$
Name 3	-	-	-

Question

So in short, my question is: Is there a way of creating the second table based on the first one (and maybe additional "helper" tables) using Excel's Pivot funcitonality? I would be really happy to get answers / tips for this. It would also be sufficient if you show me one case (i.e., split unequal by amount) and I'll implement the other cases based on that.

What I've tried so far

As already stated above, using a plain table I was already able to solve this task. However, the solution doesn't seem to be very elegant and thus I tried to accomplish it with by using a Pivot Table. I created one but I already struggle to get the same names in the rows & columns. If I get this working somehow, the next step would be to compute the correct values in the cells (I assume by introducing a new measure and a DAX-Formula, but I have no idea how :D)

Thanks for your help and feel free if you need more information!

Edit 1: Clarify the Result Matrix

As Jos stated, the result matrix maybe isn't that obvious unlike I throught, so I'd like to give one example:

Name 1 owes Name 3 0.34$ - why?

• Name 1 pays 4.99$ and this should be split equally (thus in particular name 3 owes name 1 1.66$.
• Name 1 pays 10$, however this doesn't account to name 3 since only name 1 and name 2 have to share this money
• Name 3 pays 10$ from which name 1 ones him 2$, name 2 6$ and name 3 2$.

In sum, name 3 owes name 1 1.33$ from the first payment, and name 1 owes name 3 2$ from the third payment. This can be summarized by: name 3 owes name 1 1.66 $ - 2$ = -0.33$ (which is the same as saying name 1 owes name 3 0.33$