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: 5

  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
  3. This mistake can be brought about by another Security/Unknown programming or notwithstanding missing establishment documents when the item is being refreshed or moved up to a more up to date form.
    Anybody can Solve Norton Error 8504, 104 on Windows Computer by following straightforward advances.
    1. Run Norton Remove and Reinstall Tool
    This is the most significant Troubleshooting step and is in all likelihood going to explain all Norton Errors in the primary endeavor.
    On the off chance that you are utilizing Norton Family introduced, we counsel that
    you uninstall it before you run the Norton Remove and Reinstall device from Control Panel.

    Download the Norton Remove and Reinstall device.
    Spare the record on the work area and snap on NRnR symbol.
    On certain programs, the record is consequently spared to its default area which is Downloads or Documents.
    Concur with Terms and Follow every one of the Instructions to finish the Installation.
    Snap on Advanced Options.
    Select Remove Only and Click Remove
    Snap Restart when provoked.
    After PC Restarts, Follow the On-Screen Instructions to reinstall Norton.
    Visit for more :- Norton error 8504 and 104

    ReplyDelete