Only joining specific rows based on plus/minus sign

[eldansky]

Hi,

I'm struggling with a task for my studies. To put it simply, I have two different Excel files as input data. One of them (Excel 1) contains account numbers and their corresponding category according to the german GAAP (generally accepted accounting principles). The second file (Excel 2) contains the account numbers and their balances. My aim is to join the two files based on account number, and aggregate the balances to see the sum of the individual GAAP-Items. 

My problem:

In Excel 1, some accounts are listed two times. One of them would be correct if the balance is positive, the other one would be correct if the balance is negative.

See my attached image for an example. If the Account "X123123" has a positive balance, it belongs to "claims on banks", if the balance is negative it belongs to "liabilities to banks".

Since I join the two Excel files based on the Account number, and the Account "X123123" appears two times in Excel 1, it joins an examplary balance of EUR 500 two times, one time to the Assets, one time to Liabilities.

Is there a way to assign the Account to the right GAAP-Item based on the balance BEFORE joining it with Excel 2? I struggled here because I can only find the balance in Excel 2, but I need to know if the balance is positive or negative to assign it to Assets/Liabilites.

Glad for any suggestions! Thanks!

[eldansky]

I had the same idea, it solves the issue for S110004 and S110600, however it messes up other accounts in return.

Below you can see the output after adding the Sort:

As you can see for S110004, the category A03 is correct (positive balance), but S100800 would only be A01 if the balance would be positive as well.

Since it's negative, S100800 needs to be P01. I need to find a way to somehow form a connection between "negative balance" equals Passiva/P0X etc,

and "positive balance" equals Aktiva/A 0X. 

The sort tool would work perfectly if every balance of accounts that appear two times was positive. But since some balances are negative, the select tool can't work because it can either always keep last row or always keep first row. But the input needed would be "If Balance is >0, keep last row; if balance is <0, keep first row" something like that. 

Thanks 🙂 

[atcodedog05]

Hi @eldansky 

What if balance is 0 should it keep both?

[eldansky]

If the balance is 0, the account can be ignored completely, since it will not add anything to the amounts I want to see in my final output data.

[atcodedog05]

Hi @eldansky 

You can split data, perform action and join it back like below.

[atcodedog05]

@eldansky 

If you dont need them remove them with a filter at the last😅

[eldansky]

We're getting there! 😄

This is the current result:

The yellow numbers have to be the same (ignoring plus/minus), since Assets and Liabilities always have to be the same amount in a balance sheet. There still have to be some accounts that are assigned wrong, but I can't find them quickly. I'll dig into it tomorrow and I'll double check if the input data (the two excel files) are correct. I'll try to get my hands on other sample data from another period and run the workflow with another data set. In my opinion the workflow should work so maybe the error is in the input data. 

Thank you again, I'll update you tomorrow!

Cheers