how to find an intersect of two columns

[sarora]

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

 

[JMB]

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]

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

 

[JMB]

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.