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:

Policy NumberAMOUNTMATCH
1111140.00TRUE
11111-40.00TRUE
11111100.00FALSE
222221250.00TRUE
22222-1250.00TRUE
222223000.00FALSE

<tbody>
</tbody>
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
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,590
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:

awhite0320

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

<colgroup><col><col span="2"><col></colgroup><tbody>
</tbody>

<colgroup><col><col span="2"><col></colgroup><tbody>
</tbody>




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

Well-known Member
Joined
Jul 23, 2007
Messages
4,590
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:

awhite0320

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

Well-known Member
Joined
Jul 23, 2007
Messages
4,590
It must be the size of the data. Maybe someone else on the forum will pick this up and offer a better solution.
 

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,590
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
 

awhite0320

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

Well-known Member
Joined
Jul 23, 2007
Messages
4,590
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
 

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,590
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:

Watch MrExcel Video

Forum statistics

Threads
1,099,274
Messages
5,467,695
Members
406,549
Latest member
midcoastchris04

This Week's Hot Topics

Top