comparing two columns

[MurderInk]

(posted over at the new users forum(since deleted),but trying my luck
here)

Hi,

I'm looking for a function to compare two columns(not neccesarily with
the same amount of data),and return in another column/or worksheet the
data that is the same.

_for_example:_

        *A*                 *B*             *C*
*1*    orange      grape      -grape-
*2*    apple        orange    -orange-
*3*    grape        plum        -pear-
*4*    banana     pear
*5*    pear

In column *A*,I have a list of five fruits.
In column *B*,I have a list of four fruits.

-someFunction(macro?)- returns in column *C* a list of fruits found in
both columns.

what/how to do?

TIA



------------------------------------------------




------------------------------------------------

 

[Debra Dalgleish]

As answered in excel.newusers:

If you have headings in row 1, with the list items starting in row 2,
enter the following formula in cell C2, and copy down:

=IF(COUNTIF($C$1:C1,A2)>=1,"",IF(COUNTIF(B:B,A2),A2,""))

It will return any item in column A that is duplicated in column B.