Wrap an array which has two columns

[rprsridhar]

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.

[#2]

@rprsridhar 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: How to wrap table, not vector (WRAPCOLS for tables)

[#3]

@rprsridhar 

Try this formula:

=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
                    ))))))

Or alternatively, as a LAMBDA function:

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

For example:

[#4]

@djclements  , @flexyourdata 

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.

[#5]

@rprsridhar 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!