+ Reply to Thread
Results 1 to 12 of 12

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

  1. #1
    Registered User
    Join Date
    07-08-2014
    Location
    israel
    MS-Off Ver
    10
    Posts
    38

    [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.
    Attached Files Attached Files
    Last edited by sryair; 10-01-2020 at 09:21 AM. Reason: SOLVED

  2. #2
    Registered User
    Join Date
    04-15-2013
    Location
    Melbourne
    MS-Off Ver
    Office 365
    Posts
    62

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

    pease see attached worksheet.
    Attached Files Attached Files
    Row row row your boat
    Gently down the stream

  3. #3
    Registered User
    Join Date
    07-08-2014
    Location
    israel
    MS-Off Ver
    10
    Posts
    38

    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
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    04-15-2013
    Location
    Melbourne
    MS-Off Ver
    Office 365
    Posts
    62

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

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

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    41,334

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

    Quote Originally Posted by Metoo7 View Post
    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.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  6. #6
    Registered User
    Join Date
    07-08-2014
    Location
    israel
    MS-Off Ver
    10
    Posts
    38

    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. #7
    Registered User
    Join Date
    04-15-2013
    Location
    Melbourne
    MS-Off Ver
    Office 365
    Posts
    62

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

    Thank you for your suggestion. It sounds reasonable.

  8. #8
    Registered User
    Join Date
    04-15-2013
    Location
    Melbourne
    MS-Off Ver
    Office 365
    Posts
    62

    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.
    Attached Files Attached Files
    Last edited by Metoo7; 10-01-2020 at 08:25 AM.

  9. #9
    Registered User
    Join Date
    07-08-2014
    Location
    israel
    MS-Off Ver
    10
    Posts
    38

    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

    Thanks again for your effort
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    07-08-2014
    Location
    israel
    MS-Off Ver
    10
    Posts
    38

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

    [ solved ]
    Last edited by sryair; 10-01-2020 at 09:24 AM.

  11. #11
    Registered User
    Join Date
    04-15-2013
    Location
    Melbourne
    MS-Off Ver
    Office 365
    Posts
    62

    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. #12
    Registered User
    Join Date
    07-08-2014
    Location
    israel
    MS-Off Ver
    10
    Posts
    38

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 12
    Last Post: 03-27-2019, 03:14 PM
  2. [SOLVED] Pivot Table for Analyzing Survey Results by Manager
    By GregStewartPTC in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 01-25-2019, 10:16 AM
  3. Analyzing data
    By Reddman in forum Hello..Introduce yourself
    Replies: 2
    Last Post: 11-05-2018, 12:28 PM
  4. Replies: 1
    Last Post: 03-23-2017, 03:53 PM
  5. [SOLVED] Travel Shared Expense Calculator
    By Vahan976 in forum Excel General
    Replies: 4
    Last Post: 05-15-2015, 01:08 AM
  6. analyzing pivot table
    By ammartino44 in forum Excel General
    Replies: 0
    Last Post: 09-04-2014, 07:28 PM
  7. Help analyzing my data.
    By Shindel in forum Excel General
    Replies: 6
    Last Post: 06-03-2011, 06:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts