Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    250
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unhappy matching cells with similar information

    Hello,

    I need some help. I am comparing hospital information that comes from two different sources. The only column they have similar is the hospital name, however, the name is not the same in some cases eventough they refer to the same hospital. For example one source could say San Jose Medical Center and the other source can says San Jose Hospital. What is the best way to compare them? Is there a formula I could use or VBA. I have MS Office 2010. Any help would be great. Let me know if you want me to post a sample of the data. Thanks.

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 984 Times in 914 Posts
    If the hospital names vary that much I don't fancy your chances comparing the data. Do you have some examples of things that match, then we can see if it's possible.

    cheers, Paul

  3. #3
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts

    Try an Advanced Filter

    Could try on each list an Advanced Filter copied to another location to get a unique list of the hospital names from each list. Then decide which names need to be changed in one of the lists.

    Or as Paul suggested attach an example.

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    One approach would to make a look up table where you listed each variation on a name and assign a number in the second column, giving each variation of the same name the same number. Then add a column to your table with a vlookup to retrieve the correct number, sort by the number column. Check for items w/o a number, add to table, repeat!. I know it's tedious but should get the job done.

    Good Luck.
    Last edited by RetiredGeek; 2011-01-24 at 14:27. Reason: Grammer
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    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
    You may find the "NearMatch" functions in the thread at http://windowssecrets.com/forums/sho...ight=nearmatch helpful...

    Steve

  6. #6
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    250
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Here is a small sample of the data.
    Attached Files Attached Files

  7. #7
    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
    I would do essentially as tfspry suggested.
    Use advanced filter to get a list of the unique names used.
    Sort this list.
    In the column next to it add the name of the corrected name (you could copy the original column and edit the particular entries)
    Then you could add a new (temporary) column to your dataset
    Use a vlookup to lookup the name in the list and get the corrected name
    COpy and paste-special values this column over the incorrect names and delete the temporary column

    Steve

  8. #8
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Your sample lists of hospital names is helpful. As RG and Steve indicate, goal should be to show one common Hospital Name for both lists of Hospital Names. What is the common name for "AGNEWS STATE HOSPITAL" ?

    Can you provide some sample data for each source of data (in addition hospital name) info on separate tabs in one workbook? We can show how it can be solved.

    The people here can help you with this !!! We will need to know the names you want to use as common names for each hospital.

  9. #9
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 984 Times in 914 Posts
    The sample data doesn't seem to be that different. I think you could just list all the data and sort, as long as you use colour to differentiate the sources.

    cheers, Paul

Posting Permissions

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