comparing two columns of data to find common values

  • Thread starter Thread starter patman
  • Start date Start date
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
 
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
 
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
 
Back
Top