Forum Discussion
HRECURSE instead of MAKEARRAY, recursing LAMBDA
ecovonrein Great code! I've used it in VRECURSE form to stack results of an iterative function that wouldn't spill otherwise.
Is there a way to update it to refer back to a specific row/column index of the previous step? I need more than just single row/column operations. I'd like to be able to specify INDEX(p, r, c) in the calculation.
Thanks! However, since I wrote this great piece of code, I learned that there is one function in the Excel toolkit that solves the problem without the need for recursion. Now, brace yourself. The great jesters in Seattle have dubbed this function, which allows us to augment things, REDUCE.
So, if you write:
=REDUCE(0;SEQUENCE(10);LAMBDA(prev;this;VSTACK(prev;HSTACK(this;INDEX(prev;this-1;1)))))
then you get what you want. You are successively augmenting "prev" by forever VSTACKing it (I generate 2 columns with this formula), and you have access to all of "prev", which is what you asked for. That is, you don't need to use V/HRECURSE 😉
PS: Credit for the discovery that REDUCE can be abused in this way goes to lori_m.
- Christopher GrahamAug 18, 2023Copper Contributor
ecovonrein Thanks. I'm struggling a bit with the REDUCE code on a few fronts: 1) where my function is applied; and 2) where the original array comes into play.
Is there a post that discusses this approach in more detail (search was unhelpful)?
- ecovonreinAug 19, 2023Iron Contributor
Sorry, did not have much time yesterday. Here another annotated stab:
=LET( ' Initializing Row 0 - the start-up row - however you need it. I only do 2 colums here. R0; {1\2}; ' This the equivalent to VRECURSE, iterating 10 times REDUCE(R0;SEQUENCE(10); ' This is your Lambda. It receives the evolving results as "tbl" and the heartbeat "i" LAMBDA(tbl;i; LET( ' Since R0 is in tbl(1), tbl(i) is actually row i-1. But you can access any previous row. Ri_1; INDEX(tbl;i;0); ' Do your thing. It will be more meaningful than my thing and ' probably involve HSTACK(c1,c2,...,cn) :) Ri; Ri_1+1; ' Append your result row "Ri" to the "tbl" VSTACK(tbl;Ri) )) ) )
- Christopher GrahamAug 20, 2023Copper ContributorThank you so much for the annotation! After a bit of trial and error (lots of errors), I was finally able to accomplish what I set out to do.
This specific application was actually column by column, so I switched to HSTACK and ended up using MAKEARRAY in the middle to build my columns after I defined the first.
- ecovonreinAug 18, 2023Iron Contributor
You were working in a vertical layout (using VRECURSE)? So your code replaces the HSTACK in my example. The HSTACK contributes one row to the output table (in the same way you would have deployed HSTACK in my VRECURSE, too). As many as you like. The SEQUENCE provides the heartbeat, counting from 1..10. You would use "this" to index whatever source array you are processing into the multiple columns. "prev" is the output table that progressively gets larger (one row at a time). My VRECURSE only offered access to INDEX(prev;this-1;0). But with reduce, you can also access the row this-2, -3 ... as you asked (as I understood you).
PS: May be I should have called "prev" -> "tbl" and "this" -> "i".