0

I have a table of strings that I'm trying to do an analysis of by pivoting. The problem is that I'm seeing similar strings that were just entered incorrectly and are therefore being treated as entirely separate.

For example:

PLSMPPOL130WAR

and

PLSMPPOL30WAR

In researching this, I came across the excel 'Fuzzy Lookup' tool, but that appears to be for table to table matches and all of my data is within the same table.

How can I have a formula or macro that will return strings within, say, 3 characters of the lookup value?

aLearningLady
  • 1,988
  • 4
  • 24
  • 42
  • You should maybe research using the Levenshtein distance to accomplish this. – nbayly Aug 01 '17 at 17:26
  • @pnuts I've been trying that for the past hour and it's still running. I have a couple thousand rows of data and it seems to complete 1 row every 10-30 seconds. I'll report back when it's done or when Excel decides to crash.... – aLearningLady Aug 01 '17 at 18:21
  • Yes there is a 'similarity threshold' setting, but I left it at the default value to see what it returns as the slider doesn't indicate what exactly that means... – aLearningLady Aug 01 '17 at 18:30
  • Looks like Excel gave up - 'System.OutOfMemoryException' was thrown – aLearningLady Aug 01 '17 at 18:30
  • Cranked it up to .9 and am running it again – aLearningLady Aug 01 '17 at 18:33
  • Alright - looks like matching my data with a copy of it simply returned the exact same lookup value as a 1.0 match. No dice on Fuzzy Lookups. – aLearningLady Aug 01 '17 at 18:37
  • Without sample data and expected results, it's difficult to give an accurate answer. However, I believe the answer provided for this similar question may be useful: https://stackoverflow.com/questions/43873223/text-similarity-analysis-excel – tigeravatar Aug 01 '17 at 19:07

0 Answers0