Create a new list, with calculations. Tough one

[Weduku]

Hi,

I have tough problem that I can't find any solutions to. I have been trying to solve this problem for almost a day, and can't find any way to do this without doing it manually. Look at the link in order the open the attachment i am working with. Create a new list, with calculations
Column A, B and C are the original list. Column E, F and G shows how I would like the list to look.

I have this list with around 500 rows, that shows debt between different companies (more than 50 companies). The list shows different loans, and there are therefore often more than one loan between the companies. I would like to calculate easily how much company 1 owes company 2 etc. (and in my real document, how much company 8 owes company 47)

The original list consist of all the loans between the different companies. The important this is that the company in column B (counterparty) owes the company in column A (company). There can also be several loans between the same companies, so that company 1 can be both in column A and B against the same company. And also whether you should subtract or add the number depends on column A and B. For example in cell G3 in the result list I made. The formula is C3-C9+C21. In this example, the positives are because 2 owes company 1 money, and the negative are the opposite.

I would like at the end to have a list of which company owes which company money. I am unsure how to generate the list in column E, F and G.


Any help would be greatly appreciated! ​

 

[jasonb75]

Look at the link in order the open the attachment i am working with.

The link is a cross post to another forum. Please be aware that all excel help forums have rules about cross posting, some stricter than others, but the main point of them is that you clearly state all of the places that you have asked the same question (with links) so that people can see what progress (if any) has already been made. The forum that you have linked to above also has this rule.

You will need to provide a data sample here, attachments on cross posts can only be downloaded by people with accounts registered on the host forum.

 

[GraH]

Hi,
I could not look at your Excel file, but it sound like something I did in the past.
And I happen to know there is even a tutorial on YouTube on something simular. Both a solution with formulae and Power Query are offered.

Reconciling Christmas Spending with SUMIFS, IF and Good Spreadsheet Layout - Oz du Soleil

This video revisits a challenge from a few years ago. 3 siblings needed a simple way to reconcile who owed what to whom after all the Christmas gits had been purchased. For example: If Bo agreed to chip in $200 on gifts that Jay bought, and Jay agreed to chip in $90 on gifts purchased […]

ozdusoleil.com

Reconciling Christmas Payments 2: Merging a Table with Itself - Oz du Soleil

In the previous video I showed how to reconcile Christmas payments between 3 siblings. It seemed that classic Excel using SUMIFS was a better solution that Power Query. However, after posting the video, Miguel Escobar offered a simple Power Query solution. But it’s tricky. It involves merging a...

ozdusoleil.com