comparing two columns of data to find common values

Hi all

Pretty new to excel, and attempting to compare two columns of data to find the common values.

e.g I have the following 2 columns of data

column1 column2
a b
b c
c e
d g
e x
f y
g z

what I am trying to do is from these 2 columns, determine whthe list of

1. Common values
2. Values that appear in column 1 but not in column 2
3. Values that appear in column 2 but not in column 1

The data is provided from a database. I tired using the 'IF' function but I could not work out how to say "if the value of a column 1 cell is in the whole range column 2".

Using The help section in excel, i could only work out how to do this as the examples only compare one value against another, not against a range.

any ideas?
thanks

It depends how you want to display things countif(a:A,b1) would tell you how many times the value in cell b1 appears in column a, say in cell c1

It could be changed into an if statement

=if(countif(a:a,b1)>0,"Duplicate","Unique")

in d1

=if(countif(b:b,a1)>0,"Duplicate","Unique")

both these formulas could be copied down to all the rows and maybe then filter by these values to select what you require

Regards

Dav

Hi patman,

try these for 10,000 rows of data in columns A and B starting in row
2...

1. Common values...

=IF(COUNTIF($A$2:$A$10001,B2)>0,B2,"")


2. Values that appear in column 1 but not in column 2...

=IF(B2="","",IF(COUNTIF($B$2:$B$10001,A2)=0,A2,""))


3. Values that appear in column 2 but not in column 1...

=IF(A2="","",IF(COUNTIF($A$2:$A$10001,B2)=0,B2,""))

Fill down to suit and adjust 10001 if data is deeper.

Ken Johnson