Comparing two lists of data

[Brandon]

Ok, so I have 2 lists of data, one is a master list that a client has sent me
telling me how they want their expense reports filed (unfortunatly sent to me
6 months late..) and a list we have been maintaining over the course of the
archiving for them which we created and enter manually everyday. And I need
to find a formula that will highlight the information that is similar in both
lists green and the ones that dont match up to highlight red. Here is an
example of what the sheet looks like:


|-----Their List-----| |--------Our List--------|
A B C D
E F
1 Expense# Box # Expense# Box #
Box Compare
2 Bob00942 Box001 Ryan00759 Box001
Box001
3 Ryan00759 Box001 Louis00953 Box001
Box002
4 Clayton00643 Box001 Clayton00643 Box001
Box 001
5 Sherri00571 Box002 Bob00942 Box002
Box001
6 Louis00953 Box002 Roger00911 Box002
N/A
7 Brandon00652 Box002 Steven00864 Box002 N/A
8 Anthony00945 Box002 Anthony00945 Box002
Box002

On F, I used =VLOOKUP(D2,A:B,2,FALSE) in order to get the box number that
the expense report numbers are supposed to be in according to their list, but
my boss would like me to get it to where A and D would light up so we could
see which ones are not matching up ( ex: Sherri00571 would turn red in A
since it is not in D)

Does anyone know a formula that can do this? I apologise if I wrote it out
confusing, but any help would be much appriciated.

Thanks,
Brandon

 

[Luke M]

Their list first:
Select the first cell (A1). Go to Format, conditional format. Change first
box to "Formula is". In second box, type
=ISERROR(MATCH(A1,D,0))
Click format, go to pattern, choose what color you want if there is not a
match (red?)
Copy the cell, select the rest of column A, then right click, paste special
-> format only.
Your list:
similar, except formula is
=ISERROR(MATCH(D1,A:A,0))
And it sounds like you want a different color, maybe green?

 

[Brandon]

Awesome, Thanks! That did the trick!

Their list first:
Select the first cell (A1). Go to Format, conditional format. Change first
box to "Formula is". In second box, type
=ISERROR(MATCH(A1,D,0))
Click format, go to pattern, choose what color you want if there is not a
match (red?)
Copy the cell, select the rest of column A, then right click, paste special
-> format only.
Your list:
similar, except formula is
=ISERROR(MATCH(D1,A:A,0))
And it sounds like you want a different color, maybe green?
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*