EXCEL FILTERING - aghh!

S

snugs25

Hello, would be grateful if someone could help me here. I have 2 lists of
names in 2 different columns. one column has the names first and then last,
the other has the names last and then first. Ive used a foruma provided by
someone else on this site to reverse the order which works no problem. Now
however when i try and filter the names to compare which people are on both
lists, it doesnt return with any results, yet i know there are names which
appear on both lists. if anyone can help me i would reaaallly appriciate it.

Thanks
Gemma
 
B

Bob Phillips

There could be a myriad of reasons. One may have a comma and the other not
which isn't excluded, there might be embedded/leading/trailing spaces, your
formula may be wrong.

Without examples of the data and the formula, we are just guessing. It is
possible, I just knocked a test up, where I had Bob Phillips in one column,
Phillips, Bob in another, and this formula found it

=COUNTIF(A:A,MID(B2,FIND(", ",B2)+2,99)&" "&LEFT(B2,FIND(", ",B2)-1))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
A

Arvi Laanemets

Hi

Your case is an excellent example when using full name in table is a wrong
solution.

When you design your table so, that instead ov 2 name columns you have
columns ForeName, Lastname, your life will be much easier. Whenever you need
to get the full name, you can have it in any form
=ForeName & " " & LastName
or
=LastName & " " & Forename
or
=LastName & ", " & Forename
etc.

Also, you always can sort your table at 1st by Forename and then by
LastName, or vice versa.
 
Top