return value from access into excel based on criteria or content

[dreamkeeper]

I have two unique challenges that I need help figuring out:
1. I have an access database of names
Example:
Name:
Van Gogh, Vincent

I have an excel sheet that has Name but may have incorrect spelling
or variances.
Example:
Name:
VanGogh, Vincent
Vincent Van Gogh
Van Go Vincent
(etc)


I would like to find a way to pull in the correct value for name and
title from access into excel based on exact match, close match or
keyword.


2. I would also like to do the same for name and title combination.
Example- access database has Name and Title
Name:
Van Gogh


Title:
Starry Night


the excel sheet might have incorrect spelling or variance of both
name
and title:
Name:
VanGogh, Vincent
Vincent Van Gogh
Van Go Vincent
(etc)


Title:
Starry nite
Starry night, c1889
stary night
etc.


I would like to find a way to pull in the correct value for name and
title from access into excel based on the combination of Name and
Title for an exact match, close match or keywords such as van gogh
starry.


I am completely stumped but know that there hsoudl be a way I can do
this as access hold all of my records- about 300,000 names and
titles. I get new excel sheets to correct every week and fidn the
correct name and title for possible errors in spelling or variances
in
title name.


I really appreciate your help.
Tina

 

[crferguson]

Personally, I would handle this problem from the Access side so you
don't have to update the spreadsheets you're getting with code or
formulas each time. In Access, import the spreadsheet as a table of
it's own and then set up a couple of queries to cleanup the data per
your specifications. Once the data is clean, move the results over to
your main table.

I wish I could give exact, step-by-step examples, but that would be
way too complicated for a group post. There are too many ways to
tackle your problem and we'd probably go back and forth for days
before coming up with what you need. Plus I'd need more information
and likely some actual example spreadsheets and the database to work
with.

If you want to try and do something, feel free to email me with as
much info as possible or just post back here with more questions. It
would be better to email since i don't visit this group often.

Cory