Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

5 Answers 5

up vote 16 down vote accepted
  1. Select the list in column A
  2. Right-Click and select Name a Range...
  3. Enter "ColumnToSearch"
  4. Click cell C1
  5. Enter this formula: =MATCH(B1,ColumnToSearch,0)
  6. 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))
share|improve this answer
5  
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

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

share|improve this answer

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

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.