Google Sheets Fill Down with Formula

Question

I have a very hard problem to solve, which must be completed with a formula (not a script). Basically, the Raw input column needs to be dynamically filled down until it hits the next piece of text.

Here's an example file with includes the expected output. https://docs.google.com/spreadsheets/d/1ibqCvY39NlhCRWsbBdxKITUUpVpp9wXdEz44T-pHDY0/

Is it even possible to achieve?

Thanks

Answer 1

This will work based on your ask, assuming that A2 is never blank, place this in the first row of data (not header):

=ArrayFormula(IF(A2:A<>"", A2:A, B1:B))

It checks to see if there is a value in column A, if there is, it fills that column, if not, it copies the cell above.

Answer 2

Delete everything in Column B (including the header) and place the following formula in B1:

=ArrayFormula({"Header";VLOOKUP(FILTER(ROW(A2:A),ROW(A2:A)<=MAX(FILTER(ROW(A2:A),A2:A<>""))),FILTER({ROW(A2:A),A2:A},A2:A<>""),2,TRUE)})

Here is a basic explanation of how this formula works:

A virtual array is created between the curly brackets { }; this virtual array contains a header and all results. You can change the header name to whatever you like.

VLOOKUP looks up every row number that is less than or equal to the highest row number that contains text in A2:A. Each of these qualifying rows is looked up in a second array that contains only the row numbers and Column-A data from non-blank rows, returning the data itself. Since rows are in perfect ascending order and the last parameter of VLOOKUP is set to TRUE, all blank rows in the first array will "fall backward" to find the most recent row that did have something in Column A.