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