Forum Discussion
Need a compare from two different sheets
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.
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.
2 Replies
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
