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.
What's the quickest way to do this?
Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.
Sign up to join this communityI have two columns in Excel, and I want to find (preferably highlight) the items that are in column B but not in column A.
What's the quickest way to do this?
=MATCH(B1,ColumnToSearch,0)
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)
A:A
or your range in lieu of ColumnToSearch
in the formulae.
– Michael
Feb 13 '18 at 18:21
Here's a quick-and-dirty method.
Highlight Column B and open Conditional Formatting.
Pick Use a formula to determine which cells to highlight.
Enter the following formula then set your preferred format.
=countif(A:A,B1)=0
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.
Took me forever to figure this out but it's very simple. Assuming data begins in A2 and B2 (for headers) enter this formula in C2:
=MATCH(B2,$A$2:$A$287,0)
Then click and drag down.
A cell with #N/A
means that the value directly next to it in column B does not show up anywhere in the entire column A.
Please note that you need to change $A$287 to match your entire search array in Column A. For instance if your data in column A goes down for 1000 entries it should be $A$1000.
See my array formula answer to listing A not found in B here:
=IFERROR(INDEX($A$2:$A$1999,MATCH(0,IFERROR(MATCH($A$2:$A$1999,$B$2:$B$399,0),COUNTIF($C$1:$C1,$A$2:$A$1999)),0)),"")
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.
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.
in C1
write =if(A1=B1 , 0, 1)
. Then in Conditional formatting
, select Data bars
or Color scales
. It's the easiest way.