Matching based on 2 criterias

alvbnp

Board Regular
Joined
Jun 26, 2006
Messages
180
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.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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
 
Upvote 0
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.
 
Upvote 0
vba;
Code:
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
 
Upvote 0
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?
 
Upvote 0
Hi -

this should work.
Code:
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
 
Upvote 0
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.
 
Upvote 0
changed from;
Code:
Set C = .Find(Cells(i, "a").Value, , , xlWhole)
to
Code:
Set C = .Find(Cells(i, "a").Value, lookin:=xlvalues)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,218,627
Messages
6,143,585
Members
450,494
Latest member
GolfNut39

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top