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

[Zdenek_Moravec]

Dear colleagues

I have a long table of 5 columns and maybe 200 rows.

The task is to wrap the table after a specified number of rows to see the table in landscape on a new sheet.

I was thinking about WRAPCOLS, but it works only for vector (one column). Is there a way to wrap whole table using M365 functions? If not, the next option can be Power Query.

See example attached.

Thank You very much for any hint.

Zdenek Moravec

Cesky Krumlov, Czechia

[#2]

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), "")
)

[#3]

@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.

[#4]

@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

[#5]

@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...

[#6]

Dear colleagues,
Thank You very much for Your responses. For me personally, the power query solution is the most understandable way. The LET function is very powerful and looks clear, but it is for me an Excel university. I am going to debug all the LET variations, on the first look, I have no clue, how the sub functions work.
Zdenek Moravec

[#7]

@Zdenek_Moravec  alternatively this formula doesn't use any Lambda but in no way am I saying it is better or more efficient, it was just a self-challenge for a different approach:

=LET(in, A1#, wrapAt, 21,
TEXTSPLIT(TEXTJOIN(",",FALSE,HSTACK(WRAPCOLS(TEXTSPLIT(TEXTJOIN(",",FALSE,EXPAND(in,,COLUMNS(in)+1,";")),,",;,"),wrapAt,""),EXPAND(";",wrapAt,,";"))),",",";",TRUE,,""))

[#8]

@Zdenek_Moravec 

Whatever option you finally adopt, please don't forget to mark it as response/solution as I'm quite sure such a need will surface again. Thanks

[#9]

 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;

[#10]

=LET(a,FILTER(Data!A2:F21,Data!A2:A21=A1),r,4,IF(ROWS(a)<=r,a,IFNA(TAKE(REDUCE(a,SEQUENCE(ROWS(a)/r),LAMBDA(x,y,HSTACK(x,DROP(a,y*r,1)))),r),"")))