Home » Questions » Computers [ Ask a new question ]

Find Items in one column that are not in another column

Find Items in one column that are not in another column

I have two columns in Excel, and I want to find (preferably highlight) the items that are in column B but not in column A.

Asked by: Guest | Views: 35
Total answers/comments: 5
Guest [Entry]

"Select the list in column A
Right-Click and select Name a Range...
Enter ""ColumnToSearch""
Click cell C1
Enter this formula: =MATCH(B1,ColumnToSearch,0)
Drag the formula down for all items in B

If the formula fails to find a match, it will be marked #N/A, otherwise it will be a number.

If you'd like it to be TRUE for match and FALSE for no match, use this formula instead:

=ISNUMBER(MATCH(B1,ColumnToSearch,0))

If you'd like to return the unfound value and return empty string for found values

=IF(ISNUMBER(MATCH(B1,ColumnToSearch,0)),"""",B1)"
Guest [Entry]

Select the two columns. Go to Conditional Formatting and select Highlight Cell Rules. Select Duplicate values. When you get to the next step you can change it to unique values. I just did it and it worked for me.
Guest [Entry]

"My requirements was not to highlight but to show all values except that are duplicates amongst 2 columns. I took help of @brenton's solution and further improved to show the values so that I can use the data directly:

=IF(ISNA(MATCH(B2,$A$2:$A$2642,0)), A2, """")

Copy this in the first cell of the 3rd column and apply the formula through out the column so that it will list all items from column B there are not listed in column A."
Guest [Entry]

"Thank you to those who have shared their answers. Because of your solutions, I was able to make my way to my own.

In my version of this question, I had two columns to compare -- a full graduating class (Col A) and a subset of that graduating class (Col B). I wanted to be able to highlight in the full graduating class those students who were members of the subset.

I put the following formula into a third column:

=if(A2=LOOKUP(A2,$B$2:$B$91),1100,0)

This coded most of my students, though it yielded some errors in the first few rows of data."
Guest [Entry]

in C1 write =if(A1=B1 , 0, 1). Then in Conditional formatting, select Data bars or Color scales. It's the easiest way.