mrdgaf

01-24-2019, 05:20 AM

Hi everyone, I have scoured the net to try this my problem.

I have several lists that need to be compared for reconciliation purposes. Certain criteria need to match off between two rows before the next row is searched for, hence the first 5 variables and the how the value of these variables will change within a loop. The most important thing is that a single positive match does not cause multiple positive matches - some of these lists will have the same rows multiple times, so my idea is to delete matching rows until you are left with the undeleted/unmatched rows using loops. And then the process is repeated in reverse to identify mismatches in the other list. Ideally, there would be no need to delete anything and unmatched entries would just be highlighted if someone has such an elegant solution on hand.

Anyway, I have used the CSE match array formula within the sheet that works fine. But then I can't get it to work in VBA using the many examples online for using MATCH and EVALUATE. If someone could please help, that would be much appreciated.

You can see all the attempts I've made to try and get it to work at the bottom as comments.

Sub DeleteMatches()

'Declare variables

Dim Direction As Variant

Dim OrderType As Variant

Dim Amount As Variant

Dim CCY As Variant

Dim Rate As Variant

Dim RowCt As Integer

Dim Formula As Variant

Dim iRow As Integer

Dim colNum As Variant

Sheets("Example").Select

RowCt = Cells(Rows.Count, 11).End(xlUp).Row - 1 'creating variable for toral rows to cycle through

iRow = 2

Direction = Range("K" & iRow)

OrderType = Range("L" & iRow)

Amount = Range("M" & iRow)

CCY = Range("N" & iRow)

Rate = Range("P" & iRow)

Formula = "MATCH(1,((B:B="" & OrderType & "")*(C:C="" & Direction & "")*(D:D="" & Amount & "")*(E:E="" & CCY& "")*(H:H="" & Rate & "")),0)"

RowDelete = Evaluate(Formula)

MsgBox RowDelete

'colNum = Application.Match(1,((B1:B2=OrderType)*(C1:C2=Direction)*(D:D=Amount)*(E:E= CCY)*(H:H=Rate)),0)

'Formula = "MATCH(1,((B:B=OrderType)*(C:C=Direction)*(D:D=Amount)*(E:E=CCY)*(H:H=Rate)) ,0)"

'Formula = "MATCH(1,((B:B=L2)*(C:C=K2)*(D:D=M2)*(E:E=N2)*(H:H=P2)),0)"

'colNum = Worksheets("Example").Evaluate("MATCH(1,((B:B=OrderType)*(C:C=Direction)*(D:D=Amount)*(E:E=CCY)*(H:H=Rate)) ,0)")

End Sub

I have several lists that need to be compared for reconciliation purposes. Certain criteria need to match off between two rows before the next row is searched for, hence the first 5 variables and the how the value of these variables will change within a loop. The most important thing is that a single positive match does not cause multiple positive matches - some of these lists will have the same rows multiple times, so my idea is to delete matching rows until you are left with the undeleted/unmatched rows using loops. And then the process is repeated in reverse to identify mismatches in the other list. Ideally, there would be no need to delete anything and unmatched entries would just be highlighted if someone has such an elegant solution on hand.

Anyway, I have used the CSE match array formula within the sheet that works fine. But then I can't get it to work in VBA using the many examples online for using MATCH and EVALUATE. If someone could please help, that would be much appreciated.

You can see all the attempts I've made to try and get it to work at the bottom as comments.

Sub DeleteMatches()

'Declare variables

Dim Direction As Variant

Dim OrderType As Variant

Dim Amount As Variant

Dim CCY As Variant

Dim Rate As Variant

Dim RowCt As Integer

Dim Formula As Variant

Dim iRow As Integer

Dim colNum As Variant

Sheets("Example").Select

RowCt = Cells(Rows.Count, 11).End(xlUp).Row - 1 'creating variable for toral rows to cycle through

iRow = 2

Direction = Range("K" & iRow)

OrderType = Range("L" & iRow)

Amount = Range("M" & iRow)

CCY = Range("N" & iRow)

Rate = Range("P" & iRow)

Formula = "MATCH(1,((B:B="" & OrderType & "")*(C:C="" & Direction & "")*(D:D="" & Amount & "")*(E:E="" & CCY& "")*(H:H="" & Rate & "")),0)"

RowDelete = Evaluate(Formula)

MsgBox RowDelete

'colNum = Application.Match(1,((B1:B2=OrderType)*(C1:C2=Direction)*(D:D=Amount)*(E:E= CCY)*(H:H=Rate)),0)

'Formula = "MATCH(1,((B:B=OrderType)*(C:C=Direction)*(D:D=Amount)*(E:E=CCY)*(H:H=Rate)) ,0)"

'Formula = "MATCH(1,((B:B=L2)*(C:C=K2)*(D:D=M2)*(E:E=N2)*(H:H=P2)),0)"

'colNum = Worksheets("Example").Evaluate("MATCH(1,((B:B=OrderType)*(C:C=Direction)*(D:D=Amount)*(E:E=CCY)*(H:H=Rate)) ,0)")

End Sub