Fill in blank cells from several columns in ={ARRAYFORMULA()}

Question

I have a human-friendly sheet with sparse hierarchical data:

SEASON  | FRUIT      | LETTER 
-----------------------------
Winter  |            |
        | Lemons     |
        |            | Delta
Summer  |            |
        |            | Alpha
        |            | Beta
        | Pears      |
        |            | Gamma

(Note how Alpha and Beta don't have a FRUIT entry.)

I want to generate a new column, using ARRAYFORMULA(), to contain a full "path" to the LETTER:

SEASON  | FRUIT      | LETTER | PATH
------------------------------------
Winter  |            |        | Winter//
        | Lemons     |        | Winter/Lemons/
        |            | Delta  | Winter/Lemons/Delta
Summer  |            |        | Summer//
        |            | Alpha  | Summer//Alpha
        |            | Beta   | Summer//Beta
        | Pears      |        | Summer/Pears/
        |            | Gamma  | Summer/Pears/Gamma

Please help me to understand how to write such ARRAYFORMULA().

I'm trying approach, based on answers in Fill in blank cells in ={ARRAYFORMULA()}, but I'm stuck at resetting FRUIT to empty string for a new SEASON. I.e. this naïve implementation would yield Summer/Lemons/Alpha instead of Summer//Alpha:

={ ARRAYFORMULA(
            IFERROR(VLOOKUP(ROW(SEASON), IF(SEASON<>"", { ROW(SEASON), SEASON }), 2, 1), "")
    & "/" & IFERROR(VLOOKUP(ROW(FRUIT),  IF(FRUIT<>"",  { ROW(FRUIT), FRUIT }),   2, 1), "")
    & "/" & LETTER
) }

MattKing · Accepted Answer · 2021-09-21T20:30:36.327

Here is a sample spreadsheet created specifically to answer this question.

you will find this formula in cell E1 on a tab called Possible Solution.

=ARRAYFORMULA(IF(LEN(A:A&B:B&C:C),VLOOKUP(ROW(A:A),FILTER({ROW(A:A),A:A},LEN(A:A)),2,1)&"/"&VLOOKUP(ROW(B:B),FILTER({ROW(B:B),B:B},LEN(A:A&B:B)),2,1)&"/"&C:C,))

It uses the VLOOKUP(ROW(),FILTER(),[index],TRUE) technique to append the relevant parts of the path to one another.

Note the portion of the formula in the image which i believe was the crux of the trouble with the strategy you were trying...

Fill in blank cells from several columns in ={ARRAYFORMULA()}

1 Answers1