# Matching and Clearing Transactions with multiple criteria

#### NeilATaylor

##### Board Regular
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

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

#### Lewiy

##### Well-known Member
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).
Code:
``````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

Threads
1,191,717
Messages
5,988,258
Members
440,146
Latest member
rgomes8

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

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