Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Aug 2004
    Posts
    47
    Thanks
    2
    Thanked 0 Times in 0 Posts

    find nick name in name list (excel 2000)

    I am comparing two name lists. List 2 may contain some name in the main list, which is list 1. What I want to do is to compare these two name lists, if any name in list 2 matches one in list 1, then put those matched names onto a worksheet called

  2. #2
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: find nick name in name list (excel 2000)

    Just as a general query, this does seem the kind of problem that would be better suited for Access than Excel - although there may be very good reasons for continuing with your Excel set-up. Have you considered using Access?
    Gre

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: find nick name in name list (excel 2000)

    I agree with unkamunka that Access would be more suitable for this kind of problem.

    You nickname list could do with some cleaning - many names begin with a space.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: find nick name in name list (excel 2000)

    It would seem to me that the "nickname list" should only be 2 columns:
    Column A: the list of nicknames
    Column B: the "formal name"

    Column A should not only have the nicknames, but also the formal name. You can then use this list to a create and use a "formal name" column in your lists. This would be a vlookup of the person's first name into the the "nickname list" and grab the "formal name". The do the comparison of the "last name" and "formal firstname" with the lastname and formal firstname of the other list.

    The only issue (and I don't really see an easy workaround) is that while the list of "formal names" in the nickname list can and will have duplicates (and this is not an issue), to really work, the "nickname" list should be uniqe (which is not the case). For example, "Al" might be Allen, Alan, Alexander, Alexandra, Algernon, etc. With multiple nicknames, how would you decide which is the "correct formal name". Some nicknames are also "formal names": Steve might be short for Steven, Stephen, Stephanie, Stefania but also might just be "Steve".

    This "non-uniqueness" will be a problem with excel solutions and even access solutions...

    Steve

  5. #5
    Lounger
    Join Date
    Aug 2004
    Posts
    47
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: find nick name in name list (excel 2000)

    Thanks for all your prompt reply. I didn

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: find nick name in name list (excel 2000)

    If you are going to have duplicate names and you will check them manually, then I would make the Nick name list 2 cols:
    1) unique nickname
    2) formal first name

    Again the formal firstname does not have to be unique. The unique nickname list must be. If you find a nickname that has more than 1 possibility put it into the list onetime and use as a formal first name some kind of "flag" (eg "Duplicate") so that the formal name in the formal name column (after the lookup) will be marked.

    Another option would be (if you kept a non-unique nickname/formal name list) would be checking by using a countif to see see if the nickname had more than 1 formal name and if so, use use the "flag/indicator" of duplicates.

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •