combine long list to fill a page at a time

[dkent1982]

I was given a long list provided with information containing three columns in order. I need to combine this list to where it stays in order but fill a page at a time. Below is a picture of what i have been given. Any ideas of how i can combine this list to have it more like the other example below. 

[#2]

@dkent1982 I'm not sure I understand, but if you want to 'wrap' your list and you have excel 365 then I created a LAMBDA function in the attached to do that.  The formula is:

ReshapeXrows = lambda(in,rows,LET(i, EXPAND(in,ROUNDUP(ROWS(in)/rows,0)*rows,,""),
                   DROP(REDUCE("", SEQUENCE(ROUNDUP(ROWS(i)/rows,0),,0,rows), 
                      LAMBDA(u,v, HSTACK(u, CHOOSEROWS(i, SEQUENCE(rows)+v)))),,1)))

so do a print preview of the existing list and zoom in/out to the size you want and see how many rows are shown.  Then on new worksheet use above lambda function to create the wrapped version:

=ReshapeXrows( [originalData], [numberRows] )

Then print this new sheet (make sure to set your zoom accordingly or just set to 'fit all rows'

@Patrick2788 , @Hans Vogelaar  is there an easier way to make this 'wrap around' function?  Am I missing something that already exists?

[#3]

@mtarler 

Hi Matt, I have Office 2021, so I don't have all these new functions...

[#4]

Oh no! MS should give you an upgrade just for all the help you give here!!!

[#5]

@Hans Vogelaar 

I have Excel 2013 and i can use all these new functions in Excel for the web. 

[#6]

@mtarler 

This one may be more trouble than it's worth with a LAMBDA.  I say this because the solution is not elegant. It's the ol' join/split/wrap approach.  My sample is pulling 100 records before wrapping (I realize things go haywire if 110 is selected but I share this Lambda to give an idea of what it takes.).  This task would be a lot easier if a matrix could be wrapped:

'HyperWrap
=LAMBDA(arr,records,LET(
    w, ROWS(arr) * 3 / records,
    joined, BYROW(arr, LAMBDA(row, TEXTJOIN("|", , row))),
    bundled, TEXTJOIN("|", , WRAPCOLS(joined, records, "")),
    split, TEXTSPLIT(bundled, , "|", , , ""),
    WRAPROWS(split, w, "")
))

[#7]

ah yes, in row 1 of my solution I converted the "in" range using EXPAND to make sure it is an even multiple of the N number of rows

[#8]

@dkent1982 

As variant

hWrap = LAMBDA(array, n,
    LET(
        nR, ROWS(array),
        IF(
            nR <= n,
            array,
            LET(
                a, hWrap(DROP(array, n), n),
                IFNA( HSTACK(DROP(array, n - nR), a), "" )
            )
        )
    )
)

[#9]

A recursive calling function, nice. But, I thought the recursion limit for Lambda was pretty small and that may be a limiting factor for the usability of this function so I thought I would put it to the test. Well, I looked up recursion limit and found numerous reference that excel recursion limit was 1024 divided by number of parameters+1 so in this case (2 inputs) the recursion limit would be only about 341. So given 1000 rows if I set N to 2 that should make it explode but NOPE it worked fine!! In fact I was able to increase the initial sequence # to 6552 rows which equates to 3276 iterations. IF the n+1 factor is correct that means the new limit is 9,828. I tried this new limit in the recursion limits demo sheet found here:
https://techcommunity.microsoft.com/t5/excel/lambda-recursive-lists-and-understanding-recursion-limi...
and found the previous calculated "limits" didn't apply any more and although I couldn't reach the 9,828 / (n+1) value (my excel would crash) I was able to come pretty close and many times the previously calculated value.
So did Excel change that recursion limit?
On another note, although this recursion is 'sexy' it does seem to run slower (at least when I push the limits where I can make it take ~3s vs my equation that would take ~2s but that is very informal)

[#10]

I did some timings (5 timings went into each average).
The original array with 1,000 rows (3,000 elements), stacking 100 at a time - .000888 seconds.
An array of 100,000 rows (300,000 elements), stacking 1,000 - .00083 seconds

That's impressive. The recursive Lambda reads well because it's simple.

[#11]

@Patrick2788 So I was looking at ways the function might be improved and noted that I could replace CHOOSEROWS(array of rows) with TAKE(DROP(x)y) and was wondering if that would be better or worse (original followed by variation):

ReshapeXrows = LAMBDA(in, rows,
    LET(
        i, EXPAND(in, ROUNDUP(ROWS(in) / rows, 0) * rows, , ""),
        DROP(
            REDUCE(
                "",
                SEQUENCE(ROWS(i)/rows, , 0, rows),
                LAMBDA(u, v, HSTACK(u, CHOOSEROWS(i, SEQUENCE(rows) + v)))
            ),
            ,
            1
        )
    )
);
Reshape = LAMBDA(in, rows,
    LET(
        i, EXPAND(in, ROUNDUP(ROWS(in) / rows, 0) * rows, , ""),
        DROP(
            REDUCE(
                "",
                SEQUENCE(ROWS(i)/rows, , 0, rows),
                LAMBDA(u, v, HSTACK(u, Take(drop(i, v-1),rows)))
            ),
            ,
            1
        )
    )
);

Based on a VERY informal test I believe the CHOOSEROWS is better than the TAKE(DROP())

[#12]

@mtarler 

CHOOSEROWS does seem a bit faster. TAKE/DROP is a good swap for INDEX in some situations.

Data sample: 20,000 x 1 array

5 timings: .000382 seconds avg

=TAKE(DROP(E1#,10000),10000)

5 timings: .000376 seconds avg

=CHOOSEROWS(E1#,SEQUENCE(10000,,10001))

[#13]

@mtarler 

I believe recursion stack limit was expanded about an year ago. From 1024 to 16384 if I remember correctly.

[#14]

Thx I didn't see that and all the google links I found still link outdated articles/posts/etc... Good to know.