Flash Fill and Ambiguous Data

November 16, 2022 - by Bill Jelen

Problem: How will Flash Fill get this right? Even I wouldn’t know what to fill in next?

Strategy: Before invoking Flash Fill with Ctrl+E, provide enough examples to establish the pattern. If you invoke Flash Fill from B3, you will get the prefix instead of the suffix. By filling in one more example and running Flash Fill from I4, you will get the suffix.

Watch out for Flash Fill examples that could be ambiguous. The original column has two letters, a dash, and two letters. Most of the codes are EP-ZA, ZA-BC, AD-VU, but the first example it an annoying IT-IT. When you give Flash Fill an example of IT, how will flash fill know if you want the part before the dash or after? (The heading says Suffix, but Flash Fill is not smart enough to understand the heading). Rather than Flash Fill from just the IT example, type a second example, with ZA from EP-ZA. Then, Flahs Fill from row 4 and it will work. — Figure 818. Give Flash Fill enough examples.

This article is an excerpt from Power Excel With MrExcel

Title photo by Hermes Rivera on Unsplash