+ Reply to Thread
Results 1 to 7 of 7

Matching debit and credit (accounting)

  1. #1
    Registered User
    Join Date
    12-04-2019
    Location
    Steinkjer, Norway
    MS-Off Ver
    10
    Posts
    5

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

  2. #2
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    1,909

    Re: Matching debit and credit (accounting)

    Try this: =SUMPRODUCT(($A$2:$A$16=A2)*($B$2:$B$16=-B2))>=1
    Click the * to say thanks.

  3. #3
    Registered User
    Join Date
    12-04-2019
    Location
    Steinkjer, Norway
    MS-Off Ver
    10
    Posts
    5

    Re: Matching debit and credit (accounting)

    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 (?).
    Attached Files Attached Files

  4. #4
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    1,909

    Re: Matching debit and credit (accounting)

    Try this formula:
    =IF(COUNTIFS($B$2:B2,B2,$A$2:A2,A2)>1,FALSE,IF(COUNTIFS($B:$B,-B2,$A:$A,A2)>0,TRUE,FALSE))

  5. #5
    Registered User
    Join Date
    12-04-2019
    Location
    Steinkjer, Norway
    MS-Off Ver
    10
    Posts
    5

    Re: Matching debit and credit (accounting)

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

  6. #6
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    2016
    Posts
    1,909

    Re: Matching debit and credit (accounting)

    There is nothing wrong with the formula. I used in your sample. What error are you getting?
    I've attached the file as well.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    12-04-2019
    Location
    Steinkjer, Norway
    MS-Off Ver
    10
    Posts
    5

    Re: Matching debit and credit (accounting)

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1