5

I'm trying to check how many strings in column A approximately match a string in column B.

Example:

If I have the string "angry_birds_iph_app" in column B, and "angry_birds_iph_app" and "angry_birds_adrd_app" appear somewhere in column A, I would like the function to return 2.

Tom Wall
  • 125
  • 1
  • 2
  • 9
  • 1
    ... You have to be more specific about what you mean by "approximately match" - Will the only difference always only be the "iph" "adrd"? Will they always have the same beginning? Etc... – John Bustos Feb 11 '13 at 22:14
  • No, they will not always have the same beginning. What I mean by approximate match means that >60% or so of the string matches. It could be facebook_iph_app in in column B, and facebook_web, facebook_adrd_app, and facebook_app appear in column A. In which case, the function would return 3 @JohnBustos – Tom Wall Feb 11 '13 at 22:30
  • 1
    Maybe some thing like http://stackoverflow.com/questions/4243036/levenshtein-distance-in-excel – Tim Williams Feb 12 '13 at 01:40

2 Answers2

8

Take a look at the Excel Fuzzy Lookup add-in. It is free to download from Microsoft. The tool implements the Levenshtein edit distance algorithm to return a similarity score between rows. Among other customizable features, the tool also allows you to set a threshold for how well the values must match in order to return the approximate result.

Rather than returning the count like you mentioned, the tool will return a table with the most similar match in column A relative to each entry in column B. From there you could simply use a filter and perform a count on the resulting column.

http://www.microsoft.com/en-us/download/details.aspx?id=15011

Jason
  • 2,115
  • 15
  • 21
3

In addition to the Excel Fuzzy lookup add-in, there is another add-in (also a free download) that provides a couple functions that may be helpful. One is pwrVLOOKUP and provides a fuzzy vlookup capability. The other is pwrSIMILARITY and would give you a similarity score (between 0 and 1) from which you can base your matching within your worksheet. The PowerUps add-in for Excel works in Excel 2003 thru 2013.

An example is posted on the page here: Example Demo

Dinesh Kanivu
  • 2,551
  • 1
  • 23
  • 55
Charlie
  • 31
  • 1