Forum Discussion
Needing Assistance with a fill formula
I have a report that is missing email data that I need to fill. Attached is the sample. I need the email to fill all the way down to the next email, then fill with the next email until the next and so on. This is how the report came to me with a - (dash) instead of the email. I have about 12,000 fields to fill in.
I am running on a PC, HP, Windows 10, v2004 and the report came in a .csv format, which I saved as an .xlsx format. Microsoft 365 App for Business.
Please let me know if you have suggestions for a fill formula to allow me to fill all the dashes with the appropriate emails.
Thanks
Mitchelle
8 Replies
MHall1966 Don't find an attachment. Consider using Power Query (or Get & Transform as it is called on newer versions on the Data Ribbon). Power Query comes with a nice feature that allows you to fill down something to empty cells below or above. Not very straight-forward if you've never used it, though, as you need to clean-up/prepare the data a little bit as well. But since you have 12000 rows it may be the most efficient way to go.
Upload a file with say, 100 rows and some fake data. But with columns and headers exactly as in your real file. Fairly easy to set up something that you can use without knowing much about Power Query.
Thanks, I shortened it to 50 rows for this example Riny_van_Eekelen
MHall1966 Attaching the file with a PQ solution.
What I did is convert the sampla data to a structured Excel table (Ctrl-T). It's called "Table1". Not very original. Then I connected to that table with PQ, cleaned up a few things and filled down the e-mail addresses.
The end result is in the tab "Table1". What you need to do next is to copy your real data directly below the blue table and delete the first (example rows).
Then, on the Data ribbon select Refresh All and the Table1 tab shall be updated in a matter of seconds.
