Find Items in one column that are not in another column

Question

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?

score 138 · Accepted Answer · edited Feb 19 '15 at 20:20

138

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)

edited Feb 19 '15 at 20:20

Chris Marisic

1,153
2
19
30

answered Dec 10 '09 at 19:01

devuxer

3,951
6
31
33

7

Then... Put this formula into Conditional Formatting in both lists and and use it to highlight (or whatever) the non-matching cells – Kije Dec 10 '09 at 19:55
7

"Name a Range" doesn't appear for me? Tested on Excel 2010 and 2016. – KERR Jul 14 '17 at 01:13
If, like @KERR and I, you don't have "Name a Range...", just use `A:A` or your range in lieu of `ColumnToSearch` in the formulae. – Michael Feb 13 '18 at 18:21
If, 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 May 07 '19 at 11:26
6

"Name a Range" appears as `Define Name...` (Excel 2019) – m-smith Aug 12 '19 at 13:55
2

Also "Name a Range" only appears if you select a subset of the column, clicking the column header to select the entire column doesn't let you name a range. – Quantic May 03 '21 at 19:38

score 42 · Answer 2 · edited Apr 16 '15 at 20:56

42

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

edited Apr 16 '15 at 20:56

Excellll

12,627
11
51
78

answered May 09 '11 at 16:18

Ellesa

10,895
2
38
52

1

I used "=ISODD(COUNTIF(A:A,B1)=0)" to print true for '1' or false for '0' – Ramraj Sep 20 '16 at 10:13
For unknown reasons the countif() method doesn't work reliably when specifying a multi-column array (e.g. =countif(B:C,A1)=0) - Excel 2016. – HamishKL Mar 01 '21 at 04:08

score 28 · Answer 3 · answered Apr 16 '15 at 20:02

28

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.

answered Apr 16 '15 at 20:02

DOB

281
3
2

if you have a value in column B repeated, this will not highlight them – magodiez Nov 13 '15 at 16:18
2

Surely this is the simplest and most relevant answer? No formulas required. – KERR Jul 12 '17 at 23:24
Nothing happens when doing this. – Overmind Oct 16 '19 at 08:19
1

Easily the best answer. Simplest. – SexyBeast Feb 11 '20 at 19:11

score 17 · Answer 4 · edited Dec 07 '13 at 01:22

17

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.

edited Dec 07 '13 at 01:22

n.st

1,908
1
17
30

answered Dec 06 '13 at 20:43

brenton

171
1
2

6

`=MATCH(B2,$A:$A,0)` worked for me. – nilgun Sep 06 '16 at 10:37

score 4 · Answer 5 · edited Oct 21 '11 at 14:50

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)),"")

Comparing two columns of names and returning missing names

edited Oct 21 '11 at 14:50

C. Ross

6,174
16
62
82

answered Oct 21 '11 at 14:02

Jeeped

41
1

This solution is good, but depends on the source columns being sorted alphabetically, or else it retrieves duplicates. – Steve can help Jan 04 '17 at 08:36

score 4 · Answer 6 · answered Feb 24 '14 at 11:10

4

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.

answered Feb 24 '14 at 11:10

Gautam Jain

139
6

1

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, "") – Oct 01 '14 at 17:48

score 1 · Answer 7 · answered Sep 11 '14 at 13:25

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.

score -3 · Answer 8 · edited Feb 16 '15 at 10:31

-3

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

edited Feb 16 '15 at 10:31

Jawa

3,619
13
31
36

answered Feb 16 '15 at 09:52

A.Ramin walyar

1

Find Items in one column that are not in another column

8 Answers8

Linked

Related