Print

Excel: Find values in one column that are not in another

What?
A quick note on how to compare two columns for values that are not found in another. I have a column with old values, and now that I have a new list, I want a quick way to see what values are in the old column and which ones are new...

Why?
Consider the 3 following columns in an Excel spreadsheet:
copyraw 
Old       New       Exists in Old?
--------- --------- --------------
123456    234567
234567    345678
345678    456789
567890    597890
  1.  Old       New       Exists in Old? 
  2.  --------- --------- -------------- 
  3.  123456    234567 
  4.  234567    345678 
  5.  345678    456789 
  6.  567890    597890 
I want the third column to say whether this is new or not.

How?
I found this in a StackExchange site:

Method #1
copyraw 
=MATCH(B2, $A$2:$A$100, 0)

-- Check whether value in B2 exists in range of A2:A100.
-- Returns the index of column A in which the B2 value was found.
-- Returns #N/A for a value which is NOT in column A.
  1.  =MATCH(B2, $A$2:$A$100, 0) 
  2.   
  3.  -- Check whether value in B2 exists in range of A2:A100. 
  4.  -- Returns the index of column A in which the B2 value was found. 
  5.  -- Returns #N/A for a value which is NOT in column A. 
Drag the formula in B2 down all the list as per the following screenshot. A value equal to "#N/A" means the value was NOT found in column A.

Values found in one column but not another


Method #2
Be careful with this one because if you copy the formula down, it may automatically modify the range. So the formula in the first row will be =COUNTIF(A2:A100, B2) but the formula in the second row will be =COUNTIF(A3:A101, B3):
copyraw 
=COUNTIF(A2:A100, B2)

-- Returns 1 if value in B2 was found in range A2:A100
-- Returns 0 if value in B2 was not found in range A.
  1.  =COUNTIF(A2:A100, B2) 
  2.   
  3.  -- Returns 1 if value in B2 was found in range A2:A100 
  4.  -- Returns 0 if value in B2 was not found in range A. 

Category: Excel :: Article: 551