# [SOLVED]Help analyzing data in a table - Shared-Expense (who owes whom?)

1. ## [SOLVED]Help analyzing data in a table - Shared-Expense (who owes whom?)

I have a long list of group joint expenses

Trying to analyze the data and find logic for compensation between group members (who owes whom) so that the debt will be minimal for each group member.

In addition, I would like to determine that the maximum Debit or credit per member will not be higher than 5,000 at any given time.

I was able to analyze the data for the group members but I still have not found the right approach to do similar analysis by project (who owes whom by project)

In fact I would like to build kind of Dashboard management for Visual analysis at the level of a member (debt and credit) over time and also at the project level over time

Thanks in advance to anyone who can help resolve this or offer a simpler approach.

2. ## Re: Help analyzing data in a table - Shared-Expense (who owes whom?)

pease see attached worksheet.

3. ## Re: Help analyzing data in a table - Shared-Expense (who owes whom?)

Thanks for the effort to help with this!

I tried to understand the solution you proposed with the index but it seems that
the solution actually brings me back to the starting point...

I have added a simple table that summarizes the credit/debit of each group member with the same results you reached

I know how to manually calculate the balance (I added a new worksheet with the manual calculation (highlighted in yellow)

but I'm still missing the approach & technics how to calculate it automatically by logic so that
always the one who owes more will return first to the one who paid more until there is a balance.

Regarding the calculation of the credit/debit per project, i still have no idea how to do this
and also how to calculate the balance at any point and make sure that the maximum credit/debit
between the group members is max 5,000

thanks again

4. ## Re: Help analyzing data in a table - Shared-Expense (who owes whom?)

does the net total amount should be balanced at project level?

5. ## Re: Help analyzing data in a table - Shared-Expense (who owes whom?)

Originally Posted by Metoo7
pease see attached worksheet.
Sorry for off-topic interjection:

Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

6. ## Re: Help analyzing data in a table - Shared-Expense (who owes whom?)

Required total balance between the group members and also by project

Strange that the function that you provided {IF (E15 <> F15, SUM (VLOOKUP (E15: F15, \$ A \$ 14: \$ B \$ 21,2,)), "skip")}Does not work when i copy it to another cell..

I liked your idea of creating pairs
Ben = Tom
Tom = Ben

thanks

7. ## Re: Help analyzing data in a table - Shared-Expense (who owes whom?)

Thank you for your suggestion. It sounds reasonable.

8. ## Re: Help analyzing data in a table - Shared-Expense (who owes whom?)

It is an array formula, you have to press Ctrl+Shift+Enter after keying the formula.

Anyway, I have tried other 2 solutions (see attached) according to your explanation at #3, actually both of them apply the same principle.

solution A: please refer to worksheet "methohd 2"
step 1: copy original data range (A3:E53) to "G3:I53"
step 2: swap Debit and Credit columns
step 3: apply -1 and multiple it to original Amount column, change all figures to negative
step 4: ombine the datas, simply copy data from G3:I53 and paste them to A54
step 5: use pivot table to sum the new combined data range (A3:A103), filter positive numbers.
step 6: append the pivoted data as adjustement entries to the original data range, you may see result in worksheet "Test - after adj"

Solution B: refer to worksheet "method 1", acturally it was processed through Power Query(PQ), not sure whether your Excel have this function.
here is the PQ script

``Please Login or Register  to view this content.``
Hope it helps you.

9. ## Re: Help analyzing data in a table - Shared-Expense (who owes whom?)

Thank you so much for the help and approach that helped me a lot

In the end i succeeded to solve this with a few simple functions
I added a drop-down list that allows you to select a single project or the entire balance and check who owes to whom

[ solved ]

11. ## Re: Help analyzing data in a table - Shared-Expense (who owes whom?)

You are welcome. That's good to hear that you solve this problem successfully, although it seems I misunderstood your question.

12. ## Re: Help analyzing data in a table - Shared-Expense (who owes whom?)

Thanks again
I really appreciate that you have tried several times in the last few days to offer a solution