Results 1 to 10 of 10
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: match Excel list from Access (Excel 2003)

    I'd import or link the Excel table in the Access database, and create a query based on the Access table and the Excel table, joined on the appropriate field(s). The query will return only matching records.

  2. #2
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    match Excel list from Access (Excel 2003)

    I have an Access file with about 260K records (company name, address, etc.) In Excel I have a list of about 6300 companies. I'd like to see if any of the 6300 companies exists in the Access table. What's the easiest way to do this? This is a one-time task I need to get done quickly. I've played with the MS Query and was able to connect to the .mdb file but wasn't able to figure out how to do this matching exercise. Is it better to copy the Excel names to Access and somehow to a search?

    Added note: I did a manual search on one company name and even though it's listed, I don't know how to make Excel or Access consider it a match.
    <pre>1-800-Flowers.com, Inc (in master Access file)
    800 FLOWERS (in my Excel list)</pre>

    So I can't do a full field match, or even case-insensitive since those aren't consistent. Somehow I need to match partial words (800-Flowers vs. 800 FLOWERS).

    Maybe some with the LIKE attribute?

    Thnx,
    Deb

  3. #3
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: match Excel list from Access (Excel 2003)

    Ok I started to do that but as you can see in my updated post (I added an instance of one company), the names will not be an exact match.

    1-800-Flowers.com, Inc
    800 FLOWERS

    How can I set the query to consider these matches?

    Deb

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

    Re: match Excel list from Access (Excel 2003)

    Matching names with inconsistent spelling can be a nightmare. I'd start by looking for direct matches (Access comparisons are case insensitive, so you won't have to worry about that). Then remove the direct matches from the Excel table. Next, you'd have to do some manual comparing to get an idea of what kind of differences you want to take into account.
    Possible tricks are:
    - Remove all spaces, dashes, punctuation etc. (this can be done in a query, so as not to modify the original data, although that will have a negative impact on performance), or
    - Replace all spaces, dashes, punctuation etc. with a single character, for example a space.
    This is to be done on both sides. You can then use the InStr function in a query to determine whether a string on one side is part of a string on the other side.
    You may have to chop the Access table into smaller parts to keep things manageable.

    PS There are commercial services for "fuzzy" matching. No idea about reliability and pricing, but it might be more efficient than a major programming effort.

  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

    Re: match Excel list from Access (Excel 2003)

    <P ID="edit" class=small>(Edited by sdckapr on 24-Jan-06 21:31. Add PS)</P><post#=282,837>post 282,837</post#> has a couple of functions for excel that will look for "near matches" using some simple logic.

    Steve

    PS you might also Checkout <post:=235,760>post 235,760</post:> which has a function called UStrip which returns the text with everything stripped out but text and numbers and also makes it uppercase. It makes comparisons a little easier since so many "unimportant" things are eliminated.

  6. #6
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: match Excel list from Access (Excel 2003)

    Hey this is great, I was about to write a function to strip out all punctuation and later numbers. This will save a lot of time. First I'll do as suggested earlier, just run a query against what I have, then I'll start stripping characters and see what comes up. I have to get this done by end of the day Wed.

    It's amazing how our brains can instantly look at "1-800-Flowers" and "800Flowers" and know they're the same but a computer needs a lot of expensive software and fuzzy algorithms to do the same thing. Just shows they're just stupid boxes, fast stupid boxes, but stupid. Now if I typed either one of those names into Google to search, it'd have no problem (even if I spelled flowers wrong) but then again, they worked long and hard on their secret algorithms.

    Thnx,
    Deb

  7. #7
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: match Excel list from Access (Excel 2003)

    Actually, one more thing.... for the post with the "nearmatch" code in <post#=282837>post 282837</post#>, I'll need to do this from the Access database too since the source file is 246K records. This post has:
    =nearmatch(A1,B1:B10,6)
    A1 is the value to lookup
    b1:b10 is the range
    6 is the number of characters to "near match"

    and in my case B1:B10 is the 246K Access records. How should I proceed? Should I pull in the first 64K records, do the search, then pull in the next 64K records? That's mighty ugly but I haven't used MS Query enough to know if it can do part of this lookup for me.

    Ahhh, I can see I'm in for a long night <img src=/S/nosleep.gif border=0 alt=nosleep width=27 height=15>

    Thanks again,
    Deb <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

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

    Re: match Excel list from Access (Excel 2003)

    If you want to use Steve's Excel function, you'll have to pull the Access table into Excel in chunks.
    If you want to perform the match in Access, you'll have to write Access VBA code. Access does not have worksheet functions.

  9. #9
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: match Excel list from Access (Excel 2003)

    Is there a way from MS Query to fetch the first 'n' records? I can't figure out how to read in the 246K records from Access in groups of 64K. Is ADO the only way to go? I tried exporting from Access but couldn't get it to export a specific range of records (like 66000-88000) as it always just exported the first 64K.

    I'm a dud with Access, can do simple queries and create tables, bla bla, but nothing fancy.

    Once I have my 4+ sheets with 64K records then I can try the 'nearmatch' code mentioned earlier.

    Thnx, Deb

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

    Re: match Excel list from Access (Excel 2003)

    You could create a simple query in Access or in MS Query with criteria to select only part of the table. If the table has a numeric ID field, you could use criteria such as

    <65000

    or

    Between 65000 and 130000

    Otherwise, you may have to use text criteria, you'll have to use trial and error to make the query return a suitable number of record. Examples:

    <"J"

    or

    Between "J" and "S"

    Export the query to Excel, then modify the criteria to select the next chunk, etc.

Posting Permissions

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