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.

 

[acw]

Hi

Assuming that the data starts in A1 of both sheets, try the following:

Sheet1!E1: =INDEX(Sheet2!$E$1:$E$3,SUMPRODUCT(--(Sheet2!$A$1:$A$3=Sheet1!A1),--(Sheet2!$B$1:$B$3=Sheet1!B1),ROW(Sheet2!$E$1:$E$3)),0)

Adjust ranges to suit and copy down as required.

sheet1!A1: Format, Conditional Formatting, formula is: =$E1=0, select a pattern, and save. Copy format to cells in columns A and B as required.

HTH

Tony

 

[alvbnp]

Hi

Assuming that the data starts in A1 of both sheets, try the following:

Sheet1!E1: =INDEX(Sheet2!$E$1:$E$3,SUMPRODUCT(--(Sheet2!$A$1:$A$3=Sheet1!A1),--(Sheet2!$B$1:$B$3=Sheet1!B1),ROW(Sheet2!$E$1:$E$3)),0)

Adjust ranges to suit and copy down as required.

sheet1!A1: Format, Conditional Formatting, formula is: =$E1=0, select a pattern, and save. Copy format to cells in columns A and B as required.

HTH

Tony

Sorry, don't really get what you mean. My sheet1!E1 is having data, which I want to match with sheet2!E

Maybe I didn't make it clear enough above. Here again:

Do matching for column E for sheet1 and sheet2 with 2 conditions.
If sheet1:A = sheet2!A and sheet1:B = sheet2!B, then match sheet1:E
Highlight those unmatched items in column E

Data in both sheets are not sorted.

 

[agihcam]

vba;

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, , , xlWhole)
    If Not c Is Nothing Then
    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
    End If
    End With
Next
End Sub

 

[alvbnp]

Thanks agihcam. It's working. But a little problem here.

Sample data for example:

Sheet1
Column A | B | E
2 | A | 100
2 | B | 1
2 | C | 100

Sheet2
Column A | B | E
2 | B | 100
2 | A | 100
2 | C | 100

The error in column E won't be highlighted on the above sample data, unless the column B is Ascending sorted in both sheets.

Any fixes to overcome this?

 

[agihcam]

Hi -

this should work.

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, , , xlWhole)
    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

    End If
    End With
Next
End Sub

 

[alvbnp]

Thanks. It work perfectly.

 

[alvbnp]

Need some more help. My cells containing formula, the value of the cells is derived from the formula, and this make the macro stop working.
eg:
=sheet1!A1
=lookup(XXX,YYY)
and so on.

 

[agihcam]

changed from;

Set C = .Find(Cells(i, "a").Value, , , xlWhole)

to

Set C = .Find(Cells(i, "a").Value, lookin:=xlvalues)

 

[alvbnp]

Thanks, agihcam. One more thing, is it possible to highlight those missing items, eg: it appear in sheet1 but not in sheet2, just need to highlight those in sheet1.