Find Common names in multi ranges
 

I have 4 columns of names. I would like to search all 4 columns (4
individual ranges) and find the names that appear in all 4 and copy the
names to individual cells.

The 4 Columns of names are with other data in the columns and they are
spread out on the sheet. I
could not search the whole column but only a range in a column ( 4
ranges actually), don't know if this will make a difference.

Thanks for any help and Merry Christmas (Happy Holidays..... pc)

Find Common names in multi ranges
 

Is each column the same length? How about telling us the ranges?

Will a name appear in any column only once?

Biff

"JG" wrote in message
ups.com...
I have 4 columns of names. I would like to search all 4 columns (4
individual ranges) and find the names that appear in all 4 and copy the
names to individual cells.

The 4 Columns of names are with other data in the columns and they are
spread out on the sheet. I
could not search the whole column but only a range in a column ( 4
ranges actually), don't know if this will make a difference.

Thanks for any help and Merry Christmas (Happy Holidays..... pc)

Find Common names in multi ranges
 

One way I think you could do it. Let's say your ranges are on Sheet3 in
cells A1:A6, A8:A13, A15:A20, and A22:A27. You could copy A1:A6 to cells
B2:B7 of a new sheet (assume row 1 of the new sheet has some kind of header).
Then in cell A2 of the new sheet enter:

=ISNUMBER(MATCH(B2,Sheet3!$A$8:$A$13,0))*ISNUMBER( MATCH(B2,Sheet3!$A$15:$A$20,0))*ISNUMBER(MATCH(B2, Sheet3!$A$22:$A$27,0))

and copy down. Then turn on the autofilter and filter column A for "1" and
you should have your list of names that appear in all 4 ranges. You could
then copy this filtered list to another location. Or you could filter the
list for "0" and delete those rows, then turn off autofilter and what remains
should be just those that appear in all 4 ranges.


"JG" wrote:

I have 4 columns of names. I would like to search all 4 columns (4
individual ranges) and find the names that appear in all 4 and copy the
names to individual cells.

The 4 Columns of names are with other data in the columns and they are
spread out on the sheet. I
could not search the whole column but only a range in a column ( 4
ranges actually), don't know if this will make a difference.

Thanks for any help and Merry Christmas (Happy Holidays..... pc)

Find Common names in multi ranges
 

Assume the ranges are A1:A10, C1:C10, E1:E10, G1:G10

Create this named formula:
Goto InsertNameDefine:
Name: NameCount
Refers to:

=(COUNTIF($C$1:$C$10,$A$1:$A$10))+(COUNTIF($E$1:$E $10,$A$1:$A$10))+(COUNTIF($G$1:$G$10,$A$1:$A$10))

Then, enter this formula as an array using the key combination of
CTRL,SHIFT,ENTER (not just ENTER) where you want the extracted names to
appear:

=INDEX(A$1:A$10,SMALL(IF(NameCount=3,ROW(A$1:A$10)-ROW(A$1)+1),ROWS($1:1)))

Copy down until you get #NUM! errors meaning all names have been extracted.

Biff

"T. Valko" wrote in message
...
Is each column the same length? How about telling us the ranges?

Will a name appear in any column only once?

Biff

"JG" wrote in message
ups.com...
I have 4 columns of names. I would like to search all 4 columns (4
individual ranges) and find the names that appear in all 4 and copy the
names to individual cells.

The 4 Columns of names are with other data in the columns and they are
spread out on the sheet. I
could not search the whole column but only a range in a column ( 4
ranges actually), don't know if this will make a difference.

Thanks for any help and Merry Christmas (Happy Holidays..... pc)

Find Common names in multi ranges
 

If you want an error trap:

Create this named formula:
Goto InsertName<Define:
Name: TotalNames
Refers to:

=SUM(IF((COUNTIF($C$1:$C$10,$A$1:$A$10))+(COUNTIF( $E$1:$E$10,$A$1:$A$10))+(COUNTIF($G$1:$G$10,$A$1:$ A$10))=3,1))

Then the array formula becomes:

=IF(ROWS($1:1)<=TotalNames,INDEX(A$1:A$10,SMALL(IF (NameCount=3,ROW(A$1:A$10)-ROW(A$1)+1),ROWS($1:1))),"")

Copy down until you get blanks meaning all names have been extracted.

Biff

"T. Valko" wrote in message
...
Assume the ranges are A1:A10, C1:C10, E1:E10, G1:G10

Create this named formula:
Goto InsertNameDefine:
Name: NameCount
Refers to:

=(COUNTIF($C$1:$C$10,$A$1:$A$10))+(COUNTIF($E$1:$E $10,$A$1:$A$10))+(COUNTIF($G$1:$G$10,$A$1:$A$10))

Then, enter this formula as an array using the key combination of
CTRL,SHIFT,ENTER (not just ENTER) where you want the extracted names to
appear:

=INDEX(A$1:A$10,SMALL(IF(NameCount=3,ROW(A$1:A$10)-ROW(A$1)+1),ROWS($1:1)))

Copy down until you get #NUM! errors meaning all names have been
extracted.

Biff

"T. Valko" wrote in message
...
Is each column the same length? How about telling us the ranges?

Will a name appear in any column only once?

Biff

"JG" wrote in message
ups.com...
I have 4 columns of names. I would like to search all 4 columns (4
individual ranges) and find the names that appear in all 4 and copy the
names to individual cells.

The 4 Columns of names are with other data in the columns and they are
spread out on the sheet. I
could not search the whole column but only a range in a column ( 4
ranges actually), don't know if this will make a difference.

Thanks for any help and Merry Christmas (Happy Holidays..... pc)