Find minor differences between two cells

[kujeremy]

Does anyone know of a way to compare values in two cells and determine if there is only a character or two difference? I've been asked to find a way to compare two addresses and identify if they are minutely different, such as:

123 Main St
vs
123 Main Str

They want to be able to easily identify and filter ones where it is an abbreviation or something minor like that difference.

Don't know if there's a quick & easy way to accomplish this or not but appreciate any insight.

Thanks

 

[NBVC]

Try googling Fuzzy Lookup or Fuzzy Vlookup... you will need an addin or macro to help you.

I have never tried this one, but Contextures has never failed me.... you can test it to see if it meets your needs.

http://blog.contextures.com/archives/2011/05/06/fuzzy-lookup-add-in-for-excel-2010/

 

[kujeremy]

That (Fully lookup) looks like it would do the trick, except for one problem....it's for Excel 2010 and we're on 2007. I have no access to 2010 and even if I did it would be a serious no no to move this data outside of my companies network.

Thanks for that suggestion though

 

[Brianwarnock]

If you read all of the info on the link you would find one poster, Sam, saying that it worked in 2007

Brian

 

[NBVC]

Thanks for pointing that out to the OP, Brian.

 

[kujeremy]

Thanks for the additional input. I was in a hurry to get somewhere and didn't have a chance to look beyond the microsoft site where it said it was for Excel 2010. Unfortunately I can't even give this a try right now on 2007 because our computers are very locked down, I'll have to make a request to get this add-in installed on my machine.

Thanks for the direction everyone!

 

[Galaxiom]

Weighted Damerau-Levenshtein function in Post 10 of this thread is exactly designed for your purpose.

http://www.access-programmers.co.uk/forums/showthread.php?t=246737

 

[NBVC]

That's great Galaxiom. I tested it in Excel and it seems to work great... it's a keeper!

To the OP: It doesn't really work using ranges though.. i.e. like for a lookup. It's meant to compare two cells to each other and give a number...