Everyone! We've moved to WordPress.

The Excel Lab: Soundex Search

Every once in a while, I have a spreadsheet that sits in my ‘lab’ for way too long. At a certain point, I have to admit that I’m never going to finish it completely or do a full blog post on it. I’ve decided just to release one such spreadsheet as is. Perhaps you will be the one to carry it forward? (or, write to tell me I’ve wasted my time?)

Soundex Search

I while ago, I read about a phonetic algorithm called Soundex. Soundex is useful (well, there’s some argument about that) for searching through a list of surnames while compensating for common misspellings in those names. Using a dummy dataset and UDF developed by Richard Yanco, I created the following:

Soundex

As the animation demonstrates, you can type in your search term into the ActiveX textbox and both the listbox and graph will automatically update to show you relevant search results and corresponding values. I’ve added some additional search functionality beyond Soundex as well. For each name in the dataset, I’ve assigned a random value to plot.

I’m rather unconvinced of the usefulness of this. I’ll put it you to see if you have any luck with it. For what it’s worth, the mechanism is formula driven. I didn’t use VBA except to set the original Soundex values for each surname (which, you could also probably do without VBA if you were so inclined.)

Download the source file:

soundex.xlsm

Comments: 3

  1. I tried to use various soundex versions (md soundex, org soundex and soundex2) and a couple of other phonetic algos (leveshinte, dbl met) in an Excel project where we were trying to match names and movie/TV show titles from different lists.

    It was an interesting exercise, but in the final analysis, it didn't improve the number or quality of matches, so I dumped it.

    Tom Scott - www.jerts.com

    ReplyDelete
    Replies
    1. I totally understand. I'm unconvinced that SoundEx is really all that good at matching. But like you said, it's an interesting exercise if nothing else.

      Delete
  2. The article of this blog very informative and useful also. It resolves many problems of mine.
    I loved it. Thanks for this marvelous article..excel vba consultant

    ReplyDelete