Incorrect spelling disrupting Vlookup

[DublinMeUp]

Hi guys,

Have 8000 records that im trying to pull data for from 250k records, the base data is supposedly the same but I've noticed that human error on the 8000 records has intervened. There are about 1500 spelling mistakes.

I don't have many columns that the two sheets share so i can't use another comparator.

In order for my vlookup to work is there any way of correcting this on mass? fixing them one by one is not an option as per the volume of the error and work required to fix one ( manually scan 250k records with minimal info until you find the correct spelling then copy into other sheet)

Tried to create an example sheet but can't seem to replicate the issue, it seems to guess the right word even though they are misspelled??

Any ideas for the big file?

[icestationzbra]

if you were to do this manually, what is the logic that you would apply? if there is a clear definition of that, it could be attempted to formulate / programmatise such a logic.

[DublinMeUp]

hmmm.. well my main logic for manually doing it is above ie just look for the word that is similar / obviously misspelled.

The two shared data points are a column of names, which could have more than one word and a column of dates eg 09/11/2009.
If i was two try logically program for it I suppose i'd use the name column as a string comparator and tell it to search within the range of the date attached to it.

So for example in the "lookup" sheet I may have the word "mispelled" and the date 09/11/2009, so i would say go to all rows containing the date 09/11/2009 on the "data" sheet and compare to the range "name" on the data sheet until the most likely match is found ie "misspelled"

Problem is i have no clue how to do any of that lol.

I've managed to get an example of my problem:
vloopuk error.xlsx

[Alf]

Perhaps this thread could be of help to you?

http://www.excelforum.com/excel-prog...o-to-fail.html

Alf

[martindwilson]

i dont think you can,there is some code around that would tell you a % match but other than having a list of words and all the possible ways each could be spelt incorrectly i dont see how it can be done. it would be quicker to find all the ones that match as you have done,filter on false and get a human to fix them!

[FDibbins]

I have had to do something similar, i used filters to find all the same "mis-spelled" words, and then either fixed them there, or used find/replace if there were lots of them

[DublinMeUp]

Yeah i had my doubts, When i said use the name as a comparator i was thinking of one of the first things you learn to do in C, place two strings in an array and run them through a for loop comparing each char as it goes. Didn't know if this was possible in excel / vb

The erroneous records have been dispatched far away from me, I wouldn't have the patience to manually fix 10 never mind 1.5k haha.

P's I'm a beginner in C, Just in case anyone wonders why im doing stuff in excel/VB that could probably be easily fixed in C .