Spreadsheet table wrapping

Question

Given a long spreadsheet table, is there a function to wrap it (break it) into multiple column tables?

What I tried

=query(dataTable, "select A, B limit "&floor(ROWS(dataTable)*0.5))
=query(dataTable, "select A, B offset "&floor(ROWS(dataTable)*0.5))

but maybe there is a better solution, especially that it is not flexible in the number of columns (e.g: I want the table to be distributed on 3 or 4 tables not 2).

One other problem is if the input table is created using query, and that query is changed to include additional columns, the cells where they include =query(dataTable, "select ... must be separated more to give space.

What criteria are you looking at to split the Input? Will it be always segmented into 2 tables or does that vary as well? — Broly
I want it to vary. That's why I mentioned in "What I tried" that "it is not flexible". I want it flexible by specifying how many split tables are. — AvidSeeker

player0 player0 · Accepted Answer · 2021-06-13T21:12:45

especially that it is not flexible in the number of columns

you can skip the select like:

=QUERY(dataTable, "limit  "&FLOOR(ROWS(dataTable)*0.5))
=QUERY(dataTable, "offset "&FLOOR(ROWS(dataTable)*0.5))

but maybe there is a better solution

you can use single formula:

={QUERY(dataTable, "limit  "&FLOOR(ROWS(dataTable)*0.5)),
  QUERY(dataTable, "offset "&FLOOR(ROWS(dataTable)*0.5))}

just make sure total rows are divisible by 2

UPDATE:

=ARRAYFORMULA(SUBSTITUTE(TRIM(SPLIT({REPT(TEXTJOIN(, 1, A1:D1&"×"), F1); 
 FLATTEN(QUERY(TRANSPOSE(IFNA(VLOOKUP(
 TRANSPOSE(SEQUENCE(F1, ROUNDUP(MAX(IF(A:A="",,ROW(A:A)))/F1))), 
 {ROW(A2:A)-1, FLATTEN(QUERY(TRANSPOSE(IF(A2:D="", "♀×", A2:D&"×")),,9^9))}, 
 2, ))),,9^9))}, "×", 1, )), "♀", ))

Spreadsheet table wrapping

What I tried

1 Answers

UPDATE:

spreadsheet demo