|
PureBytes Links
Trading Reference Links
|
You may want to take a different approach and have the cells that
the user may possibly have a typo be a cell with data validation.
Just validate it against a list. That way, you don't have the GIGO
problem.
Cash
Date sent: Sun, 7 Jul 2002 17:53:57 EDT
From: Shane Devenshire <ShaneDevenshire@xxxxxx>
Subject: Re: Vlookup look for like item
> Kylie Manning asked
>
> Does anyone know if it is possible to get a vlookup (or anything else) to
> look for text that is similar to the original. There may be a spelling
> mistake or a typo.
>
> =============================
> Hi Kylie,
>
> What you are looking for is a "sounds like" option for the Find command.
> Even though Word has this feature and Excel does not, the Word one is not
> always effective. For example I searched a document containing Shame for any
> occurrences that sounded like Shane and got no hits.
>
> One possible solution is, if you know that there is only one occurrence of
> the name you can use VLOOKUP, HLOOKUP, LOOKUP or MATCH to find if there are
> any occurrence with the exact spelling you want. If not you must have a
> misspelling. For example VLOOKUP("Shane",MyRange,1,FALSE) would return an
> #NA if no match was found in the first column of MyRange.
>
> You could also use the Find command manually or in code to search for Shane,
> if you get a "Cannot find data..." message you will know that there is an
> incorrect spelling.
>
> Note you can use VLOOKUP and MATCH with wild cards such as * and ? However
> this technique is no guarantee. For example S* will find Shame, *S*, *S*h*,
> *S*h*a* and other variations will, but *S*h*a*n* will not. And no
> combination of wildcards will find it if the misspelling is Dhane, you would
> need to guess which letters were included and which were not. You could
> write code to do a similar thing.
>
> Maybe someone on the list has written a routine for a "sounds like" search.
>
> Good luck,
> Shane Devenshire
>
> --------------------------------------------------------------------------
> The EXCEL-G list is hosted on a Windows NT(TM) machine running L-Soft
> international's LISTSERV(R) software. For subscription/signoff info
> and archives, see http://peach.ease.lsoft.com/archives/excel-g.html .
> COPYRIGHT INFO:
> http://peach.ease.lsoft.com/scripts/wa.exe?SHOWTPL=COPYRIGHT&L=EXCEL-G
>
Cash Coyne
Bright Trading, LLC
Remote Trading Manager
http://remote.brighttrading.com
Phone: 919-852-4454
Fax: 775-254-3461
PalTalk: CashC or BrightRemote
AOL Instant Messenger: TraderCash
Yahoo Instant Messenger: cashonly
MSN Instant Messenger: cashonly
|