cross reference two spreadsheets
 

I have two spreadsheets with employee names on them. They are the same
employees but they come from two different systems. The names in one of the
systems sometimes have mispellings. I join the two spreadsheets by employee
name and when I do the one that are mispelled in one spreadsheet do not pull
over to the other spreadsheet. Is there a way that I can cross reference or
get the spreadsheets to recognize they are the same every time I pull the two
spreadsheets together without manually changing each name every time. I can
sometimes have twenty to thirty names that are mispelled.

cross reference two spreadsheets
 

MAD101 wrote:
I have two spreadsheets with employee names on them. They are the same
employees but they come from two different systems. The names in one of the
systems sometimes have mispellings. I join the two spreadsheets by employee
name and when I do the one that are mispelled in one spreadsheet do not pull
over to the other spreadsheet. Is there a way that I can cross reference or
get the spreadsheets to recognize they are the same every time I pull the two
spreadsheets together without manually changing each name every time. I can
sometimes have twenty to thirty names that are mispelled.

MAD1,

I feel your pain. /Misspelling/ is a difficult thing to overcome in the
data world.

A few thoughts for you...

How would you reconcile names like "Tim McDougal" and "Timothy
MacDougal"? Are they different people, or the same? How about "Bob
Smith", "Robert Smith" and "Rob Smith"?

Programmatically, I think what you ask is impossible. A program cannot
discern distinct people based on name alone. Programmatically it may be
possible to give hints, but it would be far more efficacious if
something more concrete was available to match on, such as DOB, SSN,
address, etc.

So even if a soundex routine (e.g.,
http://www.google.com/search?hl=en&q=excel+soundex+vba) gives hints, you
still need to sift through and verify the results.

Were I you, I would be grateful there are only 20-30 misspellings to
attack manually.