CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Raphael Obadia
Raphael Obadia

Reputation: 445

How to make that formula work for a range

I struggle to apply my formula for a range.

here what I haveenter image description here

And here what I need enter image description here

I success to reproduce the same schema with one row : enter image description here

with this formula : ={transpose(split(rept(A2&"|",9),"|")),transpose(split(rept(B2&"|",9),"|")),transpose({C1:K1}),transpose({C2:K2}),transpose(split(rept(U2&"|",9),"|")),transpose(split(rept(V2&"|",9),"|"))}

but for the all range A2:V93 i try this but (and i have ommit the headers C1:K1 ) i have a error(Result was not automatically expanded, please insert more columns ) : =arrayformula({transpose(split(rept(A2:A93&"|",9),"|")),transpose(split(rept(B2:B93&"|",9),"|")),transpose({C2:K93}),transpose(split(rept(U2:U93&"|",9),"|")),transpose(split(rept(V2:93&"|",9),"|"))})

and the last question how to do this.

enter image description here

I have try with this formula. but if i add transpose, it will transpose all cols to rows and its not why i want =arrayformula(split({{rept(A2:A93&"|",3)}},"|"))

Thank you very much

EDIT : link => sheets

Upvotes: 1

Views: 393

Answers (1)

MattKing
MattKing

Reputation: 7773

I think this "unpivoting" formula with Flatten should work for you.

=ARRAYFORMULA(QUERY(SUBSTITUTE(SPLIT(FLATTEN(MK.Clean.Data!A2:A&"|"&MK.Clean.Data!B2:B&"#|"&TO_TEXT(MK.Clean.Data!U2:U)&"#|"&MK.Clean.Data!C1:T1&"|"&MK.Clean.Data!V2:V&"|"&MK.Clean.Data!C2:T),"|",0,0),"#",""),"where Col6<>'' order by Col1",0))

I should mention that FLATTEN() is an undocumented function that I only recently discovered.  I believe it is intended to remain "hidden" in the back end of the sheets programming, but if what I did is what you're after, there really isn't a more efficient way to do it.

Upvotes: 3

Related Questions