Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Wrap an array which has two columns

Copper Contributor

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.

 

4 Replies

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

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

flexyourdata_0-1707762953832.png

 

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

 

rprsridhar_1-1707808631331.png

 

 

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