Compare 2 columns in seperate sheets but same worknook Excel 2016 2

[Cpreston]

Hi

I want to compare 2 columns in a spreadsheet so it highlights any difference by a code

Sheet 1 and Sheet 2 have the same columns

Code Description Cost

By matching the codes in both sheets I want it to highlight in red any cost difference in Sheet 1 compared to sheet 2 where codes are equal.

I then want to able to highlight in an another colour and codes in sheet 1 that do not match in sheet 2

I would like to do this using code. I have tried Conditional Formatting but can seem to get that working. Any ideas at all please.

 

[SkipVought]

Why are you entering a CF formula in column D?

The formula belongs in the CF wizard with the appropriate column(s) selected. It matters not that the formula return N/A! N/A is a FALSE condition. the TRUE condition displays the CF Format.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[strongm]

Skip, it does matter because he actually wants (now his requirements have moved on) the inverse of what your formula is doing , and NOT(#N/A) is still #N/A even in a CF ...

 

[SkipVought]

A CF displays an other format overriding the default format on the sheet range.

Therefore, rather than doing a NOT(), he ought to assign the other format on the sheet range and use the CF to assign the default format.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[strongm]

As a matter of interest, why do you recommend that method, rather than using NOT (the drawback of which is easily overcome using IFNA, which is the way I prefer to transform #N/A into a genuine FALSE)

 

[SkipVought]

Its a matter of taste. Nothing wrong with using IFNA(). In this case, it may be the better solution.

My major thrust was, that having a CF expression return an N/A, is not necessarily a problem. But you would not want a CF expression returning N/A as the TRUE==Format condition because N/A becomes FALSE.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[strongm]

> because N/A becomes FALSE

Ah, this is where my pedantry kicks in - #N/A is not FALSE, nor is it TRUE (we have entered the exciting world of tri-state Booleans, and #N/A behaves the same way as null in VBA)

if null then msgbox "Ok"
if not null then msgbox "ok"
if null=true then msgbox "ok"
if null=false then msgbox "ok"

We'll never see 'ok'

So, as long as we are only looking for TRUE #N/A will appear to work as if it is false, but it isn't. It is an important distinction.

 

[SkipVought]

Very TRUE!

N/A works this way (being not TRUE) in CF, where the CFormat is triggered by TRUE and no thing occurs otherwise. Perhaps this seeming anomaly is like a bad habit you ignore under certain conditions.

It is an important distinction!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Cpreston]

Hi

Thank for the replys and discussion

Sorry Skip I did not understand how to change the code to Select.

The other code =NOT(IFNA(MATCH($A1,'Trex Margin'!$A$1:$A$5,0),FALSE)) looked liked it worked and populated the column with True and False. Skip I am only doing it this way so I can see if it is working before adding it into a CF

I said looked liked it worked as the 3 codes that showed False in the Refresh Tab were actually in the Trex Margin sheet. I checked the codes and they are exactly like for like so why it is giving these 3 False I don't know.

Would it be possible for one of you to try this in the sheet I attached Cost Margins v2. I will continue to work on it today to see if I can figure it out.

Many thanks

 

[Cpreston]

Hi

Tried all I could but no success. Hope you can assist.

Thanks

 

[strongm]

> try this in the sheet I attached Cost Margins v2

No, because the file you attached (actually TestingCF.xlsx) seems to bear little resemblance to what you are now describing. We're happy to help - but you have to help us help you. Provide an example spreadsheet that matches in terms of content and sheet names what you have recently been describing.

 

[Cpreston]

Hi

It did not work it was giving wrong result back, saying False when codes exited and True when some did not.

I have just managed to get this working in the Refresh Data sheet and this applies MISSING to the right codes that are not in the Trex Margin sheet and leaves matching ones blank.
I know I have used a Vlookup but seems to work, not how I wanted it to but gives clear indicators the difference between the 2 sheets.

=IF(ISNA(VLOOKUP(A9,'Trex Margin'!A:F,1,0)),"MISSING","")

Thanks for the help and patience guys it is really appreciated.

 

[SkipVought]

I haven't been at my PC all morning, but it just occurred to me as I ruminated about this post while out and about, that it used to be, in earlier versions of Excel, that referencing ranges in sheets other than the CFed sheet was verboten.

However, it is possible to overcome this shortcoming by using NAMED RANGES, something I reflexively do 1) using Structured Tables and 2) using FORMULAS > DEFINED NAMES > CREATE FROM SELECTION > Create names from values in the TOP ROW regularly, early and often.

Since this is what I've done for nearly two decades (certainly Named Ranges), it slipped my mind and since I also answer nearly all my Tek-Tips from my iPad which does not have access to Excel. I won't be sitting at my PC until much later today, but I will use your previously posted workbook to craft a solution that I can upload for you to observe.

Sorry for this oversight.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[strongm]

Skip, see my post of 5 Dec 19 14:22 …

 

[SkipVought]

Well, it turns out that remote sheet references are not a problem.

But here's your revised workbook with CF on the second sheet, using this CF Formula...
[tt]
=NOT(IFNA(MATCH($A1,'Trex Margin'!$A$1:$A$9998,0),FALSE))
[/tt]

Notice I expanded the range reference on the first sheet, in order that the list can be expanded at will. Could have been 'Trex Margin'!$A:$A

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[strongm]

Skip, this could run and run, given that's pretty much what I gave the OP in my post of 5 Dec 19 15:43

 

[SkipVought]

It is your formula.

But it seems he has a problem implementing it.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein