Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 21

Thread: Identify Matching names

  1. #1

    Identify Matching names



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Column “B” contains the authors and their affiliations for each paper (462 papers). The authors(s) for each affiliated organization are included within brackets just before the name of the organization.

    Column A contains the last names of all authors of the 462 papers listed in Column B
    Please see attached file

    The question:
    I need to identify those authors in column “A” that are affiliated with “King Saud Univ” per column “B”. Maybe by indicating True in column "C" in the same row that match the author affiliated with "King Saud Univ"

    Is there any formula to do such job?. I have large number of files with similar job.

    Help will be greatly appreciated.
    Taisir
    Attached Files Attached Files

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    It looks like ever cell in column B contains the phrase "King Saud Univ".

    You can use:

    =Isnumber(search("King Saud Univ",B2))

    copied down.

    This will give you TRUE if found, FALSE if not found.


  3. #3
    Quote Originally Posted by NBVC View Post
    It looks like ever cell in column B contains the phrase "King Saud Univ".

    You can use:

    =Isnumber(search("King Saud Univ",B2))

    copied down.

    This will give you TRUE if found, FALSE if not found.

    Dear Sir,
    Many thanks
    Indeed, every cell in column "B" contains "King Saud University"

    But I need to figure out which authors in column "A" are affiliated with “King Saud Univ” per column “B”.

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    I don't understand. Please give some examples of expected results and why they are.


  5. #5
    Quote Originally Posted by NBVC View Post
    I don't understand. Please give some examples of expected results and why they are.
    many thanks again,

    If you examine any cell in column "B", you will find that a number of organizations participated in publishing the research paper including "King Saud Univ". For example in in B2; Naushad, Mu is affiliated with King Saud Univ. The rest of the authors worked in that paper are affiliated with other organizations.

    Column A contain a comprehensive list of all outhors fro all organizations. I need to identify only those affiliated with King Saud Univ.

    I hope that helps.


    All the best and many thanks again. Taisir

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    A little difficult to do with formulas, since you have some sections with multiple names in the [ ] brackets, making the string length with a large length range.

    The best I can come up with (someone can probably do it better with some VBA and regular expressions) is.

    Using helper formulas...

    In C2 enter:

    =IF(SEARCH("King Saud Univ",B2)<=100,LEFT(B2,100),MID(B2,SEARCH("King Saud Univ",B2)-64,1000))

    copied down. This extras a certain number of characters before the searched key phrase. It is hoped that all the names between [ ] are captured.

    in D2:

    =SUBSTITUTE(MID(C2,FIND("[",C2)+1,FIND("]",C2)-FIND("[",C2)-1),",","")

    copied down. This extracts the names in [ ] before the key phrase only. If you see #VALUE error, it means it did not extract all the names, and you may have to manually copy the names from column B into column D at that cell.

    then, finally, in E2:

    =ISNUMBER(LOOKUP(10^10,SEARCH(" "&$A$2:$A$463&" "," "&D2&" ")))

    copied down. This will let you know if any of the names within the [ ] brackets is found in column A.

    Please note. The accuracy of columns C and D may not be perfect. Randomly check names in column D against column B to ensure the correct set of names was extracted. If not, manually correct column D.


    EDIT: I have revised the formulas for better accuracy. (Still not perfect, but better).
    Also, for even better accuracy, you should change the names in column A to exactly match the names found within column B, including initials etc.

    Example why... in D11, the name
    Khan, Moonis Ali is extracted. But the search will find Ali in A7 and say it is a match, since Ali is one of the "words" in column D. The Khan name is repeated multiple times in column A. Are they all the same person?
    Last edited by NBVC; 2014-11-21 at 03:07 PM. Reason: Revised formulas for better accuracy


  7. #7
    Just a quick reply to thank you. I will try your solution. All the best Taisir

  8. #8
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    Note, that I have edited my formulas and commented more.


  9. #9
    many thanks again
    your formulas were a great help. I had only 20+ #VALUEs which I plan to do manually.

    My concerns, I am not sure what the magnitude of the number of #VALUE when I use it with larger files 8000 entry.


    I hope somebody in the forum will help with a VBA.

    Unfortunately names in Column "A" are given Last Name space First Initial where as the names listed in the column B are Last Name comma First Name. Only the last Name is common between the two Columns

  10. #10
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    In your file, you have only last name listed in column A. If it is as you say, then you can add another helper column to make column A names look like column B names. Then you can compare column D to that column for hopefully more accurate results.

    If you have 8000 entries, the formulas will work very slowly. In that case, you're better bet would be the VBA. But again, the cleaner the original file is, the easier the VBA solution would be too.


Page 1 of 3 1 2 3 LastLast

Posting Permissions

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