Spill dynamic array across multiple columns with a defined depth

[EMoscosoCam]

Hello

Is it possible to distribute a dynamic array into multiple columns? For example, consider an array with 22 values, and you want to spill the values in sequence across 6 columns with a maximum row count of 4:

In this example a have put numbers in sequence for the sake of simplicity, but my real array is made of texts.


Thanks!

 

[Peter_SSs]

Using Eric W answer as a base, I wrote a WRAPROWS() imitation:

Does that have an advantage over Eric's method (or the MAKEARRAY suggestion)? Is it just that the formula seen in the worksheet looks simpler?

 

[EMoscosoCam]

Does that have an advantage over Eric's method (or the MAKEARRAY suggestion)? Is it just that the formula seen in the worksheet looks simpler?

Using "MakeArray" you have to provide the number of rows and columns, with Eric's method you only provide the number of rows, and the number of columns increase as needed - which I believe is the same behaviour as the WrapRows() function. Furthermore, when using MakeArray and you pass a maximum of 6 columns, and the data needs 7, those items are ignored.

 

[Peter_SSs]

Using "MakeArray" you have to provide the number of rows and columns,

Fair enough - though I hard-coded the 6 because your original question seemed to specify the 6 columns.

For the columns to be flexible it could be ..

=LET(rws,4,cols,ROUNDUP(ROWS(A2#)/rws,0),MAKEARRAY(rws,cols,LAMBDA(r,c,IFERROR(INDEX(A2#,(c-1)*rws+r),""))))

The basis of my question remains though

Does that have an advantage over Eric's method (or the MAKEARRAY suggestion)? a standard worksheet function formula that doesn't require the LAMBDA to be entered into the name manager? Is it just that the formula seen in the worksheet looks simpler?

 

[EMoscosoCam]

Fair enough - though I hard-coded the 6 because your original question seemed to specify the 6 columns.

For the columns to be flexible it could be ..

=LET(rws,4,cols,ROUNDUP(ROWS(A2#)/rws,0),MAKEARRAY(rws,cols,LAMBDA(r,c,IFERROR(INDEX(A2#,(c-1)*rws+r),""))))

The basis of my question remains though

Thanks for updating your formula! I wrote a custom function because it was simpler to use in the worksheet. What's more, I named it "F_WRAPROWS", so that when I finally got the roll-out I could easily change it. I must admit that I still have to understand either solution xD

= LAMBDA(vector, wrap_count, LET(rws, wrap_count,cols,ROUNDUP(ROWS(vector)/rws,0),MAKEARRAY(rws,cols,LAMBDA(r,c,IFERROR(INDEX(vector,(c-1)*rws+r),"")))))

 

[Peter_SSs]

Thanks for updating your formula!

No problem.

I wrote a custom function because it was simpler to use in the worksheet.

A bit harder to set up but it does have that advantage.