Matching debit and credit credit and debit transactions
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Matching debit and credit credit and debit transactions
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Matching debit and credit credit and debit transactions

    I have a approximately 35,000 rows of general ledger transactions. The transactions contain both debit and credit amounts and various policy numbers. I need to evaluate the rows to identify the rows, by policy number, that do not net to zero. I would like for the rows that do not zero out return a value of FALSE and the rows that zero out return a value of TRUE. For example:

    Policy Number AMOUNT MATCH
    11111 40.00 TRUE
    11111 -40.00 TRUE
    11111 100.00 FALSE
    22222 1250.00 TRUE
    22222 -1250.00 TRUE
    22222 3000.00 FALSE
    The false rows are my not matched items. I have a countif formula that i can use but I have to update the range at each change in policy number, which is very tedious when you have 35,000 rows. Can I accomplish my need with an Excel formula or VBA code?

    Thanks so much!!
    Amy

  2. #2
    Board Regular
    Join Date
    Jul 2007
    Location
    Sydney
    Posts
    4,366
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Matching debit and credit credit and debit transactions

    Hi Amy,

    Welcome to MrExcel!!

    Here's one possible way by incorporating two helper columns - the assumption I've made is that policy numbers are in Col. A and amounts are in Col. B and the last entry is in Row 35,000 (change to suit):

    Put this formula in cell C2...

    =A2&ABS(B2)

    ...and fill down to cell C35000

    Put this formula in cell D2...

    =COUNTIFS($C$2:$C$35000,C2)

    ...and fill down to cell D35000

    Column D should now show unique entries (flagged by 1) and duplicate entries (greater than 1).

    Hope this helps,

    Robert
    Last edited by Trebor76; Sep 20th, 2018 at 01:05 AM.

  3. #3
    New Member
    Join Date
    Sep 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Matching debit and credit credit and debit transactions

    Thanks, Robert!

    This seems to work in some instances and others it doesn't. Based on your formula, it is expected that anything greater than 2 should net to zero. For odd number results 3,5,7 etc., I have either two debits and 1 credit or two credits and 1 debit, which do not zero out. And some even number results I have two debits or two credits which will not zero out.

    in the example below this should be unmatched since it does not have a debit and a credit, only two debits.

    Policy number Amount Unique Number Result
    44444 250 4444250 2
    44444 250 4444250 2




    Quote Originally Posted by Trebor76 View Post
    Hi Amy,

    Welcome to MrExcel!!

    Here's one possible way by incorporating two helper columns - the assumption I've made is that policy numbers are in Col. A and amounts are in Col. B and the last entry is in Row 35,000 (change to suit):

    Put this formula in cell C2...

    =A2&ABS(B2)

    ...and fill down to cell C35000

    Put this formula in cell D2...

    =COUNTIFS($C$2:$C$35000,C2)

    ...and fill down to cell D35000

    Column D should now show unique entries (flagged by 1) and duplicate entries (greater than 1).

    Hope this helps,

    Robert

  4. #4
    Board Regular
    Join Date
    Jul 2007
    Location
    Sydney
    Posts
    4,366
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Matching debit and credit credit and debit transactions

    I based my solution on the data you initially posted which seemed to work.

    Though there's a formula way to do this I'm sure (especially with the aid of helper columns), try this macro:

    Code:
    Option Explicit
    Sub Macro1()
    
        Dim lngMyRow   As Long
        Dim lngLastRow As Long
        Dim rngMyCell  As Range
        Dim strPolicy  As String
        Dim dblAmt     As Double
        Dim blnMatch   As Boolean
        
        Application.ScreenUpdating = False
        
        lngLastRow = Range("A:B").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        
        For Each rngMyCell In Range("A2:A" & lngLastRow)
            If Len(Range("C" & rngMyCell.Row)) = 0 Then
                strPolicy = rngMyCell
                dblAmt = Val(Range("B" & rngMyCell.Row))
                blnMatch = False
                For lngMyRow = 2 To lngLastRow
                    If Range("A" & lngMyRow) = strPolicy And Range("B" & lngMyRow) = dblAmt * -1 And Len(Range("C" & lngMyRow)) = 0 Then
                        Range("C" & rngMyCell.Row) = True
                        Range("C" & lngMyRow) = True
                        blnMatch = True
                        Exit For
                    End If
                Next lngMyRow
                If blnMatch = False Then
                    Range("C" & rngMyCell.Row) = False
                End If
            End If
        Next rngMyCell
        
        Application.ScreenUpdating = True
        
        MsgBox "Amounts have now been matched.", vbInformation
    
    End Sub
    Assumes data is in columns A and B, headers in row 1 and the output will be in column C and that the absolute value of the debit and credit amounts equal just their signage are different i.e. 250 v -250.

    Thanks,

    Robert
    Last edited by Trebor76; Sep 20th, 2018 at 08:40 PM.

  5. #5
    New Member
    Join Date
    Sep 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Matching debit and credit credit and debit transactions

    Robert,
    I running the macro and Excel just spins. I am unsure why it will not run. I copied the entire code and clicked ALT F11 to open the VBA module window. Is that correct?

    Thanks,
    Amy

  6. #6
    Board Regular
    Join Date
    Jul 2007
    Location
    Sydney
    Posts
    4,366
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Matching debit and credit credit and debit transactions

    It must be the size of the data. Maybe someone else on the forum will pick this up and offer a better solution.

  7. #7
    Board Regular
    Join Date
    Jul 2007
    Location
    Sydney
    Posts
    4,366
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Matching debit and credit credit and debit transactions

    Hi Amy,

    Let me know if this is any better / faster:

    Code:
    Option Explicit
    Sub Macro2()
        
        Dim sFind As String, sAddr As String
        Dim rRng As Range, rCl As Range, rFnd As Range
        Dim rngMyCell As Range
        Dim dblAmt As Double
        Dim blnMatch As Boolean
        Dim xlnCalcMethod As XlCalculation
        
        With Application
            .ScreenUpdating = False
            xlnCalcMethod = .Calculation
            .Calculation = xlCalculationManual
        End With
        
        For Each rngMyCell In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
        
            If Len(rngMyCell.Offset(0, 2)) = 0 Then
                sFind = CStr(rngMyCell)
                dblAmt = Val(rngMyCell.Offset(0, 1))
                blnMatch = False
                
                'Adapted from Trebor76 and royUK code from here: _
                https://www.ozgrid.com/forum/forum/help-forums/excel-general/116879-findall-in-vba
                Set rRng = Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
                With rRng
                    Set rCl = .Find(sFind, LookIn:=xlValues)
                    If Not rCl Is Nothing Then
                        sAddr = rCl.Address
                        If Val(rCl.Offset(0, 1)) = dblAmt * -1 Then
                            rngMyCell.Offset(0, 2).Value = True
                            rCl.Offset(0, 2).Value = True
                            blnMatch = True
                        End If
                        If blnMatch = False Then
                            Do
                                If rFnd Is Nothing Then
                                    Set rFnd = rCl
                                End If
                                Set rCl = .FindNext(rCl)
                                If Val(rCl.Offset(0, 1)) = dblAmt * -1 Then
                                    rngMyCell.Offset(0, 2).Value = True
                                    rCl.Offset(0, 2).Value = True
                                    blnMatch = True
                                    Exit Do
                                End If
                            Loop While Not rCl Is Nothing And rCl.Address <> sAddr
                        End If
                        If blnMatch = False Then
                            rngMyCell.Offset(0, 2).Value = False
                        End If
                    End If
                End With
                
            End If
            
        Next rngMyCell
        
        With Application
            .ScreenUpdating = False
            .Calculation = xlnCalcMethod
        End With
        
        MsgBox "Numbers (Col B) have been matched to Policy Numbers (Col. A).", vbInformation
    
    End Sub
    Robert

  8. #8
    New Member
    Join Date
    Sep 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Matching debit and credit credit and debit transactions

    Robert,
    I tried this one as well and let it run for 10 minutes and it just continued to spin. I have another formula that works quite well but I have to manually change the range =COUNTIF($O$33:$O$73,O33)>COUNTIF($O$33:$O$73,-O33) but I have to update the range where the policy number changes to only look at each specific policy number rather than all policy numbers within the sheet.
    I really appreciate your help!

  9. #9
    Board Regular
    Join Date
    Jul 2007
    Location
    Sydney
    Posts
    4,366
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Matching debit and credit credit and debit transactions

    This will put your formula into Col. P:

    Code:
    Option Explicit
    Sub Macro1()
    
        Dim rngMyCell As Range
        Dim lngRowStart As Long
        Dim lngRowEnd As Long
        Dim xlnCalcMethod As XlCalculation
        
        With Application
            .ScreenUpdating = False
            xlnCalcMethod = .Calculation
            .Calculation = xlCalculationManual
        End With
        
        'Assumes policy codes are in Col. N. Change to suit.
        'Note the macro needs the entire dataset to have been sorted by policy.
        For Each rngMyCell In Range("N2:N" & Range("N" & Rows.Count).End(xlUp).Row)
            If lngRowStart = 0 Then
                lngRowStart = rngMyCell.Row
            ElseIf rngMyCell.Offset(-1, 0) <> rngMyCell Then
                lngRowStart = rngMyCell.Row
            End If
            If rngMyCell.Offset(1, 0) <> rngMyCell Then
                'Output formula to Col. P. Change to suit.
                Range("P" & lngRowStart & ":P" & rngMyCell.Row).Formula = _
                    "=COUNTIF($O$" & lngRowStart & ":$O$" & rngMyCell.Row & ",O" & lngRowStart & ")>COUNTIF($O$" & lngRowStart & ":$O$" & rngMyCell.Row & ",-O" & lngRowStart & ")"
            End If
        Next rngMyCell
        
        With Application
            .ScreenUpdating = False
            .Calculation = xlnCalcMethod
        End With
        
        MsgBox "Process complete.", vbInformation
    
    End Sub
    Note though that matched debits and credits within each policy number range are flagged as FALSE while unmatched entries are flagged as TRUE. This is oppose to your original post.

    Robert

  10. #10
    Board Regular
    Join Date
    Jul 2007
    Location
    Sydney
    Posts
    4,366
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Matching debit and credit credit and debit transactions

    Hi Amy,

    Based on this thread here's a single solution method for a range that spans from Row 2 to Row 35000 (change to suit):

    =COUNTIFS($N$2:$N$35000,N2,$Q$2:$Q$35000,-Q2)>0

    Just needs to be put into the first output cell and copied down as required (to Row 35000 in my example).

    Regards,

    Robert
    Last edited by Trebor76; Sep 26th, 2018 at 03:29 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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