Introduction
I have already created two pages (Flash Fill, Flash Fill and Quick Analysis) on Flash Fill, both for Excel and for Power Query and here is another one. In this case, I am introducing even more ideas of how Flash Fill (FF) can be used very effectively. Firstly, by using FF to extract, combine, extract and combine; and secondly, I watched a video of a highly respectable Excel practitioner who tried to sell us the idea that there are times when FF is just not good enough and neither is Power Query: in that case, he said, use a formula … he demonstrated a relatively complex formula. The formula worked but I am showing here that that presenter is wrong and that FF will do exactly what we want without programming anything.
Initial Data and Outputs
Firstly, take a look at the following screenshot that shows my original data in columns A and B. Columns E to H then contain text derived from the original data:
- Initials
- Initials and City
- No Initials but Numbered City
- email address, assuming everyone is connected to the same organisation
Your imagination will encourage you to think of even more ideas than these.
In the video below (to follow tomorrow), I show you how to do some of these FF exercises but, if you have watched videos 1 and 2 in this series, you won’t need my guidance. Moreover, you could do the above in Excel and in Power Query.
Another Example where All you need is FF
Imagine, now, you are presented with the list shown on the following screenshot: names are shown as last name and first name, where we want it to say the first name and last name. In truth, FF is an absolute breeze here but I will show you the methods that our other Excel presenter dismissed first and then the jaw droppingly easy, true FF version.
Flash Fill in Power Query
I took my original list of five names, converted the range into an Excel Table and, using Flash Fill in Power Query, I set up an Additional Column using Column from Examples, from Selection. In row one, I just typed Duncan Williamson … Success.
I no longer needed the original column in this query, so I deleted it and left myself with the result you see below.
I dragged and dropped the additional names to the end of the main list in the Excel Table then refreshed the Query and it updated, again, as you can see below.
Switch Names by Formula
I took my original list of names, converted the range into an Excel Table and, using this formula, =RIGHT(A6,(LEN(A6)-FIND(“,”,A6)))&” “&LEFT(A6,FIND(“,”,A6)-1), in the next column along, got the result you see below. Success
I dragged and dropped the additional names to the end of the main list in my Excel Table and it updated, again as you see below.
My formula is =RIGHT(A6,(LEN(A6)-FIND(“,”,A6)))&” “&LEFT(A6,FIND(“,”,A6)-1)
Our other presenter offered these formulas: they work and I am making no criticism of them except that they are not needed, as you are about to see!
=REPLACE(B3,1,SEARCH(” “,B3),””)&” “&LEFT(B3,SEARCH(“,”,B3)-1)
and
=RIGHT(B3,LEN(B3)-SEARCH(” “,B3))&” “&LEFT(B3,SEARCH(“,”,B3)-1)
Just use FF
Here is the jaw dropping situation. Take the list of names and to the right of that list, where it says Williamson, Duncan in A6, type Duncan Williamson in, say C6. Then start typing Albert Smith in C7 and before you have typed the l in Albert, FF in Excel will have suggested the pattern for you and it will have completed every cell in your range. No need for an Excel Table, no need for Power Query, No need for any formulas.
More than that, as you drag the additional names that start with Brandon, Mann, to the bottom of the original list, just press Ctrl+E and your FF list will fill down and. Job done! Success. For the final time, no need for an Excel Table, no need for Power Query, No need for any formulas.
Conclusions
Flash Fill in Excel and in Power Query is brilliant albeit not perfect. It is a massive time and effort saver, even when a list needs to be trained a little, it is still brilliant. Of course, there are those rare cases when it can find no pattern to simulate and when that happens, we kick the cat and shout at the children!
Download my Excel file: flash_fill_another_three_techniques_trainer
Watch out for my video which should be uploaded tomorrow, 2nd August 2019
Duncan Williamson
1st August 2019
Excel with Excel Master 