Matching debit and credit credit and debit transactions

[awhite0320]

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

 

[Trebor76]

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

 

[awhite0320]

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

 

[Trebor76]

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:

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

 

[awhite0320]

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

 

[Trebor76]

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

 

[Trebor76]

Hi Amy,

Let me know if this is any better / faster:

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

 

[awhite0320]

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!

 

[Trebor76]

This will put your formula into Col. P:

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

 

[Trebor76]

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