Forum Discussion
combine long list to fill a page at a time
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.
13 Replies
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), "" ) ) ) ) )- 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-limits-of/m-p/2199690
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)I believe recursion stack limit was expanded about an year ago. From 1024 to 16384 if I remember correctly.
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 , HansVogelaar is there an easier way to make this 'wrap around' function? Am I missing something that already exists?
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, "") ))- 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 🙂
Hi Matt, I have Office 2021, so I don't have all these new functions... 😞
I have Excel 2013 and i can use all these new functions in Excel for the web.
