Need a compare from two different sheets

New Contributor

I need a macro/formula/method, to compare 2 column items on one sheet to another on another sheet and maker duplicates. however they must match both columns perfectly. Example sheet 1 row one column A apples column B $10
row 2 column A bananas column B $10 , then compare to another sheet, sheet 2 row one column A bananas column B $10, row two column A Potatoes column B $10. I need it to mark(or delete) row 2 on first sheet and not row 1.



2 Replies
best response confirmed by CareySmith1976 (New Contributor)


Select A1:B100 (or however far down the data on sheet one go). A1 shoud be the active cell in the selecton.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula

=COUNTIFS('Sheet 2'!$A$1:$A$100,$A1,'Sheet 2'!$B$1:$B$100,$B1)

Change Sheet 2 to the actual name of the second sheet, and adjust the ranges if needed.

Click Format...
Activate the Fill tab.
Select a highlight color.
Click OK twice.

Thank you that worked perfectly