Is there an arrayformula to fill all blank cells in a column with the content from the cell above?

Question

In the following Google sheet:

  A          B
 ------------------------
1| Tom     | something  |
2|         | something  |
3| John    | something  |
4| Lana    | something  |
5|         | something  |
6|         | something  |
7| Jason   | something  |
 ------------------------

I want an array formula applied to column A which will automatically fill any blanks with the last data in the cell above (or above that, if there are multiple blanks).

The result should look like this:

  A          B
 ------------------------
1| Tom     | something  |
2| Tom     | something  |
3| John    | something  |
4| Lana    | something  |
5| Lana    | something  |
6| Lana    | something  |
7| Jason   | something  |
 ------------------------

As the sheet can be massive, applying the formula to each cell is not practical.

If I paste the formula =if(ISBLANK(A2), A1) into the blanks manually it will fill them, but dragging said formula over a whole column will not work.

Answer 1

The simplest way to achieve this goal would be to create a new column, and use the formula =IF(ISBLANK($A2), $B1, $A2) from B2 down. I think that what you meant was that you can't simply replace the column in place, correct? This would solve it in that case, but it is not a single arrayformula. If you are worried about inserting the formula into all rows, try selecting the first cell, CTRL+SHIFT+Down arrow key to select the full range, and CTRL+ENTER to paste the formula.

If you truly need an arrayformula, one would hope that =ArrayFormula(IF(ISBLANK(A2:A), B1:B, A2:A)) or the like would work, but unfortunately Sheets does not iterate over the results of this formula so that it does. Other functions (such as vlookup) do work properly, but I'm not sure how to compose a formula to achieve what you're looking to do here using those.

Answer 2

Yes you can do it with vlookup, by creating an array that includes a row number and using the inexact lookup form of vlookup so that it finds the previous row that contains text:

=ArrayFormula(query({vlookup(row(A:A),{if(A:A<>"",row(A:A)),A:A},2),B:B},"select Col1,Col2 where Col2 is not null"))

Answer 3

To answer your Q in one word "NO" (though slightly simplistic).

An approach that might suit is to filter ColumnA to select (Blanks) only and in the first blank cell (in your case A2) enter:

=A1

where the 1 is the number of the row immediately above the formula cell.

Copy down to suit and Edit > Copy > Edit > Paste special > Paste values only, then take off the filter.

Answer 4

That solution might help from this source:

https://infoinspired.com/google-docs/spreadsheet/fill-blank-cells-with-the-values-above/

The advantage it doesn't add extra rows at the tail end of the column.

= scan(0, indirect("B2:B" & reduce(0, B: B, lambda(a, v, (
  if (v = "", v & a, row(v)))))), lambda(a, v,
  if (v = "", a, v)))