Highlighting differences

[jennid18]

OK, so I will try to explain this the best way possible. I have seen MANY similar answers that I want but there is one detail that is missing in the other answers. I have two excel sheets, both contain the same columns of information. I need to compare the details in each of the sheets and highlight the differences. Yes, I have found answers for this but I need it to work for rows that may not be in the same rows as the other sheet. So I will try to set an example below.

Sheet 1
Name Grade Score Level
Joe 4 A 2
Jim 3 B 1
Jess 2 D 3

Sheet 2
Name Grade Score Level
Joe 4 A 3
Jess 3 B 1
George 2 C 4

OK, so this is MUCH more smaller than my spreadsheet but I think it gets the point across. So I want something, to compare this data, so it would highlight Level for Joe because they are different and it would highlight Jim because he is not on the second spreadsheet.
I don't care if I need to create a third spreadsheet that maybe combines the data next to each other but I need to make sure that Joe is side by side with Joe and so on and so on. CAN ANYONE HELP ME???? I have spent all day doing this!

Thank you!!!!!

[djapigo]

Hi jennid18,

Try this formula in conditional formatting...
=VLOOKUP($A2,$A$2:$D$4,COLUMN(A2),0)<>IFERROR(VLOOKUP($A2,Sheet2!$A$2:$D$4,COLUMN(A2),0),"")

Let me know if this is what you are looking for...

Dennis

re-post: I'm attaching a sample spreadsheet to show you how I solved this...

[jennid18]

Not really what I am looking for but I appreciate all the help! I am more looking for a way for it to highlight the cell that is different. So the level for Joe would be highlighted a color because it is different than the orginal cell. I know there is a way to match and highlight but I need a way to do this when there may be more rows in one spreadsheet than the other. Almost like a fomular that matches one row from one sheet to another row of the other sheet using the identifier (name) and then comparing the rest of the values to see the differences, such as highlighting the values that are different. Tomorrow when I am back at work I am going to upload an excel sheet to show what I am doing and see if someone can help. So frustrating.

[djapigo]

Hi jennid18,

You have to use the formula I provided into the Conditional Formatting... you can erase the formulas in cells E2:H4, I just wanted to show you how to create such formulas... bottom line is that when working with Conditional Formatting, you need a dynamic formula that will give TRUE or FALSE... once I found that formula, then I implement it in the Conditional Formatting... you should create a formula that covers all the cells, not one row or column at a time...

Go to any any cells in A2:D4, click on Conditional Formatting and see how I used the formulas in the rules... this is where I put the experimental formulas that I tried in E2:H4...

Does that make sense? Still confused?

Let me know,
Dennis

[Steve N.]

jennid18

Dennis' solution does what you ask (Very nicely too. Already added that one to my tool box - Thanks, Dennis!).

You will have to make a slight adjustment to your layout, see if the attached file helps...

I tried to keep the data on sheet2 but kept getting an error message about not being able to use conditional formatting across different worksheets or workbooks.

So, I moved your Sheet2 data to Sheet1, J1:M4, referenced Dennis' formula to those cells, then copied and pasted the formula from E2 into the conditional formatting function. Copying and pasting formulas in the conditional formatting function is not as straight forward as it sounds. Not hard - just need to follow a few steps in a particular order. If you need help with that, holler back...

[djapigo]

Thanks, Steve...

I didn't know about the issue with different worksheets... I'm using Excel 2010 and it seems fine on my end... hopefully, that's what jennid18 was experiencing...

Couple of things about your solution...
1. Why do you have 2 different highlights? I know they seem to be doing the opposite, but it seems overkill to highlight both... (sorry, just my opinion)
2. The formula in the second condtional formatting has a #REF as the range in the second VLOOKUP... you might want to fix that... or is that by design? Then again, what do I know, it seems to be working...

Thanks again for the comments and possibly solving jennid18's problem...

Dennis

[Steve N.]

Dennis,

Sorry - I don't see the second highlight or the #REF. Can you tell me where to look? I did change font color to blue on the sheet2 data the I relocated to sheet1, bit only to draw attention to it. You wrote a great solution!

I've had trouble with CF across worksheets before - looking forward to getting the 2010 update at work...

[djapigo]

Is the 2007 version that different? This is how your conditional formatting looks like to me... weird...

[jennid18]

Wow, You guys are awesome. Now I kind of feel stupid because I am trying to figure out the conditioning formatting. Let's see if I can figure it out with your steps and information in the spreadsheet. I am going to attach my actual data and if you guys could check it out, that would be awesome! I REALLY appreciate the help!

[jennid18]

OMG, I did it. I can't believe I did it. So I figured it out. Now can I have a part two to the question? Is there a way to highlight BOTH lists? Such as if there is an extra row in the second list, it will highlight it there??

[jennid18]

OK, so I figured out how to do the second list and highlight the differences, I just changed a few of the letters in the formula. Is there a simple way to do this or do I need to do conditional formatting on each? another question is, what if I want this to be a permanent thing where I can just input the data into the spreadsheet all the time. Like I will be running 10 different sets of lists each week?

[djapigo]

Hi jennid18,

Nice to hear that you are trying it on your own and, more importantly, figuring it out.

#1 You have to do have 2 different conditional formatting... sorry, but you can copy the formula and tweak the ranges (or you can follow what I explain in #2).

#2 If the layout is all the same with you have new lists, you might simply need to copy the format. Copy one cell and look for the Format Painter to paste the same formatting which will include the Conditional Formatting. just make sure there are no "links" to the original spreadsheet. If there is, simply go into the formula and re-do the range.

For the ranges, I would name the range (Name Manager or Define Name) to simplify the formula more.

Let us know if this is more confusing.

Dennis

[jennid18]

Yeah i got a little lost on this. I understand the copying and pasting the formula, which I do, just not sure what you mean by the painting of the format. I didn't see anything that said that.

[djapigo]

Sorry, try these sites to learn about Format Painter... after this, it will be your friend...
http://www.dummies.com/how-to/conten...t-painter.html

Here's a YouTube video...
http://www.youtube.com/watch?v=9QaLyGpC54E

Hope this helps...

[jkmasurkar]

is the formula same in excel 2003?

[djapigo]

Hi jkmasurkar,

I'm not sure if IFERROR is available in 2003... I haven't tested it, but try this formula instead...

=VLOOKUP($A2,$A$2:$D$4,COLUMN(A2),0)<>IF(ISNA(VLOOKUP($A2,Sheet2!$A$2:$D$4,COLUMN(A2),0)),"",VLOOKUP($A2,Sheet2!$A$2:$D$4,COLUMN(A2),0))

Also as Steve mentioned, the 2 sets of data might have to be within the same spreadsheet... I can't test it though...

Let me know what you come up with...

re-post: you might want to follow this older thread as a different solution...
http://www.excelforum.com/excel-gene...or-2003-a.html