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

[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

[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

"ruby2sdy" <scott.steel@optusnet.com.au> wrote in message
news:1128727669.459825.184480@g14g2000cwa.googlegroups.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
>

[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)


"ruby2sdy" <scott.steel@optusnet.com.au> wrote in message
news:1128727669.459825.184480@g14g2000cwa.googlegroups.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
>

[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

"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
>
>