Matching column data

[MNetD]

Hello,
I have two columns where I need to find non-matching data. For example, I
would want to know in this example anytime a value appears in column A and
does not appear in Column B (i.e., APPLE does not appear in column B). Using
the sort function will not work as I have blank spaces in some of the rows
and they are not all in the same order. Thanks!

A B
APPLE GRAPEFRUIT
ORANGE PEAR
PEAR ORANGE
GRAPEFRUIT

 

[Sheeloo]

Enter this in C1 (or wherever you want) and copy down
=SUMPRODUCT(--($B$1:$B$20=A1),--($B$1:$B$20<>""))

Change B20 to the end of your list .
Anywhere you get 1 or more means that the item in A1 appears in Col B

 

[ShaneDevenshire]

Hi,

here are two ways assuming your data start in cell A2, title in A1, and the
data in column B runs from B2:B50:
1. Enter the following formula in C2 and copy it down as many rows as the
column A data extends:

=COUNTIF(B1:$B$50,A1)

This function returns a number greater than 0 if there is a matching item in
column B for the entry in A1. Otherwise it returns 0

You can enhance this with

=IF(=COUNTIF(B1:$B$50,A1)>0,"Duplicate","no match")

You might conditionally format column A as follows:
Select the range A1:A100 or where your data is and choose
Format, Conditional Formatting, and pick Formula is from the first drop
down, in the second box enter the formula:
=COUNTIF($B$1:$B$50,A1)>0
click the Format button and on the Patterns tab pick a color.

The last formula could also be written
=COUNTIF($B$1:$B$100,A1)

 

[MNetD]

Thank you, that works for me.

Enter this in C1 (or wherever you want) and copy down
=SUMPRODUCT(--($B$1:$B$20=A1),--($B$1:$B$20<>""))

Change B20 to the end of your list .
Anywhere you get 1 or more means that the item in A1 appears in Col B