Needing Assistance with a fill formula

New Contributor

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 

As variant that's create the helper column with formula as

image.png

and double click on the dot at right bottom of D2 to fill it down.

@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.

 

 

 

 

@Sergei Baklan Ah! you chose the easy way

OMG - THANK YOU THANK YOU THANK YOU - IT WORKED AS I WAS HOPING!!!!
Appreciate all the help!

@Riny_van_Eekelen 

If data is imported from CSV it'll be more correct to do all with Power Query, the only job will be to press Refresh from time to time instead of copy/pasting data from CSV and play with formula on result.

@Sergei Baklan I know, but my thought was that if a user is not at all familiar with PQ, the copy and paste option could work just fine. It wouldn't require explaining how to connect to a file. But yes, once the user knows how to do it, that's the way to go.