Matching column data
 

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

Matching column data
 

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

"MNetD" wrote:

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

Matching column data
 

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)

--
Thanks,
Shane Devenshire


"MNetD" wrote:

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

Matching column data
 

Thank you, that works for me.

"Sheeloo" wrote:

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

"MNetD" wrote:

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