+ Reply to Thread
Results 1 to 6 of 6

Help with list of search phrases

  1. #1
    Registered User
    Join Date
    01-13-2011
    Location
    Nantwich
    MS-Off Ver
    Excel 2007
    Posts
    13

    Help with list of search phrases

    Hi all

    I need a bit of help if poss. I have a list of about 6000 companies and I need to compare these to about 200 search terms. These are a little fuzzy so at the moment, I do a "find", and manually check each one. Can anyone advise on a quicker process to check all of them, to bring up all possible matches which I would then delete?

    thanks
    Kelly

  2. #2
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Help with list of search phrases

    Hi Kelly.

    Is possible to upload a small (example)workbook for it??
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  3. #3
    Registered User
    Join Date
    10-15-2011
    Location
    bangalore
    MS-Off Ver
    Excel 2010
    Posts
    99

    Re: Help with list of search phrases

    you can use advance filter , but if you can upload the file , that will be helpful to give a proper solution

  4. #4
    Registered User
    Join Date
    01-13-2011
    Location
    Nantwich
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: Help with list of search phrases

    Sample main database.xlsxCompanies not to mailshot.xlsx

    These are 2 sample - one is the companies we don't want to mailshot and this is the one that may contain incorrect spellings etc. the other is the sample database I match them to.

    Thanks for your help on this
    K

  5. #5
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Help with list of search phrases

    ...Sorry! Even now i don't understand, what you want to compare...

  6. #6
    Registered User
    Join Date
    11-06-2011
    Location
    Massachusetts, USA
    MS-Off Ver
    Excel 2010
    Posts
    52

    Re: Help with list of search phrases

    I think I understand the problem, as I often have to address it as well. The problem is comparing records in two databases when there are inconsistent spellings or name styles. Here is what I do (with my apologies if I have misunderstood the problem.)

    I clean up the name field as best I can in the two databases. See columns E and N of the attached file. Column F shows if I got a match on the clean name. If I substitute the prefixes and suffixes that are common in the database, this usually catches most of the matches. Then, column G compares the street address. I don't try to clean this field - the variations are harder to predict, and I find addresses are usually entered consistently.

    Then analyze the results. Record 5 matches on clean name and street address, so you can conclude it is in both databases. For record 1, do an AutoFilter for 111 A Street in Database 2 and find Jon Smith - it is reasonable that the first name is spelled differently and the records match. For record 2, checking the address finds Chuck Jones vs. Mr. and Mrs. Jones in the same city. Chances are the records match. Filter on name for record 3 and you see one is C Street and the other is North C Street - again, probably a match. For record 4, the name is the same but the address is completely different, so probably not a match unless you have reason to believe one database is older than the other and the person may have moved (especially if the name is distinctive).

    Bottom line - I try to get Excel to do as much of the work as possible, but at some point it comes down to eyeballing and making an executive decision.

    I hope this helps, and maybe others have suggestions on how to get Excel to do more of the work.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1