Search code examples
if-statementgoogle-sheetsvlookuparray-formulasnested-if

Fill in blank cells in ={ARRAYFORMULA()}

I have a human-friendly sheet with sparse data:

PART  | FRUIT
---------------
Alpha | 
      | Apples
      | Pears
Beta  |
      | Lemons
      | Oranges

I want to create a second automatically updated machine-friendly sheet, which would have all empty cells in column PART filled:

PART  | FRUIT
---------------
Alpha | 
Alpha | Apples
Alpha | Pears
Beta  |
Beta  | Lemons
Beta  | Oranges

I am OK to have empty cells in the column FRUIT on the machine-friendly sheet. But ideally I would like such rows removed:

PART  | FRUIT
---------------
Alpha | Apples
Alpha | Pears
Beta  | Lemons
Beta  | Oranges

If I wanted to use interpolation in the machine-friendly sheet, I would rely on the MATCH trick or the FILTER paste-anywhere formula.

But I really want to avoid updating the machine-friendly sheet when I add, change or remove rows in the original sheet. (I'm OK if I will have to update it if I add new columns to the original sheet.) This means that using manual interpolation is off-limits.

Ideally on the second sheet I would type in a magic ={ARRAYFORMULA()} or a =QUERY of some kind, and then leave it alone.

={ ARRAYFORMULA(MAGIC(PART)), FRUIT }

But so far I cannot wrap my head on how to approach this. Any suggestions?

Solution

  • use in row 2:

    =ARRAYFORMULA(IF(B2:B="",, VLOOKUP(ROW(A2:A), IF(A2:A<>"", {ROW(A2:A), A2:A}), 2, 1)))

    UPDATE

    since SCAN and LAMBDA functions were added to google sheets we can do this even faster using shorter formula:

    =SCAN(, A2:A7, LAMBDA(a, b, IF(b="", a, b)))

    enter image description here

    to detect end of the range of column B we can replace A2:A7 with:

    =SCAN(, A2:INDEX(A:A, MAX(ROW(B:B)*(B:B<>""))), LAMBDA(a, b, IF(b="", a, b)))

    enter image description here

    further, we can use LET to remove blanks from B column

    =LET(r, A2:INDEX(A:A, MAX(ROW(B:B)*(B:B<>""))), 
     x, SCAN(, r, LAMBDA(a, b, IF(b="", a, b))), 
     FILTER({x, OFFSET(r,,1)}, OFFSET(r,,1)<>""))

    enter image description here