Forum Discussion

dkent1982's avatar
dkent1982
Copper Contributor
Dec 02, 2022

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 hav...
excel
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Dec 04, 2022

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), "" )
            )
        )
    )
)
  • mtarler's avatar
    mtarler
    Silver Contributor
    Dec 06, 2022
    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)
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Dec 13, 2022

      mtarler 

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

      • mtarler's avatar
        mtarler
        Silver Contributor
        Dec 13, 2022
        Thx I didn't see that and all the google links I found still link outdated articles/posts/etc... Good to know.
    • Patrick2788's avatar
      Patrick2788
      Silver Contributor
      Dec 06, 2022
      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.
      • mtarler's avatar
        mtarler
        Silver Contributor
        Dec 06, 2022

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

         

Resources