Comparison

[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

 

[Markus L]

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.

http://www.cpearson.com/excel/duplicat.htm#InOneNotOther

 

[Max]

list all the test numbers that

are not in both columns

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)