×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

Compare 2 columns in seperate sheets but same worknook Excel 2016

Compare 2 columns in seperate sheets but same worknook Excel 2016

(OP)
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.

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

Hi,

CODE

I have tried Conditional Formatting but can seem to get that working. Any ideas at all please. 

CF works! Chances are, if you did not use a lookup function, your CF failed. What formula did you use?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

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

Here's an example of the formula you might use for a CF...



Notice that Code B is the only one that matches (TRUE) sheet2.

Actually thus is a better formula...

D2: =INDEX(Sheet2!C:C,MATCH(A2,Sheet2!A:A,0),1)=C2



Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

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

(OP)
Sorry lost me on this one and cannot apply it to my requirements.

I have a sheet called Product Margin where all the codes are listed in column A
The other sheet is called Main Data which again as all product codes in column A

I want to highlight any codes that do not exists between the 2 in red in the sheet Main Data.

Thanks

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

Some sample data representing your issue in the attached Excel file would be nice to have here.
Along with - how the solution would look like.


---- Andy

There is a great need for a sarcasm font.

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

Er ...

Quote:

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

and

Quote:

I want to highlight any codes that do not exists between the 2 in red in the sheet Main Data.

are inconsistent with each other. Can you please clarify:

1) what colours you want for what
2) on which of the sheets the colours should be



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

(OP)
Hi

I have created a small spreadsheet just to try and get the formula correct

Sheet one in column A as 3 entrys

A1 = Dave
A2 = Mick
A3 = Ian

Sheet 2 in column A as

A1 = Dave
A2 = Mick
A3 = Ian
A4 = Chris

I want to compare both columns and in this case highlight sheet 2 A4 as this name is not in sheet 1

I have managed to get this formula to work in sheet 2 (using your first post)

=INDEX(Sheet2!A1:A4,MATCH(A1,Sheet1!A1:A4,0),1)=A1

However, Sheet 2 A1 to A3 return True, then A4 returns #N/A. Not sure why it comes back with N/A I was expecting false.

The I was going to apply the formula to a CF and try and show sheet 2 A4 in red.. Then try it on my main sheet I am working on. So any ideas why I get #N/A, Thanks




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

(OP)
1) what colours you want for what
2) on which of the sheets the colours should be

I would prefer red and sheet 2. Thanks

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

=INDEX(Sheet2!$A$1:$A$4,MATCH($A1,Sheet1!$A$1:$A$4,0),1)=A1

(and Skip'll be along in a moment to tell us that using structured tables makes this easier … 0-; )

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

(OP)
Strongm thanks for the formula

Using it as below I am still getting TRUE for the 4 that match and #N/A for the one that does not match. Should it be like this or should it be false?

=INDEX(Sheet2!$A$1:$A$4,MATCH($A1,Sheet1!$A$1:$A$4,0),1)=A1

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

This is where the clarity of your requirements becomes key. Skip's formula is designed to flag if there is a price discrepancy between sheet one and sheet two for those items that match codes between sheet 1 and sheet 2. But you are trying to use it to simply match the product codes, which means you don't need INDEX - but you do need to beable to handle the situation when there is no matching code. So something like

=NOT(IFNA(MATCH($A1,Sheet1!$A$1:$A$4,0),FALSE))

Our confusion is understandable because you still have not properly clarified your requirements!

You would prefer RED for WHAT? For mismatched product codes, or for mismatched prices? Or can we forget the prices thing completely?



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

(OP)
Also stronm By the way I am putting the formula in the B column of sheet 2

I put the formula into a conditional format and set it to use red. It highlighted the matching codes but left the one not matching with no fill. I want it to only fill the in-matching ones.
How do I reverse this. Thanks

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

(OP)
Hi

I have attached spreadsheet I want to work called Costs Margin v2.
I have also attached the testing one with names in for you to see

In the Costs Margin v2 I want to highlight in the Refresh Data tab all codes in column A that do not match in Trex Margin column A codes.

Thanks for assistance.

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

Your attached example is still using Skip's formula, which, as stated in my post of 14:59 is does not meet your current requirements (it met some earlier ones, but you seem to have moved off those now). .. Here's your sheet back with my formula from that same post (both in column b and in conditional formatting)

(I should also n ote that there is some synergy between this thread and thread707-1798587: Excel VBA - Capture the value in one column and search for it in another column)

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

(OP)
Hi

yes sorry about all the messages and nothing very clear, I was trying very hard here to get it working and knew I was close but yet so far away.

Thanks for your code example, I see what it is doing.

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

(OP)
I presume that this cannot be used on the cost margin sheet though.

I have tried it and it does not work.

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

> the cost margin sheet though.

Is this the same as Product MArgin sheet? You've not previously mentioned a cost margin sheet.

In terms of Sheet 1 and Sheet 2, which sheet is this? And do you suddenly need the same behaviour on the 'cost margin' sheet? i.e. highlight names that don't match the other sheet? In which case, sure, it'll work, with a very minor alteration

=NOT(IFNA(MATCH($A1,Sheet1!$A$1:$A$4,0),FALSE))

just change the highlighted sheet name to the name you want

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

(OP)
Sorry in the cost margin sheet

Sheet1 is renamed to Trex Marginb
Sheet2 is renamed to Refresh Data

I change you code as below and have netered it in D2 in the Refresh Data sheet. For some reason when I press enter it opens up a window to select a file from somewhere, if I click the cross to close it it does it again, click the cross and then it populates the D column with #NAME?

=NOT(IFNA(MATCH($A2,Trex Margin!$A$1:$A$1000,0),FALSE))

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

=NOT(IFNA(MATCH($A2,Trex Margin!$A$1:$A$1000,0),FALSE))

Regarding your formula,
EDIT the formula.
SELECT Trex Margin!$A$1:$A$1000 in the formula.
The SELECT that range on that sheet.

You will notice the QUOTES around the sheet name because you have a SPACE in your sheet name.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

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

Caused by the fact that you have a space in the name - so you need to protect it, as it is not unsurprisingly confusing Excel's expression evaluator

=NOT(IFNA(MATCH($A1,'Trex Margin'!$A$1:$A$5,0),FALSE))

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

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

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 ...

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

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

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)

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

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

> 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.

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

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

(OP)
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

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

(OP)
Hi

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

Thanks

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

> 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.

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

(OP)
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.

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

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

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

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

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...

=NOT(IFNA(MATCH($A1,'Trex Margin'!$A$1:$A$9998,0),FALSE))


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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

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

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

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

It is your formula.

But it seems he has a problem implementing it.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close