EXCEL FILTERING

EXCEL FILTERING - aghh!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

snugs25 - 13 Dec 2005 08:55 GMT

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

Reply to this Message

Bob Phillips - 13 Dec 2005 09:42 GMT

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

Signature

HTH

Bob Phillips

(remove nothere from email address if mailing direct)

> 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,
[quoted text clipped - 6 lines]
> Thanks
> Gemma

Reply to this Message

Arvi Laanemets - 13 Dec 2005 09:53 GMT

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.

Signature

Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )

> 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
[quoted text clipped - 9 lines]
> Thanks
> Gemma

Reply to this Message