Google-sheets – Convert ARRAYFORMULA output from 2D to 1D range

google sheetsgoogle-sheets-arrayformula

tl;dr

How to create an 1D range from a 2D range output of an ARRAYFORMULA.

Intro

I have a Google Sheet that uses a very complex ARRAYFORMULA to perform data validation on about fift cells in a row. Each cell returns a result, most will be blank but one of the cells may return a string or a number. I need that returned value, AND I need the ARRAYFORMULA to also operate on a huge number of rows.

This seems like a question asked by many people on the web, but with no answer anywhere (that I can find).

In the below I have replaces the complex formula with a simpler one and a simple set of test data, and will go through the process I've attempted step by step.

ARRAYFORMULA, one row, output to multiple cells

In E2, the following formula: =ARRAYFORMULA(if(A2=B2:D2,B2:D2,""))
In E3, the following formula: =ARRAYFORMULA(if(A3=B3:D3,B3:D3,""))
In E4, the following formula: =ARRAYFORMULA(if(A4=B4:D4,B4:D4,""))

┌───────┬───────┬───────┬───────┬────────┬────────────┬───────┬───────┐
│       │   A   │   B   │   C   │    D   │      E     │   F   │   G   │
├───────┼───────┼───────┼───────┼────────┼────────────┼───────┼───────┤
│   1   │ Input │ Val1  │  Val2 │  Val3  │Formula/out1│ out2  │ out3  │
├───────┼───────┼───────┼───────┼────────┼────────────┼───────┼───────┤
│   2   │Test00 │Test00 │Test01 │Test02  │   Test00   │       │       │
├───────┼───────┼───────┼───────┼────────┼────────────┼───────┼───────┤
│   3   │Foo    │Test10 │Test11 │Test12  │            │       │       │
├───────┼───────┼───────┼───────┼────────┼────────────┼───────┼───────┤
│   4   │Test22 │Test20 │Test21 │Test22  │            │       │Test22 │
└───────┴───────┴───────┴───────┴────────┴────────────┴───────┴───────┘

As can be seen from the above, the input 'Test00' were found at 0,0 in the input range, and therefore placed at E2, 0,0 in the output range. Similar 'Test22' were found at 0,2 in the input range, and therefore placed at G4, 0,2 in the output range. 'Foo' were not found, so output for that row remains blank.

ARRAYFORMULA, one row, output to single cell

Given that I know there will maximum be one value per row, and that all other values will be blanks, I can use CONCATENATE to ensure the value is returned on the E column:

In E2, the following formula: =ARRAYFORMULA(CONCATENATE(if(A2=B2:D2,B2:D2,"")))

In E3, the following formula: =ARRAYFORMULA(CONCATENATE(if(A3=B3:D3,B3:D3,"")))

In E4, the following formula: =ARRAYFORMULA(CONCATENATE(if(A4=B4:D4,B4:D4,"")))

┌───────┬───────┬───────┬───────┬────────┬────────────┬───────┬───────┐
│       │   A   │   B   │   C   │    D   │      E     │   F   │   G   │
├───────┼───────┼───────┼───────┼────────┼────────────┼───────┼───────┤
│   1   │ Input │ Val1  │  Val2 │  Val3  │Formula/out1│       │       │
├───────┼───────┼───────┼───────┼────────┼────────────┼───────┼───────┤
│   2   │Test00 │Test00 │Test01 │Test02  │   Test00   │       │       │
├───────┼───────┼───────┼───────┼────────┼────────────┼───────┼───────┤
│   3   │Foo    │Test10 │Test11 │Test12  │            │       │       │
├───────┼───────┼───────┼───────┼────────┼────────────┼───────┼───────┤
│   4   │Test22 │Test20 │Test21 │Test22  │   Test22   │       │       │
└───────┴───────┴───────┴───────┴────────┴────────────┴───────┴───────┘

As can be seen from the above, the output is now only in the E column – just as I wanted. However, I still have to copy the formula to every row I need this performed on, and that's a lot of rows!

ARRAYFORMULA, multiple rows, output to 2D range

In E2, the following formula: =ARRAYFORMULA(if(A2:A4=B2:D4,B2:D4,""))

┌───────┬───────┬───────┬───────┬────────┬────────────┬───────┬───────┐
│       │   A   │   B   │   C   │    D   │      E     │   F   │   G   │
├───────┼───────┼───────┼───────┼────────┼────────────┼───────┼───────┤
│   1   │ Input │ Val1  │  Val2 │  Val3  │Formula/out1│ out2  │ out3  │
├───────┼───────┼───────┼───────┼────────┼────────────┼───────┼───────┤
│   2   │Test00 │Test00 │Test01 │Test02  │   Test00   │       │       │
├───────┼───────┼───────┼───────┼────────┼────────────┼───────┼───────┤
│   3   │Foo    │Test10 │Test11 │Test12  │            │       │       │
├───────┼───────┼───────┼───────┼────────┼────────────┼───────┼───────┤
│   4   │Test22 │Test20 │Test21 │Test22  │            │       │Test22 │
└───────┴───────┴───────┴───────┴────────┴────────────┴───────┴───────┘

As can be seen from the above, the single formula perfectly performs the role of the three formulas given in the first test, but I'm back to the problem that the output is a 2D range, instead of just a 1D range, with output only to the E column.

ARRAYFORMULA, multiple rows, output to 1D range

This is where I run into problems. The CONCATENATE trick used above does not work on 2D ranges, as all the results is output into only one cell (E2), such as Test00Test22. 🙁

What I'm looking for is something that for each row in a range returns the first non-blank value.

A variation of the typical approach to search for the first non-blank in a range is this formula: =Arrayformula(ifna(INDEX(E2:H2,MATCH(FALSE,E2:H2="",0)),"")). If inserted into G2 (and then copied from G2 to G3 and G4), the formula will correctly extract the first non-blank value of the range.

But how can I combine these two formulas?
Also, given that a range is provided twice in the formula to find the first non-blank cell, it occur to be that I will have to include the original ArrayFormula twice in the combined formula, which is something I would like to avoid.

What other alternatives do I have to convert the 2D range from the Arrayformula into an 1D range?

Best Answer

Ended up answering my own question...

After a lot of research, trail and especially errors, I were finally able to come up with a generic formula that would allow me to insert any ARRAYFORMULA construct (in fact any function that output a range) into another ARRAYFORMULA and provide a separate output for each row.

={"Header";TRANSPOSE(ARRAYFORMULA(TRIM(QUERY(TRANSPOSE({ INSERT_ARRAYFORMULA }),,2^99))))}

Instead of CONCATENATE, it uses QUERY to perform the concatenation.

To use it in my example, insert the following formula in E1:
={"Formula/out";TRANSPOSE(ARRAYFORMULA(TRIM(QUERY(TRANSPOSE({ ARRAYFORMULA(if(A2:A4=B2:D4,B2:D4,"")) }),,2^99))))}

The result:

┌───────┬───────┬───────┬───────┬────────┬────────────┬───────┬───────┐
│       │   A   │   B   │   C   │    D   │      E     │   F   │   G   │
├───────┼───────┼───────┼───────┼────────┼────────────┼───────┼───────┤
│   1   │ Input │ Val1  │  Val2 │  Val3  │Formula/out │       │       │
├───────┼───────┼───────┼───────┼────────┼────────────┼───────┼───────┤
│   2   │Test00 │Test00 │Test01 │Test02  │   Test00   │       │       │ Test string.
├───────┼───────┼───────┼───────┼────────┼────────────┼───────┼───────┤
│   3   │Foo    │Test10 │Test11 │Test12  │            │       │       │ Test wrong input
├───────┼───────┼───────┼───────┼────────┼────────────┼───────┼───────┤
│   4   │Test22 │Test20 │Test21 │Test22  │   Test22   │       │       │ Test string
├───────┼───────┼───────┼───────┼────────┼────────────┼───────┼───────┤
│   5   │     6 │      2│     6 │     10 │      6     │       │       │ Test numbers
├───────┼───────┼───────┼───────┼────────┼────────────┼───────┼───────┤
│   6   │       │      2│     6 │     10 │            │       │       │ Test no Input
└───────┴───────┴───────┴───────┴────────┴────────────┴───────┴───────┘

Main point

You can't filter by the sum within the query that forms that sum. But you can run another query on the output of the first one. Like this:

=query(query(....), "select * where Col2 <> 0")

Other remarks

I don't see the need for index command here.
Instead of A2:B1000, use A2:B to capture all rows to the bottom of the sheet.
It is better to include the header row in the queried range, designating it as a header with the third parameter (number of queried rows). That is, query(A1:B, "select...", 1) is preferable to query(A2:B, "select..."). When you don't provide the third parameter, the command guesses whether the top row is header or data; and it will sometimes guess wrong.
I don't think you need "and B is not null", as null values will be ignored by the sum anyway.

So, my version of the command is

=query(query(A1:B, "select A, sum(B) where A is not null group by A order by sum(B) desc", 1), "select * where Col2 <> 0", 1)

Google-sheets – Arrayformula+VLOOKUP find value of non-empty reference (callback/recursion problem)

Formula

=ArrayFormula({ 'Form Responses 4'!A2:B5, MMULT( VLOOKUP( VLOOKUP( ROW(2:5), {IF(LEN('Form Responses 4'!C2:C5)>0,ROW(2:5),""),'Form Responses 4'!C2:E5},{2,3,4}), {'Data Inputs'!B2:B6,'Data Inputs'!A2:A6}, 2, 0), TRANSPOSE(SIGN(COLUMN('Form Responses 4'!C1:E1)))) })

Input (Form Responses 4)

   |    A    |   B   |   C   |   D   |   E   |   
---+------------------------------------------  
 1 | Key A    Key B   Value   Value   Value
 2 | A        A       Med     Med     Med
 3 | A        A         
 4 | A        A         
 5 | B        A       Low     Med     High

Word/Value table (Data Input)

   |   A   |   B   |
---+----------------
 1 | Value   Word 
 2 |      1  LLow
 3 |      2  Low
 4 |      3  Med
 5 |      4  MedHi
 6 |      5  High

Result (Calculations)

The column titles are manually entered / copy/pasted. The data below them is calculated; the formula is placed on A2.

   |    A    |   B   |    C    |   
---+---------------------------  
 1 | Key A    Key B   Row Total
 2 | A        A               9
 3 | A        A               9
 4 | A        A               9
 5 | B        A              10

Explanation

In order to keep the complexity at the lowest level that I thinked that is possible for a one formula solution, I avoided to use open references. Once you understand it, you could decide to refine it or to try other thing.

The above formula includes

The solution from my answer to An arrayformula to find the previous non-empty cell in another column to fill-up the blanks,
A VLOOKUP to replace text by their values, taken from the Data Inputs spreadsheet
MMULT to make row sums
Arrays to put things together.

If you add more rows to the Form Respondes 4 worksheet, then you should update the following references by replacing the 5 on each appearance of them by the number of the last row to include.

A2:B5
2:5
C2:E5