Flash Fill

re: Flash Fill

jporkka — Fri, 24 Aug 2012 04:49:56 GMT

FlashFill looks really interesting, but this touches on something related that I’ve run into a bunch of times.

In the other blog post you reference you have:

1. In P2 enter this formula: =DATE(MID(E2,1,4),MID(E2,5,2),MID(E2,7,2))

2. Fill that formula down across the rows of data (P2:P10 in this case)

But, what I’ve always wanted to be able to do is basically to create a user-defined function (but not vb-script).

So for the above, I’d write define a function like so:

define MyFn(cell) DATE(MID(cell,1,4),MID(cell, 5,2),MID(cell, 7,2))

Then in P2:P10 I’d fill with “=MyFn(E2)”

The closest I’ve come to this is to define a table and have excel automatically fill-down when you edit the top row.

re: Flash Fill

Jim King — Mon, 20 Aug 2012 06:25:26 GMT

Excel Flash Fill is a brilliant time saver, i wish i have it years ago. It automatically separates data pasted from sites into separate columns. Really cool!

re: Flash Fill

Chad Rothschiller — Fri, 10 Aug 2012 16:30:16 GMT

Yes Frederic, the more uniform your data, the better. One trick to help isolate where the Flash Fill rule will be applied is to explicitly select only the range you want filled in (which would include a couple of good examples you provide). So if you have 40 rows of data, and after sorting it looks like the first 10 are similar in shape/structure, then provide an example, then select those first 10 rows, and hit CTRL + E (or Data tab / Flash Fill) to fill only the first 10 rows. Repeat for the other sets of similar data.

Another option would be to use a filter to narrow down to only the data you want to Flash Fill (weed out the rows you don't want Flash Fill to operate on) - - the feature will only work on the non-filtered (visible) data.

re: Flash Fill

Chad Rothschiller — Fri, 10 Aug 2012 16:19:21 GMT

Thanks Bill. Yes, date formatting is really powerful in Excel, though ultimately we want Flash Fill to do all the heavy lifting for you. Here's a pointer to an article on custom number formatting: office.microsoft.com/.../number-format-codes-HP005198679.aspx

re: Flash Fill

Chad Rothschiller — Fri, 10 Aug 2012 16:10:47 GMT

The selection doesn't matter when providing the second example. That revsion will be added to the example set, a new rule (or set of rules) will be re-created from the new example set and applied over the entire original Flash Filled range.

Yes, we were thinking people might want to fill those a certain color, or you could also tab through them to review them (though 100 is probably too many for that). Note that you get a similar option for selecting the changed cells, and that's also available in the status bar at the bottom.

Finally, another way to review your results is to sort the Flash Filled range (sorting is one of the few operations that doesn't automatically dismiss the Flash Filled range from being dynamic/active) and look at the results that way.

re: Flash Fill

Bill Jelen — Fri, 10 Aug 2012 11:26:45 GMT

Chad - another tip. You noted that Flash Fill doesn’t know that 1 might correspond to “January”, 2 to “February”, and so on. However, if you convert the column of dates to use the Long Date format, you can get Flash Fill to give you "January", "February" and so on.

re: Flash Fill

WorkerThread — Fri, 10 Aug 2012 10:07:59 GMT

Very interesting post and it prompted me to try the feature out alongside the new WebService() function. I've blogged about my experiments here

workerthread.wordpress.com/.../excel-2013-preview-webservice-function-and-flash-fill

re: Flash Fill

Frédéric LE GUEN — Fri, 10 Aug 2012 07:25:24 GMT

When your list of data is complex and the Flash Fill is not able to "understand" your rule, it's better to sort your list before to use the Flash Fill. This tip solves a lot of problems ;)

re: Flash Fill

Bill Jelen — Thu, 09 Aug 2012 23:18:06 GMT

Chad - this is a great feature. Thanks.

Say I have 2000 cells and I flash Fill, You get 95% of them, but can not figure out 5% of them and leave them blank. There is a little UI dropdown that pops up where I can select the 100 blank cells. Does selecting just the missing cells help when providing the second rule, or were you figuring we might want to change the fill color in this case?

re: Flash Fill:

Diego M. Oppenheimer — Thu, 09 Aug 2012 07:50:08 GMT

You can get it here www.microsoft.com/.../try-office-preview