3

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?

player0
  • 124,011
  • 12
  • 67
  • 124
Alexander Gladysh
  • 39,865
  • 32
  • 103
  • 160
  • 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 Nov 25 '20 at 12:14

1 Answers1

5

use in row 2:

=ARRAYFORMULA(IF(B2:B="",, VLOOKUP(ROW(A2:A), IF(A2:A<>"", {ROW(A2:A), A2:A}), 2, 1)))
player0
  • 124,011
  • 12
  • 67
  • 124
  • That works! But is there a way to pack everything in a single formula for extra neatness points? On my second sheet I have `={ PARTS, FRUITS }` in A1 (I use named ranges referring to the original sheet) and your formula in C2. Can I pack both in A1 somehow? – Alexander Gladysh Nov 25 '20 at 12:13
  • can you share a copy of your sheet? – player0 Nov 25 '20 at 16:27
  • 2
    This worked for me (I put it in A1 of the machine-readable sheet) `=FILTER({ ARRAYFORMULA(IF(FRUITS="",, VLOOKUP(ROW(PARTS), IF(PARTS<>"", {ROW(PARTS), PARTS}), 2, 1))), FRUITS}, FRUITS<>"")` – Alexander Gladysh Nov 25 '20 at 20:18