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

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

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

"ruby2sdy" wrote in message
oups.com...
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

One way

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

copy down, if there are duplicate entries use dataadvanced filter to create
a distinct list

--
Regards,

Peo Sjoblom

(No private emails please)


"ruby2sdy" wrote in message
oups.com...
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

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

"ruby2sdy" wrote:

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