ARF

[Xlambda]

ARF !! recursive !! DIY Array Recursive Function kit. Idea triggered by an ExcelIsFun YT video posted yesterday EMT1744 (different topic) . Other functions on minisheet AFLAT.
Other functions, never defined before, can be copied directly from minisheet APP and ASU, they follow same syntax as the "kit".
If a function (F) (build in or custom made), applied to an array (a), returns a single value or a horizontal 1D array (v), F(a)=v, then, ARF((a1,a2,…an),,)={F(a1);F(a2);…;F(an)}={v1;v2…;vn}
ARF(a,ai,i)=LAMBDA(a,ai,i,LET(n,AREAS(a),s,SEQUENCE( n),j,IF(i="",n,i),x,INDEX(a,,,j),IF(j=0,ai,ARF(a,IF(s=j,F(x),ai),j-1))))
a: (a1,a2,…,an), non-adjacent ranges on same sheet, enclosed in parentheses like in 2nd syntax of INDEX function INDEX(reference, row_num, [column_num], [area_num])
ai,i: initial values always ignored, (,,) have the role of "vector carriers"
If F has one or more arguments F(a,k) : (I will cover more complex examples as soon as I will have some spare time)
ARF(a,k,ai,i)=LAMBDA(a,k,ai,i,LET(n,AREAS(a),s,SEQUENCE( n),j,IF(i="",n,i),x,INDEX(a,,,j),IF(j=0,ai,ARF(a,k,IF(s=j,F(x,k),ai),j-1))))

AGG study.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Base Case example (simplest case scenario): append arrays (single cell , or 1D horizontal arrays)
2	Note: We don't need more functionality since the functions will return single values or horizontal 1D arrays, keeping the kits construction as simple as possible
3	To append 2D arrays we already have					APPENDNHV
4	Writing the recursive function following the syntax draft, function name, let's define APP:
5	APP(a,ai,i)=LAMBDA(a,ai,i,LET(n,AREAS(a),s,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,,,j),IF(j=0,ai,APP(a,IF(s=j,x,ai),j-1))))
6	The appending "engine" functionality is extremely simple IF(s=j,x,ai)
7	Is equivalent with this :
8	=LET(s,SEQUENCE(3),SWITCH(s,1,"a",2,"b",3,2))
9	a
10	b		a1				=APP((C11:D11,C14:E14,C17:D17),,)
11	2		a	2			a	2	#N/A
12							b	3	4
13			a2				1	2	#N/A
14			b	3	4
15
16			a3
17			1	2
18
19	General Case example: let's consider a more complex F(x) that sorts in ascending order the unique elements of an array
20		a1
21		a	2	3			=TRANSPOSE(SORT(UNIQUE(AFLAT(B21:D23))))
22		x	w	2			2	3	a	t	w	x
23		t	x	a
24
25	so F(x) will be F(x)=TRANSPOSE(SORT(UNIQUE(AFLAT(x))))
26	Now let's define our specific recursive function (ASU) using the kit syntax
27	ASU(a,ai,i)=LAMBDA(a,ai,i,LET(n,AREAS(a),s,SEQUENCE(n),j,IF(i="",n,i),x,INDEX(a,,,j),IF(j=0,IFERROR(ai,""),ASU(a,IF(s=j,TRANSPOSE(SORT(UNIQUE(AFLAT(x)))),ai),j-1))))
28		a2
29		a	2	-1			=ASU((B21:D23,B29:D33,B36:C37),,)
30		q	a	c			2	3	a	t	w	x
31		d	c	2			-1	2	3	a	c	d	q
32		-1	3	-1			2	q
33		2	d	d
34
35		a3					Other function on minisheet
36		q	2				AFLAT
37		2	q
38
39	This proves the functionality of the kit, whatever function we can use to calculate an array and return a single cell result or 1D horiz array, can be done recursively to many arrays using the kit.
40	It will be nice to see others function creations posted here!!!
41
ARF post

Cell Formulas
Range	Formula
A8,G29,G21,G10	A8	=FORMULATEXT(A9)
A9:A11	A9	=LET(s,SEQUENCE(3),SWITCH(s,1,"a",2,"b",3,2))
G11:I13	G11	=APP((C11:D11,C14:E14,C17:D17),,)
G22:L22	G22	=TRANSPOSE(SORT(UNIQUE(AFLAT(B21:D23))))
G30:M32	G30	=ASU((B21:D23,B29:D33,B36:C37),,)
Dynamic array formulas.

 

[mgirvin]

No recursion limit anymore! 10 M iterations… and no error!! Talk about nice accidental discovery : )

More versatility with recursion than iteration: the future of Microsoft programming will go through Excel Lambda who will build the new L List set of functions that blow away the ease and power of even Show Values As: Percent of Parent Row Total.

Xlambda : ) : ) : ) : ) : ) : ) : ) : ) : )