Matching debit and credit credit and debit transactions

awhite0320

New Member
Joined
Sep 20, 2018
Messages
5
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:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Policy Number[/TD]
[TD]AMOUNT[/TD]
[TD]MATCH[/TD]
[/TR]
[TR]
[TD]11111[/TD]
[TD]40.00[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]11111[/TD]
[TD]-40.00[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]11111[/TD]
[TD]100.00[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD]22222[/TD]
[TD]1250.00[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]22222[/TD]
[TD]-1250.00[/TD]
[TD]TRUE[/TD]
[/TR]
[TR]
[TD]22222[/TD]
[TD]3000.00[/TD]
[TD]FALSE[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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:
Upvote 0
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.

[TABLE="width: 357"]
<colgroup><col><col span="2"><col></colgroup><tbody>[TR]
[TD="align: right"][TABLE="width: 357"]
<colgroup><col><col span="2"><col></colgroup><tbody>[TR]
[TD="align: center"]Policy number[/TD]
[TD="align: center"]Amount[/TD]
[TD="align: center"]Unique Number[/TD]
[TD="align: center"]Result[/TD]
[/TR]
[TR]
[TD="align: center"]44444[/TD]
[TD="align: center"]250[/TD]
[TD="align: center"]4444250[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]44444[/TD]
[TD="align: center"]250[/TD]
[TD="align: center"]4444250[/TD]
[TD="align: center"]2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]




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
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
It must be the size of the data. Maybe someone else on the forum will pick this up and offer a better solution.
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top