Today's #TipTuesday post is a Microsoft Excel tip, instead of the usual Dynamics GP tips! I do have a tie-in to Dynamics GP on where users could use this in a pinch in GP from an exported Smartlist, at the end of this post!
Flash Fill came out in Office 2013 (as far as I can confirm) and I must say, there are a number of different instances where I've found this extremely useful in a pinch to pull data out of some cell that contains more than what I need.
My scenario
I was working on an easy way to extract data from customer EFT remittances to import the payments into our CIS system (not Dynamics GP in this case, but that's not relevant here anyway). The format of the remittances is all over the map, and it's virtually impossible to enforce with customers a consistent format to give us their remittance info as everyone's ERP systems are different and have different capabilities. So, we've been extracting data from many of the sources using Flash Fill. Many times copying and pasting from an email or PDF attachment results in a blob of text that pastes into one column in Excel without any delimiters. Every customer's remittance differs so even having an easy template that fits all scenarios is impossible.
Enter "flash fill" to the rescue!
Example 1: Flash Fill starts automatically
In this example, sometimes when typing values in a column, Excel will be trying to determine if what has been typed is "in" the adjacent cells, and will show a flash fill ghosting/box.
Here I have a blob of text in one column which contains 2 things we want to extract: one of the sections is a reference for us to match a payment to an invoice and one is the dollar amount paid. In the yellow highlighting above, as soon as I started typing the 2nd line of data that exists in my adjacent cell, a greyed-out flash fill section appears, showing me what would happen if I simply stop typing and hit the Enter key to accept it. In my case, this extracted the data correctly. At times, it won't *quite* understand the pattern, so the way to handle that is to keep typing. 2 or 3 cells is usually sufficient for it to pick patterns up.
If there are discrepancies further down the list (if there are a large number of rows), Flash Fill will try to auto-correct other rows based on a re-typed correction in the middle of what it already "flash filled". Sometimes that's helpful, sometimes it's not! Most of the time, this is a time saver though and a great little feature.
Example 2: Flash Fill from the toolbar
There are other times where I'm typing and expected Flash Fill to "kick in" and it doesn't, for one reason or another. In those cases, there is a way to trigger it from the toolbar. In my second example, the second column is the amount field and I typed in a couple of values and then highlighted both the example and the rest of the area I want to fill and clicked on Flash Fill on the Data tab of the toolbar.
(Before Flash Fill)
(After Flash Fill)
Applying this to exported Smartlists from Dynamics GP
One way to use this with exported Smartlists would be a simple extraction from a field with multiple values, like a G/L Account. Or perhaps there is a numbering/naming convention on something like job numbers where part of it has meaning and a quick filter is required. I know in our environment, we have different parts of job/project numbers that have meaning and extraction isn't easy nor is that in another field that could be used. This could work for those situations. Using G/L Accounts is a bad example because ultimately if a user needed to do this regularly, they should add the segment column to the Smartlist in the first place so it's there, but in a pinch, this will serve as my example.
Here's was a quick example where I extracted the main segment from the Fabrikam Ltd. chart of accounts. Export and flash fill in seconds, easy peasy!
