match names in 2 different columns
 

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

match names in 2 different columns
 

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

match names in 2 different columns
 

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

match names in 2 different columns
 

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

match names in 2 different columns
 

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

match names in 2 different columns
 

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

match names in 2 different columns
 

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