Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2006
    Posts
    4

    Unanswered: Matching on similar/equivalent fields between two tables

    Hello, thank you for your attention in advance.

    This seems like a simple problem, but I cannot find a simple solution.

    Two Tables,

    T1 (Table 1) Long list of company names, entered inconsistently
    T2 (Table 2) Short list of company names, unique

    I would like to match based on equivalence between T1 & T2, and display or update T1 with equivalently matched data from T2.

    Example:

    T1
    ATT
    WELLSFARGO
    BANKOFAMER
    WellsF
    W Fargo
    Bank of Amer.

    T2
    AT & T
    Wells Fargo
    Bank of America

    Desired results are a new column in T1, with normalized naming as follows:

    T1-Original Column-----T1-Additional/Results Column
    ATT--------------------AT & T
    WELLSFARGO-----------Wells Fargo
    BANKOFAMER-----------Bank of America
    WellsF-----------------Wells Fargo
    W Fargo---------------Wells Fargo
    Bank of Amer.----------Bank of America

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Sorry fella. Unless someone knows of something really cunning you will need to associate this manually.

    You might be able to use a soundex algorithm that will short cut a lot of the donkey work but I would expect anything we come up with to have stuff "fall through the gaps". A really simple start would be to replace all non alpha characters (" ", ".") with a "*" and do a like comparrison. You could also create a vb UDF to parse the input string (the column entries from T1) and add a "*" before every capitalised letter that is preceded with a lower case letter too.

    Are you hoping to do this at run time or are you intending to clean the data?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Sep 2006
    Posts
    4
    Thank you Pootle. Yeah, after researching this, there does not seem to be a simple solution. My intent was to update a new column with company names that matched.

Posting Permissions

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