How to wrap table, not vector (WRAPCOLS for tables)

Zdenek_Moravec Here's a few more options to add to the mix...

METHOD 1: the MAKEARRAY / INDEX method (slowest)

=LET(arr, T_Data, wrap_count, 10, pad_with, "",
w, COLUMNS(arr),
n, CEILING.MATH(ROWS(arr)/wrap_count*w, w),
MAKEARRAY(wrap_count, n, LAMBDA(r,c, LET(
   row, (ROUNDUP(c/w, 0)-1)*wrap_count+r,
   col, MOD(c-1, w)+1,
   IFERROR(INDEX(arr, row, col), pad_with)))))

Also, to repeat the headers the appropriate number of times, the TOROW function can be used with IF & SEQUENCE:

=LET(arr, T_Data[#All], wrap_count, 10,
w, COLUMNS(arr),
n, CEILING.MATH(ROWS(DROP(arr, 1))/wrap_count*w, w),
TOROW(IF(SEQUENCE(n/w), TAKE(arr, 1))))

To make things easier, you can also define a custom LAMBDA function in Name Manager called WRAPTABLE by combining both formulas into a single function:

=LAMBDA(array,wrap_count,[header],[pad_with], LET(
data, IF(header, DROP(array, 1), array),
w, COLUMNS(data),
n, CEILING.MATH(ROWS(data)/wrap_count*w, w),
v, MAKEARRAY(wrap_count, n, LAMBDA(r,c,
   IFERROR(INDEX(data, (ROUNDUP(c/w, 0)-1)*wrap_count+r, MOD(c-1, w)+1), pad_with))),
IF(header, VSTACK(TOROW(IF(SEQUENCE(n/w), TAKE(array, 1))), v), v)))

Once defined, the WRAPTABLE function can be used as follows:

=WRAPTABLE(T_Data[#All], 10, TRUE)

Note: If the specified array includes a header row, set the optional [header] argument to 1 or TRUE.

To return the data for "Biology" or "Chemistry" wrapped by 4 records, as demonstrated on the two worksheets in your sample file, the WRAPTABLE function can be adapted as follows:

=WRAPTABLE(FILTER(DROP(T_Data,, 1), TAKE(T_Data,, 1)=A1), 4,, "")

METHOD 2: the REDUCE / HSTACK method, with WRAPCOLS, TOCOL, WRAPROWS & CHOOSECOLS (faster)

=LET(arr, T_Data, wrap_count, 10, pad_with, "",
w, COLUMNS(arr),
a, WRAPCOLS(TOCOL(arr), w*wrap_count, pad_with),
DROP(REDUCE(0, SEQUENCE(COLUMNS(a)), LAMBDA(v,n,
   HSTACK(v, WRAPROWS(CHOOSECOLS(a, n), w)))),, 1))

Which can also be used to define the custom WRAPTABLE function as follows:

=LAMBDA(array,wrap_count,[header],[pad_with], LET(
data, IF(header, DROP(array, 1), array),
w, COLUMNS(data),
a, WRAPCOLS(TOCOL(data), w*wrap_count, pad_with),
b, DROP(REDUCE(0, SEQUENCE(COLUMNS(a)), LAMBDA(v,n,
   HSTACK(v, WRAPROWS(CHOOSECOLS(a, n), w)))),, 1),
IF(header, VSTACK(TOROW(IF(SEQUENCE(COLUMNS(b)/w), TAKE(array, 1))), b), b)))

METHOD 3: WRAPROWS with SORTBY, TOCOL & EXPAND (fastest)

=LET(arr, T_Data, wrap_count, 10, pad_with, "",
n, ROUNDUP(ROWS(arr)/wrap_count, 0),
h, wrap_count*n,
w, COLUMNS(arr),
a, MOD(INT(SEQUENCE(h*w,, 0)/w), wrap_count)+1,
WRAPROWS(SORTBY(TOCOL(EXPAND(arr, h,, pad_with)), a), n*w))

Which can also be used to define the custom WRAPTABLE function as follows:

=LAMBDA(array,wrap_count,[header],[pad_with], LET(
data, IF(header, DROP(array, 1), array),
n, ROUNDUP(ROWS(data)/wrap_count, 0),
h, wrap_count*n,
w, COLUMNS(data),
a, MOD(INT(SEQUENCE(h*w,, 0)/w), wrap_count)+1,
b, WRAPROWS(SORTBY(TOCOL(EXPAND(data, h,, pad_with)), a), n*w),
IF(header, VSTACK(TOROW(IF(SEQUENCE(n), TAKE(array, 1))), b), b)))

Please see the attached files, which contain all of the above examples...

Hi Zdenek_Moravec 

Would this work for you (Biology; 4 rws)?:

=LET(
  rws,    4,
  data,   FILTER(T_Data[[Question]:[Answer4]], T_Data[Subject] = "biology"),
  k,      SEQUENCE( ROUNDUP( ROWS(data) / rws, 0)-1 ),
  Stack,  LAMBDA(seed,n, HSTACK(seed, TAKE( DROP(data, rws*n), rws) ) ),
  IFNA( REDUCE(TAKE(data,rws), k, Stack), "")
)

Zdenek_Moravec 

=LET(filtered,DROP(FILTER(A2:F21,A2:A21=I1),,1),

IFNA(

DROP(

REDUCE("",SEQUENCE(ROUNDUP(ROWS(filtered)/J1,0),1,1,J1),

LAMBDA(u,v,

HSTACK(u,

IF(MAX(ROWS(filtered))-v>=J1,

CHOOSEROWS(filtered,SEQUENCE(J1,1,v,1)),

CHOOSEROWS(filtered,SEQUENCE(MAX(ROWS(filtered))-v+1,1,v,1))))))

,,1),

"")

)

You can apply this formula. In my example the subject is selected in cell I1. The number of rows is specified in cell J1.

Zdenek_Moravec 

With Power Query that could be (for Biology; rws = 4):

// PQ_Bio
let
    rws = 4,
    Source = Excel.CurrentWorkbook(){[Name="T_Data"]}[Content],
    FilteredBio = Table.SelectRows(Source, each ([Subject] = "Biology")),
    RemovedSubject = Table.RemoveColumns(FilteredBio, {"Subject"}),
    Hstacked = Table.FromColumns(
        List.Combine(
            List.Transform(Table.Split(RemovedSubject, rws), Table.ToColumns)
        )
    )
in
    Hstacked

 SQL:

create temp table aa as
select (row_number() over ( partition by f01)-1)%4 r ,* from wrap_table;
//select * from aa;
create temp table bb as
select r,f01,group_concat(f02||'</td><td>'||f03||'</td><td>'||f04||'</td><td>'||f05||'</td><td>'||f06,'</td><td>') cols from aa group by r,f01;

select udf_exec_sql('select cols '||f01||' from bb where f01 like '''||f01||'''') 'Data' from wrap_table group by f01;