Matching based on 2 criterias

[alvbnp]

I have 2 worksheets for doing matching. The matchings goes like if column A = column A and column B = Column B in both worksheets, then match column E, and highlight those unmatched cell in column E in sheet1. Besides that, also highlight those items which appear in sheet1 but not found in sheet2.

Can this be done just using built in formula in Excel or I need a macro for it?

Thanks for helping.

 

[agihcam]

sure;

Sub test()
Dim i As Long
For i = 1 To Sheets("Sheet1").Range("a" & Rows.Count).End(xlUp).Row
    With Sheets("Sheet2").Columns("a")
    Set C = .Find(Cells(i, "a").Value, LookIn:=xlValues)
    If Not C Is Nothing Then
    FF = C.Address
    Do
    If C.Offset(, 1) = Cells(i, "b") _
    And C.Offset(, 4) <> Cells(i, "e") Then
      Sheets("Sheet1").Cells(i, "e").Interior.ColorIndex = 6
      C.Offset(, 4).Interior.ColorIndex = 6
          End If
      Set C = .FindNext(C)
      Loop Until FF = C.Address
Else
Sheets("Sheet1").Cells(i, "e").Interior.ColorIndex = 4
    End If
    End With
Next
End Sub

 

[alvbnp]

It's doesn't seems to work correctly, I noticed it wrongly highlighted cells in column E in green colour.

While the code is actually using column A & B to do the matching, if possible, i think a better idea is to highlight those missing in column A & B. But for my case, highlight in column B for missing item is good enough if highlighting both is too much of work.

 

[agihcam]

how about?
else, you need to provide again a sample data;

Sub test()
Dim i As Long
For i = 1 To Sheets("Sheet1").Range("a" & Rows.Count).End(xlUp).Row
    With Sheets("Sheet2").Columns("a")
    Set c = .Find(Cells(i, "a").Value, LookIn:=xlValues)
    If Not c Is Nothing Then
    FF = c.Address
    Do
    If c.Offset(, 1) = Cells(i, "b") _
    And c.Offset(, 4) <> Cells(i, "e") Then
      Sheets("Sheet1").Cells(i, "e").Interior.ColorIndex = 6
      c.Offset(, 4).Interior.ColorIndex = 6
          End If
         Set c = .FindNext(c)
      Loop Until FF = c.Address
Else
Sheets("Sheet1").Cells(i, "a").Resize(, 2).Interior.ColorIndex = 4
    End If
    End With
Next
End Sub

 

[alvbnp]

Still doesn't work. Maybe I rephrase a bit.

The current code working on searching column A and B in both sheets in order to match column E. Somehow, if either column A or B is not found in either one of the sheets, it won't highlight anything. This give false result.

Eg:
sheet1
1 | a | 100
2 | a | 100
2 | b | 100

sheet2
1 | a | 100
2 | b | 100

the second item (2 | a | 100) is missing in sheet2, should be highligted. The current coding simply skip it.

 

[agihcam]

ok. try this;

Sub test()
Dim i, ii As Long
For i = 1 To Sheets("Sheet1").Range("a" & Rows.Count).End(xlUp).Row
    With Sheets("Sheet2").Columns("a")
    Set c = .Find(Cells(i, "a").Value, LookIn:=xlValues)
    If Not c Is Nothing Then
    ff = c.Address
    Do
    If c.Offset(, 1) = Cells(i, "b") _
    And c.Offset(, 4) <> Cells(i, "e") Then
      Sheets("Sheet1").Cells(i, "e").Interior.ColorIndex = 6
      c.Offset(, 4).Interior.ColorIndex = 6
      End If
    Set c = .FindNext(c)
      Loop Until ff = c.Address
Else
Sheets("Sheet1").Cells(i, "a").Resize(, 2).Interior.ColorIndex = 4
    End If
    End With
Cells(i, "aa") = Cells(i, "a") & Cells(i, "b")
For ii = 1 To Sheets("Sheet2").Range("a" & Rows.Count).End(xlUp).Row
Sheets("Sheet2").Cells(ii, "aa") = Sheets("Sheet2").Cells(ii, "a") & Sheets("Sheet2").Cells(ii, "b")
Next
Next
For i = 1 To Sheets("Sheet1").Range("a" & Rows.Count).End(xlUp).Row
With Sheets("Sheet2").Columns("aa")
    Set fc = .Find(Cells(i, "aa").Value, , , xlWhole)
    If fc Is Nothing Then
Sheets("Sheet1").Cells(i, "a").Resize(, 2).Interior.ColorIndex = 4
    End If
End With
Next
Sheets("Sheet1").Columns("aa").ClearContents
Sheets("Sheet2").Columns("aa").ClearContents
End Sub

 

[alvbnp]

Thanks. It's working great.

 

[alvbnp]

agihcam, need some modification on the code.

The last part of the code highlighting missing items in sheet1, how to change it to highlight missing items in sheet2 as well?

Thanks.

 

[agihcam]

add another loop;

Sub test()
Dim i, ii As Long
For i = 1 To Sheets("Sheet1").Range("a" & Rows.Count).End(xlUp).Row
    With Sheets("Sheet2").Columns("a")
    Set c = .Find(Cells(i, "a").Value, LookIn:=xlValues)
    If Not c Is Nothing Then
    ff = c.Address
    Do
    If c.Offset(, 1) = Cells(i, "b") _
    And c.Offset(, 4) <> Cells(i, "e") Then
      Sheets("Sheet1").Cells(i, "e").Interior.ColorIndex = 6
      c.Offset(, 4).Interior.ColorIndex = 6
      End If
    Set c = .FindNext(c)
      Loop Until ff = c.Address
Else
Sheets("Sheet1").Cells(i, "a").Resize(, 2).Interior.ColorIndex = 4
    End If
    End With
Cells(i, "aa") = Cells(i, "a") & Cells(i, "b")
For ii = 1 To Sheets("Sheet2").Range("a" & Rows.Count).End(xlUp).Row
Sheets("Sheet2").Cells(ii, "aa") = Sheets("Sheet2").Cells(ii, "a") & Sheets("Sheet2").Cells(ii, "b")
Next
Next
For i = 1 To Sheets("Sheet1").Range("a" & Rows.Count).End(xlUp).Row
With Sheets("Sheet2").Columns("aa")
    Set fc = .Find(Cells(i, "aa").Value, , , xlWhole)
    If fc Is Nothing Then
Sheets("Sheet1").Cells(i, "a").Resize(, 2).Interior.ColorIndex = 4
    End If
End With
Next
'highlight sheet2
For i = 1 To Sheets("Sheet2").Range("a" & Rows.Count).End(xlUp).Row
With Sheets("Sheet1").Columns("aa")
    Set fc = .Find(Sheets("Sheet2").Cells(i, "aa").Value, , , xlWhole)
    If fc Is Nothing Then
Sheets("Sheet2").Cells(i, "a").Resize(, 2).Interior.ColorIndex = 4
    End If
End With
Next
Sheets("Sheet1").Columns("aa").ClearContents
Sheets("Sheet2").Columns("aa").ClearContents
End Sub