Identifying Data and highlighting matches in multiple tables

I'm trying to compare two sets of data and identify which have the same information.

If you look at the attached - I want to conditionally format "ID" and "Occupant ID" for the two sets of data.

So somehow the formula would say - IF "ID" is match, then check to see if "Occupant ID" is a match, if both are true, then either conditionally format them....or, In a separate column if they are both true, put a 1.

Assuming you don't want to include the "vacant" entries... then select the first table, from row 2 down and apply new condition using formula:

=AND($D2<>"vacant",COUNTIFS($I$2:$I$13,$C2,$J$2:$J$13,$D2))

and apply format


Then select second table and apply conditional format formula:

=AND($J2<>"vacant",COUNTIFS($C$2:$C$13,$I2,$D$2:$D$13,$J2))

adjust ranges to suit.

Are you sure you did it correctly?

See attached.

Even in my attachment?

Did you compare?

Great!

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save