Fill in blank cells in ={ARRAYFORMULA()}

Question

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?

NB: Any edits improving terminology and visibility of this question in the search are very welcome. I dislike my original title the most, please help me to find a better one. — Alexander Gladysh

player0 player0 · Accepted Answer · 2020-11-25T11:42:14

use in row 2:

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

Fill in blank cells in ={ARRAYFORMULA()}

1 Answers