Solved: Excel Macro for trade reconciliation

[buhay]

Does anyone know an excel macro for trade reconciliation. The two trades are located on two different sheets.

For example:

Sheet1
Col A Col B Col C Col D Col E Col F
ISIN B/S TD SD Amount Price

Row 1 Ch0XXX B 8.7.10 11.7.10 6000 15,9

Row 2 US0xxx S 2.7.10 5.7.10 500 12

Row 3 XXx989 S 4.7.10 7.7.10 60 19

Row 4 DT3243 B 9.7.10 13.7.10 50 13


Sheet2

Sheet1
Col A Col B Col C Col D Col E Col F
ISIN B/S TD SD Amount Price

Row 1 Ch0XXX B 8.7.10 11.7.10 6000 15,9

Row 2 US0xxx S 2.7.10 5.7.10 450 12

Row 3 XXx989 S 4.7.10 7.7.10 60 15

Row 4 DT3243 B 9.7.10 13.7.10 50 13


The macro should loop through the two excel sheets and highlight the cells in red that don't match with each other, in this case the cell(Row 2 and Column E) and the cell(Row 3 and Column F) and copy and paste the two trades(Row 2 and Row 3) to Sheet3.

Any help is highly appreciated

[shawnhet]

Am I correct in assuming that the ISIN (column A) is a unique identifier of the transaction? That is to say, that there are not multiple trades with the same ISIN on the same sheet. I have attached a sample sheet that allows you to point out the differences btw the two if the ISIN is only listed once on each sheet.

You can copy the formulas I have added onto your sheet1 and Sheet2 from columns G-K(G lets you know if there is a difference in column2, H in column 3 ...) and copy them down through the whole sheet and use the autofilter to find ones that differ, you can then format them or copy them as needed.

Assuming that this is more or less what you are looking for, you can obviously automate this using VBA, but let's see if this is on track first.

Cheers, :)

[buhay]

Thank you very much. I will try to write a macro and post it as soon as possible.

[rhughes]

I believe i need to post something in order to access the Macro you wrote......here it is.

Thanks

[buhay]

As promised here is the macro(though I didn't write it myself) but for some reason it's not perfect. It never loops through all the rows on sheet1 and always leaves the last row unreconciled(please see sample file for further clarification). Does anyone know how to fix it? Thanks in advance

Assuming you have 2 sheets labeled "Sheet1", "Sheet2",

Sub Reconciliation()

perfectmatch = "Yes"
rowmax = Application.WorksheetFunction.CountA(Columns("A:A"))

'Define Sheet 1 Elements
Sheets("Sheet1").Select

For i = 2 To rowmax

ISIN = Cells(i, 1)
BS = Cells(i, 2)
TD = Cells(i, 3)
SD = Cells(i, 4)
AMT = Cells(i, 5)
Price = Cells(i, 6)

'Check for ISIN on Sheet 2
ISINcheck = Application.WorksheetFunction.CountIf(Sheets("Sheet2").Columns("A:A"), ISIN)

If ISINcheck = 0 Then
Cells(i, 7).Value = "Unable to find ISIN on Sheet 2"
Else

'Find correct ISIN row
Sheets("Sheet2").Select
ISINrow = Application.WorksheetFunction.Match(ISIN, Columns("A:A"), 0)

'Check Sheet 2 Elements
If Cells(ISINrow, 1) <> ISIN Then
Cells(ISINrow, 1).Font.Color = -16776961
perfectmatch = "No"
End If

If Cells(ISINrow, 2) <> BS Then
Cells(ISINrow, 2).Font.Color = -16776961
perfectmatch = "No"
End If

If Cells(ISINrow, 3) <> TD Then
Cells(ISINrow, 3).Font.Color = -16776961
perfectmatch = "No"
End If

If Cells(ISINrow, 4) <> SD Then
Cells(ISINrow, 4).Font.Color = -16776961
perfectmatch = "No"
End If

If Cells(ISINrow, 5) <> AMT Then
Cells(ISINrow, 5).Font.Color = -16776961
perfectmatch = "No"
End If

If Cells(ISINrow, 6) <> Price Then
Cells(ISINrow, 6).Font.Color = -16776961
perfectmatch = "No"
End If

Cells(ISINrow, 7).Value = perfectmatch

Sheets("Sheet1").Select
perfectmatch = "Yes"
End If
Next i


End Sub

[buhay]

I forgot to include the attachement but now it's up

[slamet Harto]

The macro should loop through the two excel sheets and highlight the cells in red that don't match with each other, in this case the cell(Row 2 and Column E) and the cell(Row 3 and Column F) and copy and paste the two trades(Row 2 and Row 3) to Sheet3.

Any help is highly appreciated
what do you mean with copy and paste the two trades?
from your sample attached, I don;t find any data in the column E and F

Is that mean to compare row by row in each sheet? if so, you can use this code
Sub bla()
Dim i As Long
Dim XRow As Long
XRow = Sheet1.Cells(Rows.Count, 2).End(xlUp).Row
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
For i = 2 To XRow
If Sheet1.Range("B" & i) <> Sheet2.Range("B" & i) Then
Sheet1.Cells(i, 2).Interior.ColorIndex = 3
Sheet2.Cells(i, 2).Interior.ColorIndex = 3
End If
Next i
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic

End With

Sheets("Sheet1").Select
End Sub

[buhay]

thanks, that's exactely what I have been looking for.
The procedure I posted doesn't copy the cells that don't match with each other and paste them to sheet3. If you know such a code that only copy and paste the mistaken rows(with the cells that don't match with each other, you're welcome to post it here.

Thanks again for helping me out

[slamet Harto]

bla()
Dim i As Long
Dim XRow As Long
XRow = Sheet1.Cells(Rows.Count, 2).End(xlUp).Row
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
For i = 2 To XRow
If Sheet1.Range("B" & i) <> Sheet2.Range("B" & i) Then
Sheet1.Cells(i, 2).Interior.ColorIndex = 3
Sheet1.Cells(i, 2).Offset(0, -1).Resize(1, 2).Copy Sheet3.Cells(i, 1)
Sheet2.Cells(i, 2).Interior.ColorIndex = 3
Sheet2.Cells(i, 2).Copy Sheet3.Cells(i, 3)
End If
Next i
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic

End With

Sheets("Sheet3").Select
End Sub

[buhay]

Thanks a lot mate

[slamet Harto]

No problem

Please clean this thread by marking as solve. thanks