Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inputting data to one worksheet for it effect another | Excel Discussion (Misc queries) | |||
Find data in columns, then place in rows | Excel Worksheet Functions | |||
how do you prevent data from changing values when sorting linked . | Excel Discussion (Misc queries) | |||
Finding common data in multiple columns and rows in Excel | Excel Worksheet Functions | |||
Comparing data in two columns and highlighting the data | Excel Worksheet Functions |