comparing two sheets

Question

I need to compare data in two sheets for differences. If any value in Sheet "Drive" column C is not found in sheet "daily" column a, I want it to highlight yellow. And if any value in sheet "daily" column A is not found in sheet Drive Column C I want it to highlight green. I've seen examples of comparing field to field but I need to show any unique value on either sheet. Tried to use conditional formatting "unique' but can't figure out a formula that works for this.

Answer 1

Hi @JH

Thank you for contacting the Microsoft Q&A forum. 

As I understand, you need to compare two sheets and highlight unique values. Here are the steps I have tested, and they worked for me: 

For the Excel app: 

Drive Sheet: 

1. Select the range in column C that you want to check. (e.g., C1:C100) 
2. Go to Home > Conditional Formatting > New Rule. 
3. Choose Use a formula to determine which cells to format. 
4. Enter the formula: =ISNA(MATCH(C1, daily!A:A, 0)). 
5. Click Format, choose the Fill tab, and select Yellow. 
6. Click OK to apply the formatting. 

Daily Sheet: 

1. Select the range in column A that you want to check. (e.g., A1:A100) 
2. Go to Home > Conditional Formatting > New Rule. 
3. Choose Use a formula to determine which cells to format. 
4. Enter the formula: =ISNA(MATCH(A1, Drive!C:C, 0)). 
5. Click Format, choose the Fill tab, and select Green. 
6. Click OK to apply the formatting. 

For Excel Web: 

Drive Sheet: 

1. Select the range in column C that you want to check. (e.g., C1:C100) 
2. Go to Home > Conditional Formatting > New Rule. 
3. Choose Format cells where a formula is true. 
4. Enter the formula: =ISNA(MATCH(C1, daily!A:A, 0)). 
5. Click Format Style, choose the plus sign > Fill color and select Yellow. 
6. Click Done. 

Daily Sheet: 

1. Select the range in column A that you want to check. (e.g., A1:A100) 
2. Go to Home > Conditional Formatting > New Rule. 
3. Choose Format cells where a formula is true. 
4. Enter the formula: =ISNA(MATCH(A1, Drive!C:C, 0)). 
5. Click Format Style, choose the plus sign > Fill color and select Green. 
6. Click Done. 

I hope this helps. 

If you need further assistance, please let us know. 

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".     

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.