Wrap an array which has two columns

Question

I am using office 365 (family). I have an array output which is 27 rows long and two column wide.

I am trying to wrap it dynamically say for eg. after the 13th row to be displayed in the forth and fifth column.

I am able to do it with a single column [vector] output using index and sequence but not when it is more than one column [multiple column array].

Currently I am managing it with TAKE and DROP function. Obviously both the functions are entered in separate cells.

Is there a work around to do it with a single cell formula without compromising on its dynamic capabilities without macros or VBA or power query.

djclements · Answer

rprsridhar&nbsp;Please see the attached workbook, which contains a custom LAMBDA function defined in Name Manager. For more information, please see "METHOD 3" in my response to this post:&nbsp;How to wrap table, not vector (WRAPCOLS for tables)

flexyourdata · Answer

rprsridhar&nbsp;&nbsp;Try this formula:&nbsp;=LET(
    array, B1#,
    wrapcount, 13,
    totalRows, ROWS(array),
    groups, SEQUENCE(ROUNDUP(totalRows / wrapcount, 0), , 0),
    first, CHOOSEROWS(array, SEQUENCE(wrapcount)),
    REDUCE(
        first,
        DROP(groups, 1),
        LAMBDA(a, b,
            HSTACK(
                a,
                CHOOSEROWS(
                    array,
                    SEQUENCE(
                        MIN(wrapcount, totalRows - b * wrapcount),
                        ,
                        b * wrapcount + 1
                    ))))))&nbsp;Or alternatively, as a LAMBDA function:&nbsp;WRAPROWS_ARRAY = LAMBDA(array, wrapcount, 
LET(
    totalRows, ROWS(array),
    groups, SEQUENCE(ROUNDUP(totalRows / wrapcount, 0), , 0),
    first, CHOOSEROWS(array, SEQUENCE(wrapcount)),
    REDUCE(
        first,
        DROP(groups, 1),
        LAMBDA(a, b,
            HSTACK(
                a,
                CHOOSEROWS(
                    array,
                    SEQUENCE(
                        MIN(wrapcount, totalRows - b * wrapcount),
                        ,
                        b * wrapcount + 1
                    ))))))
)&nbsp;For example:&nbsp;

rprsridhar · Answer

djclements&nbsp;&nbsp;,&nbsp;flexyourdata&nbsp;Thanks for your quick response.While trying few solutions, I found a single cell formula without using let, lambda.Here is my formula.=IFERROR(INDEX(TOCOL(TRANSPOSE(VSTACK(DataSheet[#Headers],FILTER(DataSheet,$B$1=DataSheet[PC NAME],"Not Available"))),0,FALSE),SEQUENCE(9,6,1,1)),"")Currently my table has 27 columns in my table and I am displaying it along with column headers.So in my case I was required to block 54 cells [ 27 for data and 27 for headers].One can make it flexible by using variables in sequence function to make it dynamic.Now I can see my data of 27 rows and 2 col in 9 rows and 6 col.I will appreciate your feed back.&nbsp;&nbsp;&nbsp;

djclements · Answer

rprsridhar&nbsp;That's a nice, simple formula (INDEX/TOCOL/SEQUENCE). Looks like it outputs by row (left-to-right, top-to-bottom) instead of by column (top-to-bottom, left-to-right), which is great if that's your desired result. Thanks for sharing!

medhum · Answer

This worked great for an attendance sheet for a Mens Shed weekly social gathering that needed to be dynamic

Forum Discussion

Wrap an array which has two columns

5 Replies

Resources