Comparison

  • Thread starter Thread starter Matti Koski
  • Start date Start date
M

Matti Koski

What is the easiest way to make following comparison
between two columns. For example I have two columns
containing test numbers. I would like to make a comparison
between the two columns and list all the test numbers that
are not in both columns into the third column.

Matti
 
list all the test numbers that
are not in both columns
Click to expand...

hmm, just wondering ....
would the obvious answer to the above be... none? <g>

ok, assuming the test data are in A1:A5 and B1:B5

A. To extract values in col B which are not in col A
----------------------------------------------------------
Put in C1: =IF(COUNTIF($A$1:$A$5,B1)=0,B1,"")

Copy C1 down to C5 (the last row)
(this extracts values in col B which are not in col A)

Put in D1: =SMALL(C:C,ROW())
Copy down until #NUM! appears

or

Put in D1 : =LARGE(C:C,ROW())
Copy down until #NUM! appears

(the above will "move" the extracted values to the top)


B. To extract values in col A which are not in col B (similar steps as
above)
----------------------------------------------------------------------------
---------------
Put in E1: =IF(COUNTIF($B$1:$B$5,A1)=0,A1,"")

Copy E1 down to E5 (the last row)
(this extracts values in col A which are not in col B)

Put in F1: =SMALL(E:E,ROW())
Copy down until #NUM! appears

or

Put in F1 : =LARGE(E:E,ROW())
Copy down until #NUM! appears

(the above will "move" the extracted values to the top)
 
Back
Top