how to find an intersect of two columns

Hi,
I am new to this forum and am trying to explore excel. I want to find the common names between two columns and get the number of the common names. how do i do it
Thanks
sarora

If one list is in B1:B4, the other in A1:A10, this seems to work. Change
ranges as necessary:

SUMPRODUCT(--ISNUMBER(MATCH(B1:B4,A1:A10,0)))

"sarora" wrote:

>
> Hi,
> I am new to this forum and am trying to explore excel. I want to find
> the common names between two columns and get the number of the common
> names. how do i do it
> Thanks
> sarora
>
>
> --
> sarora
> ------------------------------------------------------------------------
> sarora's Profile: http://www.excelforum.com/member.php...o&userid=34860
> View this thread: http://www.excelforum.com/showthread...hreadid=546091
>
>

Hi,
Thanks for the reply but being a beginner i didnt understand it completely. if i have one set of names in column A and the other set in column B, how do i pick the names common to both the columns.
Thanks a lot
shilpi

Sorry, I misunderstood. I thought you just wanted the number of duplicate
items, but you want to identify the actual duplicate items. One way, in C1
enter

=IF(ISNA(MATCH(B1,A:A,0)),"","X")

copy down as far as the data in column B. the cells with an "X" are
duplicates. you can then use an autofilter on column C to filter and copy
the data to another worksheet if needed.


"sarora" wrote:

>
> Hi,
> Thanks for the reply but being a beginner i didnt understand it
> completely. if i have one set of names in column A and the other set in
> column B, how do i pick the names common to both the columns.
> Thanks a lot
> shilpi
>
>
> --
> sarora
> ------------------------------------------------------------------------
> sarora's Profile: http://www.excelforum.com/member.php...o&userid=34860
> View this thread: http://www.excelforum.com/showthread...hreadid=546091
>
>