Solved

Conditional formatting to highlight differences between two worksheets

Posted on 2013-06-16
8
666 Views
Last Modified: 2013-06-16
Hey guys,

I have been driving myself crazy trying to find the answer to this problem.

I have two contact lists, each in their own worksheet in an Excel 2010 workbook.

I need to find if any contact names in contact list B match those in contact list A. And if the name matches I need to check if the rest of their details are the same. If they dont match, I would like to use conditional formatting to highlight the cells that are different in contact list A.

I then also need to do the opposite, compare list A to B and highlight the unique contact names.

I spent a large part of yesterday trying to figure this out with no success. What I think I just figured out this morning was that using the VLOOKUP formula to return the matching text from the other worksheet, the conditional format doesn't seem to be able to recognise when the text matches and hence format the cell appropriately.

I would really appreciate any assistance that anyone may have. My brain turned to mush yesterday. Thanks.
0
Comment
Question by:defecta
  • 5
  • 3
8 Comments
 
LVL 81

Assisted Solution

by:byundt
byundt earned 500 total points
ID: 39251928
Here are two Conditional Formatting formulas to consider. The first one highlights non-matching detail info in column B, while the second one highlights unique names.
=INDEX(Sheet1!B:B,MATCH($A1,Sheet1!$A:$A,0))<>B1
=COUNTIF(Sheet2!$A:$A,$A1)=0
0
 

Author Comment

by:defecta
ID: 39251962
Thanks byundt.

I think I am applying the first formula incorrectly. Here is what I am trying to do.

I am applying that first conditional format formula to cell A1 on Sheet1.
I want it to find if A1 on Sheet 1 is unique in column C on Sheet2.
So the formula should look this?
=INDEX(Sheet2!C:C,MATCH($A1,Sheet1$A:$A,0))<>C1

(I dont understand how that B1 operates)

Thanks.
0
 

Author Comment

by:defecta
ID: 39251974
I have the second formula working perfectly, thank you byundt.

I am still stuck with the first though.
0
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 39251980
I've posted a sample file showing my suggested Conditional Formatting formulas in action.

My first Conditional Formatting formula was intended to be used on Sheet2. If you are using it on Sheet1, then it should be:
=INDEX(Sheet2!C:C,MATCH($A1,Sheet2$A:$A,0))<>C1

Brad
ConditionalFormattingQ28158812.xlsx
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Closing Comment

by:defecta
ID: 39252034
That is excellent, Thank you so much byndt!

I think I can take it from here.
0
 

Author Comment

by:defecta
ID: 39252039
One last query.

Can you explain what function the C1 has at the end of that formula?
0
 
LVL 81

Expert Comment

by:byundt
ID: 39252051
=INDEX(Sheet2!C:C,MATCH($A1,Sheet2$A:$A,0))<>C1

In the above formula, MATCH is looking at Sheet2 column A and returning the index number (equivalent to a row number in this case) for where the value in Sheet1 cell A1 might be found.

The INDEX function then looks at Sheet2 column C on the same row, and returns a value.

The <>C1 part of the formula then compares the value from the INDEX to the contents of cell C1. If they are different (the <> part of the expression), then the formula returns TRUE. If they are the same, then the formula returns FALSE.

Conditional Formatting is expecting either a TRUE or FALSE value. If it gets anything other than TRUE, the Conditional Formatting doesn't occur. For example, if the MATCH fails (because the value in Sheet1 cell A1 isn't found in Sheet2 column A), the formula returns a #N/A error value--and Conditional Formatting doesn't occur. Only if there is a match in column A and the value in column C differs does the formula return TRUE--and the Conditional Formatting then highlights that cell.

I was careful to use $ in the formula so you could copy it down and across and the Conditional Formatting would be applied as required. The $ means to keep the reference to column A fixed, both for Sheet1 cell A1 and Sheet2 column A.

The references to row 1 and column C are relative. Assuming that they are correct for the top left cell in the range being Conditional Formatted, then those references will automatically adjust row number and column letter as required for all other cells that receive that formatting.

Brad
0
 

Author Comment

by:defecta
ID: 39252063
Excellent explanation and my discovery this morning was leading me to that conclusion. That conditional formatting needs a true or false value. (I should have RTFM! LOL!)

Thanks again for a prompt and excellent explanation of how this all works.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql server query from excel 3 57
How to move the last 4 digits of a number to a new column 8 43
Excel - find text within text? 1 25
Vlookup formula error 15 12
My experience with Windows 10 over a one year period and suggestions for smooth operation
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now