Column Value comparison tested against another column

[Nerdhurd]

Greetings, say you have 2000 rows in column A and 13,000 rows in column B. You would like to see which values are NOT in both rows, what would be the best way to do this? i've got an idea but i cant get it to work, trying to compare a value in column B vs all the values in column A to find a match like this, then sort by TRUE FALSE:

=IF(A1:A13000=B1,TRUE,FALSE)
=IF(A1:A13000=B2,TRUE,FALSE)
=IF(A1:A13000=B3,TRUE,FALSE)

However its not working, its always false...

any other ideas on how to do this?

 

[mbarron]

Try this.
=if(ISNA(VLOOKUP(B1,$A$1:$A1$300,1,0)),FALSE,TRUE)

It will show you whether the value in B1 is in column A

Mike

 

[AnotherHiggins]

First, please see my response to you previous post (thread68-1037679) regarding the use of TRUE and FALSE in formulas.

If you just want an indicator in the row, then put this in column C
[COLOR=blue white]=COUNTIF($A$1:$A$2000,B1)[/color]
any row where column C is a zero is an 'unmatched' record.

But there are far better, and more elegant, ways of doing this.

Go to Data > Filter > Advanced Filter
[li]In the List Range enter $B$1:$B$13000[/li]
[li]In the Criteria Range enter $A$1:$A$2000[/li]
You can either Filter in place, or copy to another location

[tt]_____
[blue]-John[/blue]
[/tt][red]"If you're flammable and have legs, you are never blocking a fire exit."[/red]
-Mitch Hedberg

Help us help you. Please read FAQ181-2886 before posting.