100
33
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?
100
33
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?
95
=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)
32
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
I used "=ISODD(COUNTIF(A:A,B1)=0)" to print true for '1' or false for '0' – Ramraj – 2016-09-20T10:13:15.387
17
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.
if you have a value in column B repeated, this will not highlight them – magodiez – 2015-11-13T16:18:39.123
1Surely this is the simplest and most relevant answer? No formulas required. – KERR – 2017-07-12T23:24:28.940
Nothing happens when doing this. – Overmind – 2019-10-16T08:19:17.097
Easily the best answer. Simplest. – SexyBeast – 2020-02-11T19:11:28.837
15
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.
5=MATCH(B2,$A:$A,0)
worked for me. – nilgun – 2016-09-06T10:37:51.147
4
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)),"")
This solution is good, but depends on the source columns being sorted alphabetically, or else it retrieves duplicates. – Steve Taylor – 2017-01-04T08:36:45.390
3
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.
There is an error in the formula above =IF(ISNA(MATCH(B2,$A$2:$A$2642,0)), A2, "") should be =IF(ISNA(MATCH(B2,$A$2:$A$2642,0)), B2, "") – None – 2014-10-01T17:48:22.090
1
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.
-3
in C1
write =if(A1=B1 , 0, 1)
. Then in Conditional formatting
, select Data bars
or Color scales
. It's the easiest way.
4"Name a Range" doesn't appear for me? Tested on Excel 2010 and 2016. – KERR – 2017-07-14T01:13:16.837
If, like @KERR and I, you don't have "Name a Range...", just use
A:A
or your range in lieu ofColumnToSearch
in the formulae. – Michael - Where's Clay Shirky – 2018-02-13T18:21:26.957If, like me, you don't realize at first where to enter "ColumnToSearch" (step 3), enter it as the name of the named range you are creating. – DaveL17 – 2019-05-07T11:26:52.713
2"Name a Range" appears as
Define Name...
(Excel 2019) – oliver-clare – 2019-08-12T13:55:31.3206Then... Put this formula into Conditional Formatting in both lists and and use it to highlight (or whatever) the non-matching cells – Kije – 2009-12-10T19:55:28.943