excel add-in Archives - Page 3 of 3

09Nov 2013 by Charlie Nichols No Comments

Fuzzy Search in Excel with the Fuzzy Find and Replace Tool

200 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Fuzzy Search and Replace in Excel You can find similar entries from a list or table in Excel by doing a fuzzy search in Excel. This gives you a way to consider the following to effectively be the same. John Smith Jonathan Smith Smith, John Smith, Jonathan John Q. Smith John Smyth J. Smith Johnny Smith … etc. So if you just want to look for “John Smith” and simply find those entries that are pretty close to that. You want to do a fuzzy text search (not just a wildcard search at the beginning or end of a string). This post describes how to use the Fuzzy Find and Replace feature of the Excel PowerUps add-in for Excel to find those approximate matches. Fuzzy text search in…

23Oct 2013 by Charlie Nichols 2 Comments

Fuzzy VLOOKUP in Excel

300 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] pwrVLOOKUP. Just like VLOOKUP, only Better. You can do a VLOOKUP operation in Excel and get a fuzzy match instead of being limited to the exact match function built into Excel. To do a fuzzy VLOOKUP you need to use the pwrVLOOKUP function that is part of the Excel PowerUps Premium Suite add-in which is available as a free download on this site. Usage of the pwrVLOOKUP function is very similar to the built-in VLOOKUP function. The first 3 parameters are the same (lookup value, lookup range, and column offset). The difference starts in the fourth parameter. In pwrVLOOKUP, the fourth parameter is a strength-of-match value. It is a number between 0 and 100. If you do not provide this value, an exact match (strenth-of-match = 100) is…

30Sep 2013 by Charlie Nichols No Comments

How to Generate Random Demographic Data

300 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] How to Generate Random Demographic Data Why Generate Random Demographic Data? There are several reasons I can think of that you would want to or need to generate random demographic data. They primarily center on testing scenarios. Due to the nature of the data, it may be unfeasible for you to access real data to test your application or process. You wouldn’t want to risk exposing PII (Personally Identifiable Information) or other sensitive information. Also, some of the data you may need to test would be restricted or protected under the Sarbanes-Oxley Act (SOX) and/or the Health Insurance Portability and Accountability Act (HIPAA). There are complex regulations that you don’t want to run afoul of, but most of all you want to protect your customer’s sensitive data –…

06Apr 2013 by Charlie Nichols No Comments

An Excel function to calculate your dog’s or cat’s age

400 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Dog years, explained Do you think one person year equals seven dog years. I did, until doing some research. I wanted to add a “fun” conversion factor for a conversion function in an Excel add-in I've created. What I learned is that the seven year equivalent value is a pretty poor approximation of a dog’s equivalent age. Dogs do mature more quickly than people, but when they are young they mature at an even faster rate (relative to people) than they do when they are older. This post describes some capability you only get by installing an add-in for Excel. The add-in used here is fully functional and free during its trial period - so you'd have plenty of time to try this out. The add-in is called PowerUps…