SPACs (Special Purpose Acquisition Companies) or "blank check" companies have been in the news recently, so I used some real SPAC data for this episode. Your spreadsheet has empty cells in column A, and these empty cells should be filled with values. Your task is to fill values down up until you find another cell with a value, at which point you need to fill that value down. This episode walks through how to do this programmatically with a script in Google Apps Script (for Google Sheets) and VBA (for Excel). This is the Google Sheet associated with the episode. The Google App Script is here and VBA script is here. See a quick example of what the issue is in the gif below and how the script "fills in" the values for you.

See the video below if you want to jump straight to the tutorial:

https://www.youtube.com/watch?v=t-32QkyjKVE&feature=youtu.be

Why is this data structure a problem?

You've inherited a spreadsheet and the data structure looks like this:

It's a list of data but there are empty cells in column A. This is usually a category or dimension in your data set that needs to be "filled down" so that the data set is complete. In the Google Sheet, each row represents one person that is associated with a given SPAC, but the SPAC Ticker column is incomplete. You'll usually get this type of data structure through the following:

* Data was manually created by someone who didn't fill down the values in column A since they thought it was a "category" * You are working a data set that originally came from a PivotTable but you only have the "values" from the PivotTable, not the PivotTable itself

This data structure is a problem because if you want to do any type of analysis on this data, it will be extremely difficult since you have missing values in column A. Sorting, filtering, and PivotTables are all out of the question if your data set looks like that screenshot.

Solving this with keyboard shortcuts

Totally doable for this Google Sheet. This is what you could do:

All I'm doing above is the following (on PC):

* SHIFT+CONTROL+DOWN ARROW - Select all the empty cells from the current cell with a value up until the next cell with a value* SHIFT+UP ARROW - Reduce the selection by one row* CONTROL+D - Fill the value from the first cell in the selection down* CONTROL+DOWN ARROW - Skip to the next value that needs to be filled down

The obvious tradeoff here is time vs. human error. Every time I have to do this task on a spreadsheet, I think about whether it was worth filling the values down "manually" using keyboard shortcuts or using a VBA script (in Excel) to do this programatically. It really depends on the number of rows. For the example SPAC Google Sheet, doing this with keyboard shortcuts takes 10 seconds tops. If this spreadsheet was 1,000,000 rows, then we have a problem.

Don't worry, I got you. Here's the script you can use to do this programmatically.

Using Google Apps Script in Google Sheets

First off, here's the script you can use for Google Sheets (gist