Replace blanks with nearest non-blank cell in Google Sheets query

Question

Given the following table:

Col1   Col2
1      Lorem
       Ipsum
2      Dolor
3      Sit
       Amet
       Consecutor

I am trying to query this to another sheet with the values of Col1 replaced with the previous non-blank cell in the column, e.g. the result would look like this:

Col1   Col2
1      Lorem
1      Ipsum
2      Dolor
3      Sit
3      Amet
3      Consecutor

I've tried so many things: query() ArrayFormula() filter() row() index() lookup(). I'm at my wit's end. Can anyone help with formula or otherwise technique for doing this? I can't do anything in the target sheet because the source sheet can be updated at any time.

player0 player0 · Accepted Answer · 2019-06-25T00:28:31

try this simple formula in Col3:

=ARRAYFORMULA(QUERY({VLOOKUP(ROW(A:A), 
 {IF(A:A<>"", ROW(A:A)), A:A}, 2), B:B}, 
 "select Col1,Col2 where Col2 !=''"))

Replace blanks with nearest non-blank cell in Google Sheets query

1 Answers