Conditional Formatting: Comparing Items between two lists

[kbdavis]

Hello all -

I have two relatively long lists of data that are each located on separate tabs 'Display' and 'Data'. One list or the other are constantly being updated and it makes data integrity/accuracy an issue.

What I would love to happen is to create a conditional format where if an Exact text string is located in the 'Display' tab, but not in the 'Data' tab, then that text string will be highlighted in Display and vice versa.

Miscellaneous information:

• Display tab, the column of interest is in D:D
• Data tab, the column of interest is in C:C
• Duplicate values in the same sheet are not a concern whatsoever (Data list can contain the word "apple" 10 times, but as long as the Display tab contains 1 match it will not highlight)

So in summary I just need to make sure that if the word 'apple' is on the first sheet then it needs to also be on the 2nd sheet, so if one or the other are highlighted it basically would just flag me to review whether I need to delete that value or to have it added to the other. Obviously it's not the perfect system - but it will sure make it hell of a lot easier!

If any additional clarification / information is needed, please let me know!

[etaf]

you should be able to use countif()

on the display TAB
setup conditional formatting using a rule
=countif(Data!C:C,Display!D1)=0

and on the data tab
=countif(Display!D:D,Data!C1)=0

so if no match , ie the count = 0
then it should highlight

[kbdavis]

Worked out perfectly! I did have to add an AND statement

Please Login or Register  to view this content.

(the ^^ represents your formula) so it wouldn't highlight the spaces (but I didn't specify so that was on me). I just wanted to comment with that in the event someone with a similar issue stumbles upon your answer.

Thanks for the response - you saved me many headaches down the road!

[etaf]

your welcome -thanks for the rep