Text searching within a cell, returning the nearest value.

Hello experts,

Need an help in writing a fuction or VBA code for the text in returns the similar (or nearly value match) value of the text.

Example
refer the customer A sheet

and other sheet called Master A sheet

I want to find string search (from Customer A sheet) which will match the nearest string match(in Master A Sheet) returning the customer number.?

I used vlookup but it seems to returning the extact content match not the nearest ... http://www.excelforum.com/images/icons/icon9.png

pls help me and advise me the right function name or VBA where i can do for similar lakhs of records?

waiting for your reply

There is no standard function to find a 'nearest' match in the sense you mean here, the concept of a nearest match is one of those things that is easy for a human, but incredibly difficult for a computer.

Originally Posted by Andrew-R

There is no standard function to find a 'nearest' match in the sense you mean here, the concept of a nearest match is one of those things that is easy for a human, but incredibly difficult for a computer.

thanks for your reply andrew , Hmm let me tell in this way,
Assume a Customer A sheet , cell contain as " Dr Lossen Roy "

and the Master A sheet contain
Name no#

Dr Roy 122
roy 2232
Lossen 2323

Is there any way using VBA where it return values matching with first index of value among top 10 matched found?
I found similarly thread below,
http://www.excelforum.com/excel-prog...urn-match.html

There are ways to do *something*, that's not an issue. The problem is in doing something worthwhile, trustworthy and practical.

It would be trivial to split your search string into individual words and then return all of the cells that match any one of the words, but that would be useless over a large data set because, say, searching on "Dr" would match "Dr Notthe Manwewant" but wouldn't match "Doctor Lossen".

Big companies invest millions of dollars and thousands of person-hours into developing software to do this sort of thing (have a look at IBM SPSS text analyticals, for example), so you're unlikely to get a useful solution from a free forum, sorry.

Your real choices are:

1. Pay however many thousand dollars for software to do this, or
2. Sanitize your data collection/input, or
3. Employ a team of low-paid workers to do this manually.

Sorry if that's not what you want to hear, but it's how it is.

Well, thanks a lot for your usefull info

Can you pls tell me the some more product name like IBM SPSS text analyticals ... etc it

It's not really my field any more, I'm afraid. I used to work for a company which did text searching and analysis, but that was in the late 90s, so my knowledge of specific products is rather out of date. I'm only aware of SPSS because I evaluated it for my current job last year.