Help with a formula

[Guest]

This may seem academic, but I'm having difficulty finding the right formula
to use for a task. In simple terms, I'm trying to cross-reference 2 columns
(say A and B) to find instances where a value exists in column A but not B,
and vice versa. For example, I have 2 columns of Vehicle Id numbers (VIN#), A
and B, that should each have the same count. Assuming that these columns are
duplicates, I'd like to find the exceptions where a VIN# exists in column A
and not, and vice versa.

Thanks for any help!!!!

 

[Frank Kabel]

Hi
in C1 enter
=IF(A1<>B1,"no match","")
and copy this down

 

[Jason Morin]

Select col. A, go to Format > Conditional Formatting, and
select "Formula Is" on the drop-down. Insert:

=AND(COUNTIF($B:$B,$A1)=0,$A1<>"")

and format as desired. OK out. Select col. B and follow
the same procedure. Use this formula:

=AND(COUNTIF($A:$A,$B1)=0,$B1<>"")

Anything that changed format is *not* found in the other
column.

HTH
Jason
Atlanta, GA

 

[Guest]

Thanks Frank. Since my data is "all over the place" for each column, this
formula does not work. It's the right concept, however if for example I take
B1, I would not want to look for a match on A1, I need to take the entire
range of A1 to An to see if it would match.

Dan

 

[Frank Kabel]

Hi
then try in C1:
=IF(COUNTIF($A:$A,B1)>0,"cell in column A","")
and copy this down

 

[Trevor Shuttleworth]

Dan

sounds like you should try using VLOOKUP or MATCH

For example:

=IF(ISERROR(VLOOKUP(B1,A:A,1,FALSE)),"no match","")

and

=IF(ISNA(MATCH(A1,B:B,0)),"no match","")

Regards

Trevor

 

[Guest]

Thanks for the help! This works fine and came through at the right time. I'll
try the other ideas mentioned as I get time...Thanks all!!

Dan