Comparing two excel documents and coloring the differences

[jonathynblythe]

Hello,

My name is Jon and thanks for letting me join here!

What I wanted to know is how I'd do the following:

I have two excel spreadsheets.... they have names, addresses and phone numbers, etc. with their own columns (names are in column A, addresses are in column B, etc.)

This year's data (spreadsheet #2) is different from last year's (spreadsheet #1). I need to know if the phone numbers have different data this year..... and if those phone numbers are even listed in spreadsheet #2. Phone numbers are in column D for both spreadsheets...

* How do I make spreadsheet #1 highlight the entire row blue in color if the phone number was not included on spreadsheet #2?
* If the phone number is on spreadsheet #2 this year like it should be, how do I make the other cells for that phone number's row turn red if there are any changes this year (i.e. 610-000-0009 is on both spreadsheets, however this year the address changed and I would like to see that cell in that phone number's row turn red....... i.e. 609-000-1111 is on both spreadsheets, but the name and zip code changed and I want both those cells to turn red)

If there are no changes for any of the phone numbers that are found on both spreadsheets, then that gets left alone. Does this make sense?


Thank you all in advance!
JB

 

[Akashwani]

Hi Jon,
Welcome to MrExcel.

Let's try and solve one bit at a time.

You need to use Conditional Formatting, BUT, you cannot reference another Worksheet in CF, what you can do however is use something like an IF formula in a Helper column that you can then refer to in CF.

So for example, IF the phone number is not present in column D of Sheet2 you could use a formula like this in say column E cell 2 of Sheet1 and copied down...

=IF(Sheet2!D2="","D","Match")

What this does is adds D to cell E2 if there is no phone number in D2 of Sheet2 and adds Match if there is a phone number present.

You can then highlight your range say A2:D10, in 2007 go to Conditional Formatting - New Rule - Use a formula etc and use this formula in the white box....

=$E2="D"

Choose your format, click OK.

We can then take this further by using this formula instead in cell E2 of Sheet1 and copied down as far as your data goes....

=IF(Sheet2!D2="","D",IF(A2<>Sheet2!A2,"A",IF(B2<>Sheet2!B2,"B",IF(C2<>Sheet2!C2,"C","MATCH"))))

Highlight the data in column A and use this formula in CF...

=$E2="A"

Choose your format, click Ok.

Highlight the data in column B and use this formula in CF...

=$E2="B"

Choose your format, click Ok.

Finally, Highlight the data in column C and use this formula in CF...

=$E2="C"

Choose your format, click Ok.

At this moment I'm unsure as to how you can have more than one cell highlighted red, if for example B2 and C2 on sheet1 are different to B2 and C2 on Sheet2, I think that it would be a long IF/AND formula, if you are using Excel 2007/10 then that wouldn't be a problem because you can have more than 3 Conditional Formats.

Anyway, this hopefully gives you a start and points you in the right direction, if nothing else it has bumped you up.

Good Luck

Ak

Check out Mike's videos on Conditional Formatting at ExcelIsFun

 

[jonathynblythe]

Sorry... was out for a week....

Thanks for the answer, that'll do me just fine!