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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
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

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

Well-known Member
Joined
Jan 16, 2006
Messages
1,624
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
 

alvbnp

Board Regular
Joined
Jun 26, 2006
Messages
180

ADVERTISEMENT

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

Well-known Member
Joined
Jan 16, 2006
Messages
1,624
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
 

alvbnp

Board Regular
Joined
Jun 26, 2006
Messages
180

ADVERTISEMENT

Thanks. It work perfectly.
 

alvbnp

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

Well-known Member
Joined
Jan 16, 2006
Messages
1,624
changed from;
Code:
Set C = .Find(Cells(i, "a").Value, , , xlWhole)
to
Code:
Set C = .Find(Cells(i, "a").Value, lookin:=xlvalues)
 

alvbnp

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

Forum statistics

Threads
1,141,720
Messages
5,708,086
Members
421,545
Latest member
TWR

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
Top