Matching and Clearing Transactions with multiple criteria

[NeilATaylor]

Good evening,
I have received a lot of information and advice from this board on my account reconciliation project and I'm nearly there! Its really going to save everyone a lot of time usung it. The last phase of the macro is going to be the most difficult...

If I was left with the following transactions,
25014078 YE-Accrual Office Improvements 5,000.00
25014087 YE-Accrual Office Improvements -5,000.00
25014078 YE-Accrual Business Rates 6,000.00
25015160 YE-Accrual Office Service Charges 6,000.00
25014087 YE-Accrual Business Rates - 6,000.00
25014099 YE-Accrual Office Service Charges -6,000.00

We can see exactly what items contra. What I want to do is breakdown this into logical steps for a macro. Some ideas I have had:

Sort by ABS value, run a macro that if (amount) C1+C2=0 and (transaction number) A1 < A2= mark as "True" and delete transaction from sheet...Wouldn't clear the two debit £6000 amounts above though.


Does anyone have any ideas on how to get something like this done? Any idea at all would be appreciated!

Cheers

 

[Lewiy]

I hope I’ve understood what you want, this should put “TRUE” in column D next to any transactions that contra off against another item (column D would need to be empty before hand mind).

Sub MyMacro()
Dim limit As Long
Dim c As Long
Dim d As Long
limit = Cells(Rows.Count, 1).End(xlUp).Row
For c = limit To 1 Step -1
    For d = 1 To limit
        If c <> d And Cells(c, 4) <> True And Cells(d, 4) <> True Then
            If Cells(c, 2) = Cells(d, 2) And Cells(c, 3) + Cells(d, 3) = 0 And Cells(c, 1) > Cells(d, 1) Then
                Cells(c, 4) = True
                Cells(d, 4) = True
            End If
        End If
    Next d
Next c
End Sub

Edit: corrected sub name