Forum Discussion
Excel Compare two columns to find unmatched values
I want to compare two columns (A and B) and want to identify the values in B which are not present in A.
7 Replies
If there are no extras in ListOne or duplicates in ListTwo
= UNIQUE(VSTACK(ListOne, ListTwo), , TRUE)would do.
If there are values in ListOne that do not occur in ListTwo they may be eliminated by using
= UNIQUE(VSTACK(ListOne, ListOne, UNIQUE(ListTwo)), , TRUE)would give the result required.
That could be with formula for conditional formatting rule as
=(C3<>"")*(COUNTIF($B$3:$B$11, C3)=0)You can easily achieve this using Conditional Formatting-
- Select the cells in column B where you want to see the values that don't match.
- Go to the "Home" tab in the Excel ribbon.
- In the "Styles" section, select "Conditional Formatting".
- Select "Highlight Cells Rules".
- Then choose "Duplicate Values."
- In the "Duplicate Values" dialog box, select "Unique" from the dropdown list.
- Choose a format (e.g., fill color) for highlighting the unmatched values, and click "OK."
If you have any doubts, please let me know.
tanayprasad Thanks for sharing the detailed steps. Just one correction needed in step 1
Select the cells in both column A and B where you want to see the values that don't match.
Depends on goals. Initial question was "to identify the values in B which are not present in A." If select both columns, that will be unique for this two-columns range. Not values in second column which are not in first columns. Doesn't matter they are duplicated within second column or not.
- Thank you tanayprasad. Niraj1
But this will only give unique valves in the same column (column B).
I have same query, where i want to compare two columns A & B which might have duplicate values but I only want to filter out (identify) the unique values of column B which are not present in column A.
Thank you
