Google-sheets – Return value in column A in preceding cells in column B using an array formula

formulasgoogle sheets

How do you fill cells in column B (using an array formula) that are preceding the cell in column A with the value of column A only when cell in column C is not blank?

In other words, after column C is filled with text values, in the next empty row I enter a text value in column A, then I want the preceding cells in column B where column C is not empty to be filled with this manually entered value in column A, like this:

A   B   C
    d   notblank
    d   notblank
    d   notblank
d
    a   notblank
    a   notblank
    a   notblank
    a   notblank
a
    e   notblank
    e   notblank
e

Best Answer

Assuming the data starts in row 1, try in B1:

=ArrayFormula(IF(LEN(A:A)=0,VLOOKUP(ROW(A:A),{{0;FILTER(ROW(A:A),LEN(C:C)=0)},{FILTER(A:A,LEN(C:C)=0);""}},2),))

Out of interest, the formula simplifies a bit if you are retrieving from cells below:

=ArrayFormula(IF(LEN(A:A)=0,VLOOKUP(ROW(A:A),FILTER({ROW(A:A),A:A},LEN(C:C)=0),2),))

Short answer

A formula that does the required in the question

=JOIN(",",
      QUERY(
            {ArrayFormula({A1:A6}&{B1:B6}),B1:B6},
            "Select Col1 Where Col2<>''"
      )
 )

Explanation

The above formula has nested three functions, use the matrix handling feature of Google Sheets and the concatenate operator.

ArrayFormula({A1:A6}&{B1:B6}) : Concatenates the cell values of each row.
{ArrayFormula({A1:A6}&{B1:B6}),B1:B6} : Creates a range with two columns. The second column will be used for filtering.
QUERY is used to do the filtering.
JOIN is used to create the string separating the elements with a comma.

Google-sheets – Applying a Formula to All Cells in a Column (Even New Cells)

Try:

=ArrayFormula(if(len(H2:H)*len(G2:G), iferror(H2:H/G2:G, 0),))

The formula will output results if there are entries in col H AND col G. See if that works for you ?

Best Answer

Related Solutions

Google-sheets – Concatenating two columns using only filled cells in one of them

Short answer

Explanation

Google-sheets – Applying a Formula to All Cells in a Column (Even New Cells)

Related Topic