CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-formulaarray-formulas
Crypt
Crypt

Reputation: 13

Arrayformula with Filter of Mulitple Columns

I can't convert this formula into an arrayformula.

short version: =filter(H2:AI2,H2:AI2<>"")

long version: =if(H2<>"",H2,IF(I2<>"",I2,IF(J2<>"",J2,IF(K2<>"",K2,IF(L2<>"",L2,IF(M2<>"",M2,IF(N2<>"",N2,IF(O2<>"",O2,IF(P2<>"",P2,IF(Q2<>"",Q2,IF(R2<>"",R2,IF(S2<>"",S2,IF(T2<>"",T2,IF(U2<>"",U2,IF(V2<>"",V2,IF(W2<>"",W2,IF(X2<>"",X2,IF(Y2<>"",Y2,IF(Z2<>"",Z2,IF(AB2<>"",AB2,IF(AC2<>"",AC2,IF(AD2<>"",AD2,IF(AE2<>"",AE2,IF(AF2<>"",AF2,IF(AG2<>"",AG2,IF(AH2<>"",AH2,IF(AI2<>"",AI2,"")))))))))))))))))))))))))))

Neither returns an array effect. Dragging down the formula will work until a form is submitted. The cell in that row loses its formula.

SAMPLE1

SAMPLE2

Upvotes: 0

Views: 90

Answers (1)

Krzysztof Dołęgowski
Krzysztof Dołęgowski

Reputation: 2660

If each column has just one cell filled then problem is simple:

You take ALL the cells in the table and join together (I use # as separator) using textjoin. Then I split them (which gives me a row of cells).

To have it as a column I use transpose at the end.

=TRANSPOSE(split(textjoin("#",1,A1:E10),"#"))

enter image description here

If there is more content in each row - it will be solved in a totally different way.

Upvotes: 1

Related Questions