comparing two columns of data to find common values

P

patman

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
 
D

Dav

It depends how you want to display things countif(a:A,b1) would tell yo
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 the
filter by these values to select what you require

Regards

Da
 
K

Ken Johnson

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
 
Top