Compare 2 columns, and create a list of items that are in both lists

R

ruby2sdy

Hi

Can anyone please help me compare two columns of text to come up with a
list of items that are in both lists

Many thanks
 
B

Biff

Hi!

One way:

Assume the lists are in the range A1:B10

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=INDEX(A$1:A$10,SMALL(IF(COUNTIF(B$1:B$10,A$1:A$10),(ROW(A$1:A$10)-ROW(A$1))+1),ROW(1:1)))

Copy down until you get #NUM! errors meaning the data has been exhausted.

Biff
 
P

Peo Sjoblom

One way

=IF(ISNUMBER(MATCH(B1,$A$1:$A$20,0)),B1,"")

copy down, if there are duplicate entries use data>advanced filter to create
a distinct list

--
Regards,

Peo Sjoblom

(No private emails please)
 
A

Ashish Mathur

Hi,

Try this array formula (Ctrl+Shift+Enter)

=if(OR(EXACT(A3,Names1!$A$3:A5)),A3,"")

where A3 is from the first column and Names1!$A$3:A5 is from the second column

Regards,

Ashish Mathur
 
Top