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

1. ## 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. ## 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

3. ## 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

Originally Posted by 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

4. ## 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

5. ## 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. ## 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. ## 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
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
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. ## 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.

9. ## 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. ## 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