match names in 2 different columns

[Mike]

Hey guys, how can I match names in two different columns and kick out the
matches in a 3rd column?

For example

bill john john
mary mike mike
mike david
john sam


any ideas?

Thanks!

Mike

[Gary''s Student]

In C1 enter:
=IF(COUNTIF(A:A,"=" & B1)>0,B1,"")
and copy down
--
Gary''s Student


"Mike" wrote:

> Hey guys, how can I match names in two different columns and kick out the
> matches in a 3rd column?
>
> For example
>
> bill john john
> mary mike mike
> mike david
> john sam
>
>
> any ideas?
>
> Thanks!
>
> Mike
>
>

[Don Guillett]

this formula will do it but if you don't want blanks you will need a macro
=IF(COUNTIF($J$1:$K$4,J1)>1,J1,"")

--
Don Guillett
SalesAid Software
dguillett1@austin.rr.com
"Mike" <Mike@discussions.microsoft.com> wrote in message
news:DFD53DB6-040F-45D3-9182-2D2AA847FD97@microsoft.com...
> Hey guys, how can I match names in two different columns and kick out the
> matches in a 3rd column?
>
> For example
>
> bill john john
> mary mike mike
> mike david
> john sam
>
>
> any ideas?
>
> Thanks!
>
> Mike
>
>

[Max]

Another play which dredges the results out neatly at the top ..

Assuming names listed in cols A & B, from row1 down

Put in C1:
=IF(ISERROR(SMALL(D:D,ROW(A1))),"",INDEX(B:B,MATCH(SMALL(D:D,ROW(A1)),D:D,0)))

Put in D1:
=IF(B1="","",IF(ISNUMBER(MATCH(B1,A:A,0)),ROW(),""))

Select C1:D1, fill down to last row of data in col B

Col C will auto-extract the names in col B which are within col A,
with all results neatly bunched at the top
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Mike" wrote:
> Hey guys, how can I match names in two different columns and kick out the
> matches in a 3rd column?
>
> For example
>
> bill john john
> mary mike mike
> mike david
> john sam
>
>
> any ideas?
>
> Thanks!
>
> Mike
>
>

[Max]

The preceding play extracts in col C
> .. names in col B which are within col A,
> with all results neatly bunched at the top

If you need to (conversely) match it the other way around
(i.e. names in col A with those within col B),
just similarly ..

Place in C1:
=IF(ISERROR(SMALL(D:D,ROW(A1))),"",INDEX(A:A,MATCH(SMALL(D:D,ROW(A1)),D:D,0)))

In D1:
=IF(A1="","",IF(ISNUMBER(MATCH(A1,B:B,0)),ROW(),""))

Select C1:D1, fill down to last row of data in col A

Col C will auto-extract the names in col A which are within col B,
with all results neatly bunched at the top ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

[Mike]

Excellent!! Thanks guys! I tried it both ways! Did exactly what I needed!

Thanks again!

Mike


"Max" wrote:

> The preceding play extracts in col C
> > .. names in col B which are within col A,
> > with all results neatly bunched at the top
>
> If you need to (conversely) match it the other way around
> (i.e. names in col A with those within col B),
> just similarly ..
>
> Place in C1:
> =IF(ISERROR(SMALL(D:D,ROW(A1))),"",INDEX(A:A,MATCH(SMALL(D:D,ROW(A1)),D:D,0)))
>
> In D1:
> =IF(A1="","",IF(ISNUMBER(MATCH(A1,B:B,0)),ROW(),""))
>
> Select C1:D1, fill down to last row of data in col A
>
> Col C will auto-extract the names in col A which are within col B,
> with all results neatly bunched at the top ..
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---

[Max]

"Mike" wrote:
> Excellent!! Thanks guys! I tried it both ways! Did exactly what I needed!
> Thanks again!

Great to hear that, Mike !
Thanks for the feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---