If you spend much time in Excel, you have probably found yourself doing some repetitive tasks to clean up a column of data. Or maybe instead of doing it manually, you might craft some formulas to do it for you. If you’re just doing a one-off cleanup, make sure you consider the Flash Fill feature in Excel. I recently used this in a demo to my team at work and many people in the room were surprised by it so I figured there were probably a few of you reading this who could benefit from it as well.
The basic idea is that you have one or more input columns and an output column. You provide an example or two of what the output should be and then you click the Flash Fill button. A magical wizard in your computer figures out the pattern and repeats it for the rest of the column. It really does feel like magic the first couple times you use it. Sometimes the algorithm gets it wrong for a few cells so you can just make a correction and it will update it’s algorithm and redo any cells that need it.
This video has a bunch of great examples:
If that’s not enough for you, check out prose-playground.cloudapp.net. The tech behind Flash Fill is open sourced out of Microsoft Research and this is one of the sample sites they have created. If you want to use the SDK, you can get it from GitHub.