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

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

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

"Mike" wrote in message
...
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

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

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
---

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
---

"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
---