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


--
patman
------------------------------------------------------------------------
patman's Profile: http://www.excelforum.com/member.php...o&userid=36739
View this thread: http://www.excelforum.com/showthread...hreadid=564605

comparing two columns of data to find common values
 

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


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=564605

comparing two columns of data to find common values
 

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