Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Matching a column of new data to existing larger data set. | Excel Worksheet Functions | |||
Matching Data in a Row and Display in Column | Excel Discussion (Misc queries) | |||
Matching one column against another column of data to show the same amount of data. | Excel Worksheet Functions | |||
matching data from column b | Excel Worksheet Functions | |||
Matching data in one column to another | Excel Worksheet Functions |