From-To Matrix to reconcile debts among pairs of people

[arveearv]

Hi:

I'm not even sure what to call this kind of chart. A win-loss table? A balance matrix??

Let's say there are 3 backpackers travelling the world. They pay cash everywhere and are always lending and borrowing money from each other. Every time they lend each other money, they track it in a simple 4 column table: FROM, TO, AMOUNT, CURRENCY. See sample attached.

At the end of the trip, we want to generate summary tables like the ones attached [note that there would be a Lookup Table for exchanging all currencies into U.S. dollars]. This needs to be scalable for any number of "backpackers". The challenge I'm having is that the "givers" are also "receivers". Maybe this requires a pivot table of a pivot table? Any ideas?

Thanks very much!

[6StringJazzer]

Welcome to the Forum arveearv!

My first thought is to use a pivot table. You also need another column converting foreign currencies to US$. You mentioned that but I don't see it.

I'm not sure how you plan to reconcile the debts. You have one figure per person, rather than one reconciliation for each pair. Do the debtors put their money in a pot and the creditors then take their money out of the pot? If that's the case then you don't really need the matrix. You just need the net, also shown in my file.

Just attach the Excel file. It's easier than taking a screenshot first and then attaching that. The paper clip icon does not work for attachments. To attach a file, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.

[arveearv]

Thank you 6stringjazzer!

Yes, you have largely solved my problem and the solution looks like it will be simpler than I thought.

You asked whether the participants reconcile each debt independently or put their money in a collective pot. They will pay and draw from the collective pot, but in the case of disputes they will want them to see a summary of the trades between them. In the pivot table, is it possible to force all of the participants' names into the headers of both the rows and columns, even in instances where a participant, say, appears as a "to" but never a "from"?

The SumIf net summary (G1:H4) that you created is great. Is there a way for this set of formulas -- including the source names in Column G -- to automatically extend/update as new participant names appear in the line-entry list? Assume that new backpackers can enter the mix at any time. (This is all a simplification of my actual scenario, which is a bunch of clinicians in a medical practice who step in to take on each other's patients for procedures of varying prices.)

Amazingly helpful so far. Thanks also for the tip about attaching spreadsheets rather than images and also for sharpening my thread's title (maybe a moderator did this).

[6StringJazzer]

In the pivot table, is it possible to force all of the participants' names into the headers of both the rows and columns, even in instances where a participant, say, appears as a "to" but never a "from"?

No, a pivot table only knows what data there is, not what data there isn't. You could seed the data with zeroes to force that. There might be a completely different approach using something like Power Pivot (which I am not familiar with).

The SumIf net summary (G1:H4) that you created is great. Is there a way for this set of formulas -- including the source names in Column G -- to automatically extend/update as new participant names appear in the line-entry list?

I'll give that a little thought. I'm sure there is a way but not a straightforward way.

Thanks [...] also for sharpening my thread's title (maybe a moderator did this).

That was me. I probably should have left an edit comment for that.

[arveearv]

Thanks very much. It sets me up well.

I'll give that a little thought. I'm sure there is a way but not a straightforward way.

On the question of updating the SUMIF formulas as the list of "backpackers" keeps growing, let's say I already have a range of cells on a different sheet with the names of all the backpackers (for data validation purposes). Can that range be used to set up the SUMIF summary?

Attached is the validation list added to our test file (Sheet 2), with some new data points (red section) and data validation. I haven't refreshed analyses yet.

Thanks for the huge help on this forum!

[JeteMc]

See if this will do what you need.
The list of names in column I is linked to the data validation list using: =IF(Sheet2!A2="","",Sheet2!A2)
The list of names in row 12 is populated using: =INDEX($I14:$I21,COLUMNS($A1:A1))
The values in J14:Q21 are populated using: =IF(COUNTIFS($A$2:$A$7,$I14,$B$2:$B$7,J$12)=0,"",COUNTIFS($A$2:$A$7,$I14,$B$2:$B$7,J$12)&" "&SUMIFS($E$2:$E$7,$A$2:$A$7,$I14,$B$2:$B$7,J$12))
The amounts each backpacker is owed are populated using*: =SUMPRODUCT(($I14:$I21=J12)*(IFERROR(VALUE(RIGHT($J14:$Q21,LEN($J14:$Q21)-SEARCH(" ",$J14:$Q21))),0)))
The amounts each backpacker owes are populated using*: =SUMPRODUCT(IFERROR(VALUE(RIGHT(J14:J21,LEN(J14:J21)-SEARCH("",J14:J21)-1)),0))
The net amounts for each backpacker are populated using: =J9-J10
*Denotes an array entered formula which is confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula.
Note that the amounts is owed, owes and net are placed above the table to allow for expansion.
Let us know if you have any questions.

[arveearv]

Thank you. A very elegant solution!

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.