Matching debit and credit (accounting)

Hi and thanks for reading and helping me.


What I want: A formula that matches debit/credit amounts that sums to 0 on the same account.

For example:

account 1500 with amount +100 should be matched with account 1500 amount -100, but not with another +100.


I have almost solved this problem by reading other threads (thank you very much), but I still have an issue: Similar amounts in credit or debit also get interpreted as "TRUE" when they should not.

For example:

account 1500 with amount +110 should be matched with account 1500 amount -110, but not with another +110. Similarly double negative(credit) amounts should not be matched.

I think the problem is related to the formula using the absolute value, hence not being able to distinguish mathematical signs.

The formula I am using:

=SUMPRODUCT(($A$2:$A$999999=$A2)*(ABS($B$2:$B$999999)=ABS($B2)))>1

Please se my excel-attachment.

Thank you.

Thank you for the reply.

The problem was partially solved, and a new one arose:

The function does not work if there are more than one amount to match with, see attachment.

I would like the formula to just pick one of them so the SUM of all true amounts should be 0.


Side note: There are millions of accountants in the world who could be help with a formula like this - a bit weird that one doesn't exist (?).

Is something wrong with that formula? I can't paste it inn without getting an error. Im trying to paste it in C2.

Ok, some progress. Thank you.

But if i ad more transactions that should be matched which are similar, the function does not work, see attachment.

Any solution to this?

@Trukjo,

as you are from Norway, you will probably have to use semicolons ( ; ) instead of commas ( , ) in any formula which uses them as a list separator, which is dependent on your local settings.

Hope this helps.

Pete

Originally Posted by JeteMc

I am leaving the two helper columns (E:F) in this proposal because they add verification in that you can see in which row the match is established.
The formulas that populate the helper columns are similar to: =IFERROR(AGGREGATE(15,6,ROW(B$2:B$30)/(B$2:B$30<0)/(B$2:B$30=-B2)/(A$2:A$30=A2),COUNTIFS(B$2:B2,B2)),"")
Let us know if you have any questions.

Thank you.

After testing this formula I discovered it seems to not be able to handle similar numbers on different accounts. See attachment.

Any solution to this?