ATEXTSPLIT

[Xlambda]

ATEXTSPLIT array text split by any delimiter,(one or more chars), fine-tuned for complex scenarios.
Wrote this function inspired by MrExcel video: Excel TEXTJOIN And The Need For TEXTSPLIT with Ann K. Emery - 2407
The idea was to create a function that is clean and robust, does not call other functions, non-recursive, no FILTERXML, and uses same arguments as TEXTJOIN. What TEXTJOIN joins, ATEXTSPLIT should be able to split it back like it was, with same arguments. When we join, we choose the delimiter, but when we split imported data, things can go quite tricky.
ar: array 1D vertical, dl: delimiter, any string , ea: empty argument , 0 or omitted - ignores empty ; 1 - does not ignore empty
Note: There are 2 special characters in the formula, (h,"º",d,"ª"), first variables after "LET" . These should be changed if any of them is found on the array of strings. Formula advises if this happens.

=LAMBDA(ar,dl,ea,
    LET(h,"º",d,"ª",ch,ISNUMBER(SEARCH(CHOOSE({1,2},h,d),ar)),
       a,SUBSTITUTE(ar,dl,d),b,IF(ea,a,SUBSTITUTE(SUBSTITUTE(TRIM(SUBSTITUTE(SUBSTITUTE(a," ",h),d," "))," ",d),h," ")),
       n,LEN(b)-LEN(SUBSTITUTE(b,d,""))+1,c,MAX(n),sc,SEQUENCE(,c),
       x,SEARCH(h,SUBSTITUTE(d&b,d,h,sc)),y,SEARCH(h,SUBSTITUTE(b&d,d,h,sc)),m,IFERROR(MID(b,x,y-x),""),
       IF(OR(ch),"change special chars",IFERROR(--m,m))
    )
)

LAMBDA 7.0.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI
1						TEXTJOIN		ATEXTSPLIT
2								empty ignored						empty not ignored						2 chars				empty ignored
3						empty not ignored		ea arg.=0						ea arg.=1						delimiter				ea arg.=0
4	sample					=TEXTJOIN(",",0,A5:D5)		=ATEXTSPLIT(F5:F7,",",)						=ATEXTSPLIT(F5:F7,",",1)										=ATEXTSPLIT(T5:T7,"><",)					LEN check
5	a	4		c		a,4,,c		a	4	c				a	4		c			a><6.8><t				a	6.8	t			1	3	1
6	d			0.5		d,,,0.5		d	0.5					d			0.5			><2.4 w><				2.4 w					6	0	0
7	g h	i 2	k l	3 n		g h,i 2,k l,3 n		g h	i 2	k l	3 n			g h	i 2	k l	3 n			<Ac4><gh>< ><				<Ac4	gh				4	2	1
8																															space
9						empty ignored		ea arg.=0						ea arg.=1										empty not ignored
10						=TEXTJOIN(",",,A5:D5)		=ATEXTSPLIT(F11:F13,",",)						=ATEXTSPLIT(F11:F13,",",1)										ea arg.=1
11						a,4,c		a	4	c				a	4	c								=ATEXTSPLIT(T5:T7,"><",1)					LEN check
12						d,0.5		d	0.5					d	0.5									a	6.8	t			1	3	1	0
13						g h,i 2,k l,3 n		g h	i 2	k l	3 n			g h	i 2	k l	3 n								2.4 w				0	6	0	0
14																								<Ac4	gh				4	2	1	0
15						Complex scenario																							space followed by an empty string
16								empty ignored						empty not ignored
17								ea arg.=0						ea arg.=1										error debug.
18						sample		=ATEXTSPLIT(F19:F22,",",)						=ATEXTSPLIT(F19:F22,",",1)												=ATEXTSPLIT(X19,",",)
19						a,b,c,2.3,,d e,		a	b	c	2.3	d e		a	b	c	2.3		d e					a,ºb,c		change special chars
20						,,g,,h, ,1.8		g	h		1.8					g		h		1.8						=ATEXTSPLIT(X21,",",1)
21						, k m, ,4.9, ,p		k m		4.9		p			k m		4.9		p					a,ªb,c		change special chars
22						, ,,x 4, ,,			x 4								x 4
23
24	Important Obs.: -two delimiters next to each other ( ,, ) , hold an empty string between them that can be ignored or not depending of "ea" argument
25	-two delimiters sep by space ( , , ) , hold a space between them, and can not be ignored, no matter of "ea" argument
26	We can check this behaviour with LEN function. (same rule applies for leading or trailing delimiters)
27
28								=LEN(H19#)						=LEN(N19#)
29								1	1	1	3	3		1	1	1	3	0	3	0
30								1	1	1	3	0		0	0	1	0	1	1	3
31								4	1	3	1	1		0	4	1	3	1	1	0
32								1	5	1	0	0		0	1	0	5	1	0	0
33
34	- if values themselves embed inner spaces, could be important to keep them (like special codes with fixed length that follow a pattern),TRIM tweaks can not be used
35
36	ex. code pattern					sample		=ATEXTSPLIT(F37:F38,"/",)						=LEN(H37#)
37	fixed with 4 pos.					a bc/a b/ bcd		a bc	a b	bcd				4	4	4
38	/_	_	_	_ /		ab d/ d/a		ab d	d	a				4	4	4
39	/x	x	x	x/										check:pattern is kept
40
ATEXTSPLIT post

Cell Formulas
Range	Formula
H4,N36,H36,N28,H28,Z18,Z20,N18,H18,F10,X11,N10,H10,F4,X4,N4	H4	=FORMULATEXT(H5)
H5:K7,H11:K13	H5	=ATEXTSPLIT(F5:F7,",",)
N5:Q7,N11:Q13	N5	=ATEXTSPLIT(F5:F7,",",1)
X5:Z7	X5	=ATEXTSPLIT(T5:T7,"><",)
AC5:AE7,AC12:AF14	AC5	=LEN(X5#)
F5:F7	F5	=TEXTJOIN(",",0,A5:D5)
X12:AA14	X12	=ATEXTSPLIT(T5:T7,"><",1)
F11:F13	F11	=TEXTJOIN(",",,A5:D5)
H19:L22	H19	=ATEXTSPLIT(F19:F22,",",)
N19:T22	N19	=ATEXTSPLIT(F19:F22,",",1)
Z19	Z19	=ATEXTSPLIT(X19,",",)
Z21	Z21	=ATEXTSPLIT(X21,",",1)
H29:L32,N29:T32	H29	=LEN(H19#)
H37:J38	H37	=ATEXTSPLIT(F37:F38,"/",)
N37:P38	N37	=LEN(H37#)
Dynamic array formulas.

 

[Xlambda]

ATEXTSPILL.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		Vertical patterns, we can flatten the horizontal ones and filter the blanks or we use same formulas simply by deleating a ","
2
3		=ATEXTSPILL(SEQUENCE(5),LAMBDA(x,SEQUENCE(4)))
4		↓↓↓		=ATEXTSPILL(SEQUENCE(5),LAMBDA(x,SEQUENCE(4)^0+x-1))
5		↓↓↓		↓↓↓		=ATEXTSPILL(SEQUENCE(5),LAMBDA(x,SEQUENCE(x)))
6		↓↓↓		↓↓↓		↓↓↓		=ATEXTSPILL(SEQUENCE(5),LAMBDA(x,SEQUENCE(x)^0-1+x))
7		↓↓↓		↓↓↓		↓↓↓		↓↓↓		=ATEXTSPILL(SEQUENCE(5),LAMBDA(x,CHAR(SEQUENCE(x)+64)))
8		1		1		1		1		A
9		2		1		1		2		A
10		3		1		2		2		B
11		4		1		1		3		A
12		1		2		2		3		B
13		2		2		3		3		C
14		3		2		1		4		A
15		4		2		2		4		B
16		1		3		3		4		C
17		2		3		4		4		D
18		3		3		1		5		A
19		4		3		2		5		B
20		1		4		3		5		C
21		2		4		4		5		D
22		3		4		5		5		E
23		4		4
24		1		5
25		2		5
26		3		5
27		4		5
28
ATS 5

Cell Formulas
Range	Formula
B3	B3	=FORMULATEXT(B8)
D4	D4	=FORMULATEXT(D8)
F5	F5	=FORMULATEXT(F8)
H6	H6	=FORMULATEXT(H8)
J7	J7	=FORMULATEXT(J8)
B8:B27	B8	=ATEXTSPILL(SEQUENCE(5),LAMBDA(x,SEQUENCE(4)))
D8:D27	D8	=ATEXTSPILL(SEQUENCE(5),LAMBDA(x,SEQUENCE(4)^0+x-1))
F8:F22	F8	=ATEXTSPILL(SEQUENCE(5),LAMBDA(x,SEQUENCE(x)))
H8:H22	H8	=ATEXTSPILL(SEQUENCE(5),LAMBDA(x,SEQUENCE(x)^0-1+x))
J8:J22	J8	=ATEXTSPILL(SEQUENCE(5),LAMBDA(x,CHAR(SEQUENCE(x)+64)))
Dynamic array formulas.

 

[Xlambda]

Challenge. Quite a common task in real life. Create a random array with no dups by row.

ATEXTSPILL.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1		Task. Single cell formula to create an array 10 x 15 of random numbers between 1 and 100 with no duplicates by row
2
3		Using only RANDARRAY can produce duplicates by row																checking dups by row, apart from CF
4		=RANDARRAY(10,15,1,100,1)																=BYROW(B5#,LAMBDA(x,COUNT(UNIQUE(x,1))<15))
5		79	23	16	41	66	41	73	34	1	87	30	83	21	79	42		TRUE
6		95	28	17	44	46	74	15	24	4	6	4	63	73	78	37		TRUE
7		38	66	50	51	26	88	92	27	26	71	93	53	58	50	59		TRUE
8		22	15	74	41	35	54	7	47	1	8	34	86	65	55	9		FALSE
9		79	94	93	19	2	32	43	51	88	4	39	96	93	38	22		TRUE
10		69	63	75	58	97	21	70	60	76	98	56	9	4	76	42		TRUE
11		15	73	74	78	86	2	27	54	45	94	25	29	52	97	27		TRUE
12		20	80	40	54	58	54	68	53	55	14	46	99	34	52	14		TRUE
13		18	34	100	57	18	8	30	32	9	77	14	3	33	33	31		TRUE
14		86	34	76	21	42	82	20	67	66	18	63	66	80	42	54		TRUE
15
16		single cell formula
17		=ATEXTSPILL(SEQUENCE(10),LAMBDA(x,TAKE(SORTBY(SEQUENCE(,100),RANDARRAY(,100)),,15)))																=BYROW(B18#,LAMBDA(x,COUNT(UNIQUE(x,1))<15))
18		75	51	29	66	83	35	19	37	56	80	15	43	52	23	73		FALSE
19		62	27	96	46	8	56	34	78	10	31	76	98	92	11	30		FALSE
20		33	43	66	26	77	44	62	69	34	74	78	35	58	83	9		FALSE
21		66	67	20	97	17	16	78	27	52	15	57	91	47	18	42		FALSE
22		24	49	91	92	3	1	43	4	63	65	97	39	75	88	78		FALSE
23		56	38	6	25	73	58	64	83	59	98	72	9	94	18	7		FALSE
24		94	82	56	81	93	9	17	34	29	50	4	69	52	98	33		FALSE
25		67	27	82	37	21	60	20	3	71	7	44	5	31	35	10		FALSE
26		25	74	32	41	42	39	82	83	40	54	50	8	87	72	86		FALSE
27		33	44	82	18	14	2	40	34	17	96	39	66	46	35	62		FALSE
28
ATS 6

Cell Formulas
Range	Formula
B4,R17,B17,R4	B4	=FORMULATEXT(B5)
B5:P14	B5	=RANDARRAY(10,15,1,100,1)
R5:R14,R18:R27	R5	=BYROW(B5#,LAMBDA(x,COUNT(UNIQUE(x,1))<15))
B18:P27	B18	=ATEXTSPILL(SEQUENCE(10),LAMBDA(x,TAKE(SORTBY(SEQUENCE(,100),RANDARRAY(,100)),,15)))
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
R18	Expression	=R18	text	NO
B18:P27	Expression	=COUNTIFS($B18:$P18,B18)>1	text	NO
R5:R14	Expression	=R5	text	NO
B5:P14	Expression	=COUNTIFS($B5:$P5,B5)>1	text	NO

 

[Xlambda]

Needless to say, all ATEXTSPILL formulas can be replaced with the "master" version SPILLBYROWS that deals with any arrays and any functions, not only column vectors.
Full study of SPILLBYROWS and much more, still has to be done whenever I will have enough spare time for it.
ATEXTSPILL could be slightly faster, and for text operations does the job.
Anyhow, there is a way to tweak ATEXTSPILL to do what SPILLBYROWS does. See the example below.

ATEXTSPILL.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1
2			=SPILLBYROWS(A3:A6,LAMBDA(x,TEXTSPLIT(x,{" ",", ",". ",": "},"-",1)))
3	Do. Or do not. There is no try. -Anonymous		Do	Or	do	not	There	is	no	try
4	There are two options: Adapt or die.		Anonymous
5	One cannot step twice in the same river. -Heraclitus		There	are	two	options	Adapt	or	die
6	Whatever you are, be a good one. -Abraham Lincoln		One	cannot	step	twice	in	the	same	river
7			Heraclitus
8			Whatever	you	are	be	a	good	one
9			Abraham	Lincoln
10
11			A tweak to trick ATEXTSPILL to handle 2D arrays is to "feed" entire rows into lambda helper argument.
12			Task. Extracting unique by rows in ascending order
13
14			=RANDARRAY(5,8,1,20,1)
15			14	12	12	20	11	5	14	8
16			5	18	4	17	7	17	15	8
17			11	18	12	8	14	3	16	2
18			15	9	4	17	16	6	7	14
19			1	6	1	3	6	12	17	6
20
21			=SPILLBYROWS(C15#,LAMBDA(x,SORT(UNIQUE(x,1),,,1)))
22			5	8	11	12	14	20
23			4	5	7	8	15	17	18
24			2	3	8	11	12	14	16	18
25			4	6	7	9	14	15	16	17
26			1	3	6	12	17
27
28			=ATEXTSPILL(SEQUENCE(5),LAMBDA(x,LET(y,INDEX(C15#,x,),SORT(UNIQUE(y,1),,,1))))
29			5	8	11	12	14	20
30			4	5	7	8	15	17	18
31			2	3	8	11	12	14	16	18
32			4	6	7	9	14	15	16	17
33			1	3	6	12	17
34
Sheet12

Cell Formulas
Range	Formula
C2,C28,C21,C14	C2	=FORMULATEXT(C3)
C3:J9	C3	=SPILLBYROWS(A3:A6,LAMBDA(x,TEXTSPLIT(x,{" ",", ",". ",": "},"-",1)))
C15:J19	C15	=RANDARRAY(5,8,1,20,1)
C22:J26	C22	=SPILLBYROWS(C15#,LAMBDA(x,SORT(UNIQUE(x,1),,,1)))
C29:J33	C29	=ATEXTSPILL(SEQUENCE(5),LAMBDA(x,LET(y,INDEX(C15#,x,),SORT(UNIQUE(y,1),,,1))))
Dynamic array formulas.

 

[Xlambda]

We have seen how to create random rows without repetitions, this will be about how to randomly generate a sequence of arrays without repetitions.
Task. Out of 12 people, we have to assign random teams of 4 members each for a period of 15 days.
For every 3 consecutive days, each member should have only 1 assignment, and every other 3 days the team distributions have to be changed randomly.
On other words, we have to create 5 random arrays of 3x4 members with no repetitions.
Random names list from a random list generator site: Male Name Generator — A random list of boys' names .

ATEXTSPILL.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1		Copying data from site => single cell list with line feed separator CHAR(10)
2
3		Richard Cruz Calvin Sheldon Yadiel Wade Milo Mark Quinn Bobby Justus Karter
4
5		Spliting as column vector					If we not "inject" the random function					random function inside function argument
6		=TEXTSPLIT(B3,,CHAR(10))			Shuffling the array		inside function argument => same array sequence					recalculates with every iteration
7		↓↓↓	=RANDARRAY(12)		=SORTBY(B8#,C8#)		=ATEXTSPILL(SEQUENCE(5),LAMBDA(x,WRAPCOLS(E8#,3)))					=ATEXTSPILL(SEQUENCE(5),LAMBDA(x,WRAPCOLS(SORTBY(B8#,RANDARRAY(12)),3)))
8		Richard	0.559265		Calvin		Calvin	Yadiel	Richard	Cruz		Cruz	Yadiel	Sheldon	Bobby
9		Cruz	0.930086		Milo		Milo	Bobby	Justus	Sheldon		Mark	Justus	Quinn	Richard
10		Calvin	0.050665		Quinn		Quinn	Wade	Karter	Mark		Wade	Calvin	Karter	Milo
11		Sheldon	0.931163		Yadiel		Calvin	Yadiel	Richard	Cruz		Yadiel	Milo	Bobby	Karter
12		Yadiel	0.283132		Bobby		Milo	Bobby	Justus	Sheldon		Quinn	Wade	Sheldon	Justus
13		Wade	0.505379		Wade		Quinn	Wade	Karter	Mark		Cruz	Richard	Mark	Calvin
14		Milo	0.10434		Richard		Calvin	Yadiel	Richard	Cruz		Bobby	Karter	Wade	Milo
15		Mark	0.945032		Justus		Milo	Bobby	Justus	Sheldon		Richard	Sheldon	Justus	Yadiel
16		Quinn	0.151372		Karter		Quinn	Wade	Karter	Mark		Mark	Quinn	Calvin	Cruz
17		Bobby	0.43663		Cruz		Calvin	Yadiel	Richard	Cruz		Karter	Sheldon	Bobby	Justus
18		Justus	0.649646		Sheldon		Milo	Bobby	Justus	Sheldon		Wade	Mark	Calvin	Quinn
19		Karter	0.665065		Mark		Quinn	Wade	Karter	Mark		Yadiel	Milo	Richard	Cruz
20							Calvin	Yadiel	Richard	Cruz		Quinn	Justus	Yadiel	Mark
21							Milo	Bobby	Justus	Sheldon		Bobby	Richard	Wade	Cruz
22							Quinn	Wade	Karter	Mark		Karter	Milo	Sheldon	Calvin
23
ATS 8

Cell Formulas
Range	Formula
B6	B6	=FORMULATEXT(B8)
C7,E7,G7,L7	C7	=FORMULATEXT(C8)
B8:B19	B8	=TEXTSPLIT(B3,,CHAR(10))
C8:C19	C8	=RANDARRAY(12)
E8:E19	E8	=SORTBY(B8#,C8#)
G8:J22	G8	=ATEXTSPILL(SEQUENCE(5),LAMBDA(x,WRAPCOLS(E8#,3)))
L8:O22	L8	=ATEXTSPILL(SEQUENCE(5),LAMBDA(x,WRAPCOLS(SORTBY(B8#,RANDARRAY(12)),3)))
Dynamic array formulas.

 

[Xlambda]

ATEXTSPILL.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1		Reversed "engineering"
2		Extract unique values every other 3x4 array, ascending order											nr arrays
3													=ROWS(B4:E36)/3
4		Sheldon	Bobby	Calvin	Quinn		embedded lambda ar(i) useful for iteration						11
5		Karter	Cruz	Justus	Bobby		ar(2)
6		Cruz	Milo	Yadiel	Richard		=LAMBDA(i,INDEX(B4:E36,SEQUENCE(3,,3*(i-1)+1),SEQUENCE(,4)))(2)
7		Justus	Richard	Yadiel	Sheldon		Justus	Richard	Yadiel	Sheldon
8		Calvin	Karter	Richard	Cruz		Calvin	Karter	Richard	Cruz
9		Cruz	Bobby	Justus	Bobby		Cruz	Bobby	Justus	Bobby
10		Justus	Milo	Karter	Wade
11		Bobby	Quinn	Calvin	Cruz		ar(4)
12		Sheldon	Yadiel	Mark	Richard		=LAMBDA(i,INDEX(B4:E36,SEQUENCE(3,,3*(i-1)+1),SEQUENCE(,4)))(4)
13		Yadiel	Calvin	Cruz	Richard		Yadiel	Calvin	Cruz	Richard
14		Justus	Sheldon	Justus	Karter		Justus	Sheldon	Justus	Karter
15		Mark	Calvin	Yadiel	Mark		Mark	Calvin	Yadiel	Mark
16		Richard	Justus	Quinn	Cruz
17		Karter	Milo	Calvin	Mark		solution with embedded lambda ar(i)
18		Wade	Yadiel	Sheldon	Bobby		=LET(ra,B4:E36,ar,LAMBDA(i,INDEX(ra,SEQUENCE(3,,3*(i-1)+1),SEQUENCE(,4))),ATEXTSPILL(SEQUENCE(11),LAMBDA(x,SORT(UNIQUE(TOROW(ar(x)),1),,,1))))
19		Calvin	Bobby	Sheldon	Justus		Bobby	Calvin	Cruz	Justus	Karter	Milo	Quinn	Richard	Sheldon	Yadiel
20		Sheldon	Calvin	Milo	Mark		Bobby	Calvin	Cruz	Justus	Karter	Richard	Sheldon	Yadiel
21		Wade	Cruz	Wade	Quinn		Bobby	Calvin	Cruz	Justus	Karter	Mark	Milo	Quinn	Richard	Sheldon	Wade	Yadiel
22		Mark	Sheldon	Justus	Justus		Calvin	Cruz	Justus	Karter	Mark	Richard	Sheldon	Yadiel
23		Quinn	Mark	Milo	Milo		Bobby	Calvin	Cruz	Justus	Karter	Mark	Milo	Quinn	Richard	Sheldon	Wade	Yadiel
24		Quinn	Sheldon	Calvin	Bobby		Bobby	Calvin	Cruz	Justus	Mark	Milo	Quinn	Sheldon	Wade
25		Mark	Karter	Yadiel	Sheldon		Bobby	Calvin	Justus	Mark	Milo	Quinn	Sheldon
26		Cruz	Yadiel	Karter	Yadiel		Bobby	Calvin	Cruz	Justus	Karter	Mark	Sheldon	Wade	Yadiel
27		Bobby	Justus	Wade	Calvin		Calvin	Cruz	Justus	Karter	Mark	Milo	Quinn	Wade	Yadiel
28		Justus	Quinn	Mark	Calvin		Bobby	Calvin	Cruz	Justus	Karter	Mark	Milo	Quinn	Richard	Sheldon	Wade	Yadiel
29		Cruz	Mark	Quinn	Yadiel		Calvin	Karter	Mark	Milo	Quinn	Richard	Sheldon	Wade	Yadiel
30		Mark	Karter	Milo	Wade		or
31		Wade	Mark	Cruz	Quinn		lambda formula
32		Karter	Bobby	Sheldon	Richard		=ATEXTSPILL(SEQUENCE(11),LAMBDA(x,SORT(UNIQUE(TOROW(INDEX(B4:E36,SEQUENCE(3,,3*(x-1)+1),SEQUENCE(,4))),1),,,1)))
33		Justus	Milo	Calvin	Yadiel		Bobby	Calvin	Cruz	Justus	Karter	Milo	Quinn	Richard	Sheldon	Yadiel
34		Karter	Wade	Calvin	Sheldon		Bobby	Calvin	Cruz	Justus	Karter	Richard	Sheldon	Yadiel
35		Sheldon	Karter	Quinn	Milo		Bobby	Calvin	Cruz	Justus	Karter	Mark	Milo	Quinn	Richard	Sheldon	Wade	Yadiel
36		Richard	Mark	Yadiel	Yadiel		Calvin	Cruz	Justus	Karter	Mark	Richard	Sheldon	Yadiel
37							Bobby	Calvin	Cruz	Justus	Karter	Mark	Milo	Quinn	Richard	Sheldon	Wade	Yadiel
38							Bobby	Calvin	Cruz	Justus	Mark	Milo	Quinn	Sheldon	Wade
39							Bobby	Calvin	Justus	Mark	Milo	Quinn	Sheldon
40							Bobby	Calvin	Cruz	Justus	Karter	Mark	Sheldon	Wade	Yadiel
41							Calvin	Cruz	Justus	Karter	Mark	Milo	Quinn	Wade	Yadiel
42							Bobby	Calvin	Cruz	Justus	Karter	Mark	Milo	Quinn	Richard	Sheldon	Wade	Yadiel
43							Calvin	Karter	Mark	Milo	Quinn	Richard	Sheldon	Wade	Yadiel
44
ATS 9

Cell Formulas
Range	Formula
M3,G32,G18,G12,G6	M3	=FORMULATEXT(M4)
M4	M4	=ROWS(B4:E36)/3
G7:J9	G7	=LAMBDA(i,INDEX(B4:E36,SEQUENCE(3,,3*(i-1)+1),SEQUENCE(,4)))(2)
G13:J15	G13	=LAMBDA(i,INDEX(B4:E36,SEQUENCE(3,,3*(i-1)+1),SEQUENCE(,4)))(4)
G19:R29	G19	=LET(ra,B4:E36,ar,LAMBDA(i,INDEX(ra,SEQUENCE(3,,3*(i-1)+1),SEQUENCE(,4))),ATEXTSPILL(SEQUENCE(11),LAMBDA(x,SORT(UNIQUE(TOROW(ar(x)),1),,,1))))
G33:R43	G33	=ATEXTSPILL(SEQUENCE(11),LAMBDA(x,SORT(UNIQUE(TOROW(INDEX(B4:E36,SEQUENCE(3,,3*(x-1)+1),SEQUENCE(,4))),1),,,1)))
Dynamic array formulas.

 

[Xlambda]

ATEXTSPILL.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1		Same results keeping initial format of source array plus adding index array number
2		lambda formula
3	G5:	=ATEXTSPILL(SEQUENCE(11),LAMBDA(x,HSTACK("ar "&x,WRAPROWS(SORT(UNIQUE(TOROW(INDEX(B5:E37,SEQUENCE(3,,3*(x-1)+1),SEQUENCE(,4))),1),,,1),4))))
4
5		Sheldon	Bobby	Calvin	Quinn		ar 1	Bobby	Calvin	Cruz	Justus
6		Karter	Cruz	Justus	Bobby			Karter	Milo	Quinn	Richard
7		Cruz	Milo	Yadiel	Richard			Sheldon	Yadiel
8		Justus	Richard	Yadiel	Sheldon		ar 2	Bobby	Calvin	Cruz	Justus
9		Calvin	Karter	Richard	Cruz			Karter	Richard	Sheldon	Yadiel
10		Cruz	Bobby	Justus	Bobby		ar 3	Bobby	Calvin	Cruz	Justus
11		Justus	Milo	Karter	Wade			Karter	Mark	Milo	Quinn
12		Bobby	Quinn	Calvin	Cruz			Richard	Sheldon	Wade	Yadiel
13		Sheldon	Yadiel	Mark	Richard		ar 4	Calvin	Cruz	Justus	Karter
14		Yadiel	Calvin	Cruz	Richard			Mark	Richard	Sheldon	Yadiel
15		Justus	Sheldon	Justus	Karter		ar 5	Bobby	Calvin	Cruz	Justus
16		Mark	Calvin	Yadiel	Mark			Karter	Mark	Milo	Quinn
17		Richard	Justus	Quinn	Cruz			Richard	Sheldon	Wade	Yadiel
18		Karter	Milo	Calvin	Mark		ar 6	Bobby	Calvin	Cruz	Justus
19		Wade	Yadiel	Sheldon	Bobby			Mark	Milo	Quinn	Sheldon
20		Calvin	Bobby	Sheldon	Justus			Wade
21		Sheldon	Calvin	Milo	Mark		ar 7	Bobby	Calvin	Justus	Mark
22		Wade	Cruz	Wade	Quinn			Milo	Quinn	Sheldon
23		Mark	Sheldon	Justus	Justus		ar 8	Bobby	Calvin	Cruz	Justus
24		Quinn	Mark	Milo	Milo			Karter	Mark	Sheldon	Wade
25		Quinn	Sheldon	Calvin	Bobby			Yadiel
26		Mark	Karter	Yadiel	Sheldon		ar 9	Calvin	Cruz	Justus	Karter
27		Cruz	Yadiel	Karter	Yadiel			Mark	Milo	Quinn	Wade
28		Bobby	Justus	Wade	Calvin			Yadiel
29		Justus	Quinn	Mark	Calvin		ar 10	Bobby	Calvin	Cruz	Justus
30		Cruz	Mark	Quinn	Yadiel			Karter	Mark	Milo	Quinn
31		Mark	Karter	Milo	Wade			Richard	Sheldon	Wade	Yadiel
32		Wade	Mark	Cruz	Quinn		ar 11	Calvin	Karter	Mark	Milo
33		Karter	Bobby	Sheldon	Richard			Quinn	Richard	Sheldon	Wade
34		Justus	Milo	Calvin	Yadiel			Yadiel
35		Karter	Wade	Calvin	Sheldon
36		Sheldon	Karter	Quinn	Milo
37		Richard	Mark	Yadiel	Yadiel
38
ATS 10

Cell Formulas
Range	Formula
B3	B3	=FORMULATEXT(G5)
G5:K34	G5	=ATEXTSPILL(SEQUENCE(11),LAMBDA(x,HSTACK("ar "&x,WRAPROWS(SORT(UNIQUE(TOROW(INDEX(B5:E37,SEQUENCE(3,,3*(x-1)+1),SEQUENCE(,4))),1),,,1),4))))
Dynamic array formulas.

 

[Xlambda]

I know that the last examples were kind of off topic to the text functions but, was fun to show what level of complexity can be accomplished with today's Excel functions environment, and versatility of custom-made lambda helper functions, as product of this fine, and more powerful than never, environment.

ATEXTSPILL.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1		I thought that also adding info about nr. of unique members/each array could be useful
2
3	G5:	=ATEXTSPILL(SEQUENCE(11),LAMBDA(x,LET(u,SORT(UNIQUE(TOROW(INDEX(B5:E37,SEQUENCE(3,,3*(x-1)+1),SEQUENCE(,4))),1),,,1),HSTACK("ar "&x,WRAPROWS(u,4),"cnt "&COLUMNS(u)))))
4
5		Sheldon	Bobby	Calvin	Quinn		ar 1	Bobby	Calvin	Cruz	Justus	cnt 10
6		Karter	Cruz	Justus	Bobby			Karter	Milo	Quinn	Richard
7		Cruz	Milo	Yadiel	Richard			Sheldon	Yadiel
8		Justus	Richard	Yadiel	Sheldon		ar 2	Bobby	Calvin	Cruz	Justus	cnt 8
9		Calvin	Karter	Richard	Cruz			Karter	Richard	Sheldon	Yadiel
10		Cruz	Bobby	Justus	Bobby		ar 3	Bobby	Calvin	Cruz	Justus	cnt 12
11		Justus	Milo	Karter	Wade			Karter	Mark	Milo	Quinn
12		Bobby	Quinn	Calvin	Cruz			Richard	Sheldon	Wade	Yadiel
13		Sheldon	Yadiel	Mark	Richard		ar 4	Calvin	Cruz	Justus	Karter	cnt 8
14		Yadiel	Calvin	Cruz	Richard			Mark	Richard	Sheldon	Yadiel
15		Justus	Sheldon	Justus	Karter		ar 5	Bobby	Calvin	Cruz	Justus	cnt 12
16		Mark	Calvin	Yadiel	Mark			Karter	Mark	Milo	Quinn
17		Richard	Justus	Quinn	Cruz			Richard	Sheldon	Wade	Yadiel
18		Karter	Milo	Calvin	Mark		ar 6	Bobby	Calvin	Cruz	Justus	cnt 9
19		Wade	Yadiel	Sheldon	Bobby			Mark	Milo	Quinn	Sheldon
20		Calvin	Bobby	Sheldon	Justus			Wade
21		Sheldon	Calvin	Milo	Mark		ar 7	Bobby	Calvin	Justus	Mark	cnt 7
22		Wade	Cruz	Wade	Quinn			Milo	Quinn	Sheldon
23		Mark	Sheldon	Justus	Justus		ar 8	Bobby	Calvin	Cruz	Justus	cnt 9
24		Quinn	Mark	Milo	Milo			Karter	Mark	Sheldon	Wade
25		Quinn	Sheldon	Calvin	Bobby			Yadiel
26		Mark	Karter	Yadiel	Sheldon		ar 9	Calvin	Cruz	Justus	Karter	cnt 9
27		Cruz	Yadiel	Karter	Yadiel			Mark	Milo	Quinn	Wade
28		Bobby	Justus	Wade	Calvin			Yadiel
29		Justus	Quinn	Mark	Calvin		ar 10	Bobby	Calvin	Cruz	Justus	cnt 12
30		Cruz	Mark	Quinn	Yadiel			Karter	Mark	Milo	Quinn
31		Mark	Karter	Milo	Wade			Richard	Sheldon	Wade	Yadiel
32		Wade	Mark	Cruz	Quinn		ar 11	Calvin	Karter	Mark	Milo	cnt 9
33		Karter	Bobby	Sheldon	Richard			Quinn	Richard	Sheldon	Wade
34		Justus	Milo	Calvin	Yadiel			Yadiel
35		Karter	Wade	Calvin	Sheldon
36		Sheldon	Karter	Quinn	Milo
37		Richard	Mark	Yadiel	Yadiel
38
ATS 11

Cell Formulas
Range	Formula
B3	B3	=FORMULATEXT(G5)
G5:L34	G5	=ATEXTSPILL(SEQUENCE(11),LAMBDA(x,LET(u,SORT(UNIQUE(TOROW(INDEX(B5:E37,SEQUENCE(3,,3*(x-1)+1),SEQUENCE(,4))),1),,,1),HSTACK("ar "&x,WRAPROWS(u,4),"cnt "&COLUMNS(u)))))
Dynamic array formulas.

 

[Xlambda]

Even more complex, adding individual unique count for each array extraction.
Nested lambda helper MAP inside custom-made lambda helper ATEXTSPILL

ATEXTSPILL.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	Adding info about array index nr., nr. of total unique members plus individual each member count for each array extraction
2	=ATEXTSPILL(SEQUENCE(11),LAMBDA(x,LET(u,SORT(UNIQUE(TOROW(INDEX(B7:E39,SEQUENCE(3,,3*(x-1)+1),SEQUENCE(,4))),1),,,1),HSTACK("ar "&x,WRAPROWS(u,4),"cnt "&COLUMNS(u)))))
3	=ATEXTSPILL(SEQUENCE(11),LAMBDA(x,LET(a,INDEX(B7:E39,SEQUENCE(3,,3*(x-1)+1),SEQUENCE(,4)),u,SORT(UNIQUE(TOROW(a),1),,,1),m,MAP(u,LAMBDA(x,SUM(--(x=a)))),HSTACK("ar "&x,WRAPROWS(u&","&m,4),"cnt "&COLUMNS(u)))))
4
5						array #, extract unique, total unique count							array #, extract unique, individual count, total unique count
6
7	Sheldon	Bobby	Calvin	Quinn		ar 1	Bobby	Calvin	Cruz	Justus	cnt 10		ar 1	Bobby,2	Calvin,1	Cruz,2	Justus,1	cnt 10
8	Karter	Cruz	Justus	Bobby			Karter	Milo	Quinn	Richard				Karter,1	Milo,1	Quinn,1	Richard,1
9	Cruz	Milo	Yadiel	Richard			Sheldon	Yadiel						Sheldon,1	Yadiel,1
10	Justus	Richard	Yadiel	Sheldon		ar 2	Bobby	Calvin	Cruz	Justus	cnt 8		ar 2	Bobby,2	Calvin,1	Cruz,2	Justus,2	cnt 8
11	Calvin	Karter	Richard	Cruz			Karter	Richard	Sheldon	Yadiel				Karter,1	Richard,2	Sheldon,1	Yadiel,1
12	Cruz	Bobby	Justus	Bobby		ar 3	Bobby	Calvin	Cruz	Justus	cnt 12		ar 3	Bobby,1	Calvin,1	Cruz,1	Justus,1	cnt 12
13	Justus	Milo	Karter	Wade			Karter	Mark	Milo	Quinn				Karter,1	Mark,1	Milo,1	Quinn,1
14	Bobby	Quinn	Calvin	Cruz			Richard	Sheldon	Wade	Yadiel				Richard,1	Sheldon,1	Wade,1	Yadiel,1
15	Sheldon	Yadiel	Mark	Richard		ar 4	Calvin	Cruz	Justus	Karter	cnt 8		ar 4	Calvin,2	Cruz,1	Justus,2	Karter,1	cnt 8
16	Yadiel	Calvin	Cruz	Richard			Mark	Richard	Sheldon	Yadiel				Mark,2	Richard,1	Sheldon,1	Yadiel,2
17	Justus	Sheldon	Justus	Karter		ar 5	Bobby	Calvin	Cruz	Justus	cnt 12		ar 5	Bobby,1	Calvin,1	Cruz,1	Justus,1	cnt 12
18	Mark	Calvin	Yadiel	Mark			Karter	Mark	Milo	Quinn				Karter,1	Mark,1	Milo,1	Quinn,1
19	Richard	Justus	Quinn	Cruz			Richard	Sheldon	Wade	Yadiel				Richard,1	Sheldon,1	Wade,1	Yadiel,1
20	Karter	Milo	Calvin	Mark		ar 6	Bobby	Calvin	Cruz	Justus	cnt 9		ar 6	Bobby,1	Calvin,2	Cruz,1	Justus,1	cnt 9
21	Wade	Yadiel	Sheldon	Bobby			Mark	Milo	Quinn	Sheldon				Mark,1	Milo,1	Quinn,1	Sheldon,2
22	Calvin	Bobby	Sheldon	Justus			Wade							Wade,2
23	Sheldon	Calvin	Milo	Mark		ar 7	Bobby	Calvin	Justus	Mark	cnt 7		ar 7	Bobby,1	Calvin,1	Justus,2	Mark,2	cnt 7
24	Wade	Cruz	Wade	Quinn			Milo	Quinn	Sheldon					Milo,2	Quinn,2	Sheldon,2
25	Mark	Sheldon	Justus	Justus		ar 8	Bobby	Calvin	Cruz	Justus	cnt 9		ar 8	Bobby,1	Calvin,1	Cruz,1	Justus,1	cnt 9
26	Quinn	Mark	Milo	Milo			Karter	Mark	Sheldon	Wade				Karter,2	Mark,1	Sheldon,1	Wade,1
27	Quinn	Sheldon	Calvin	Bobby			Yadiel							Yadiel,3
28	Mark	Karter	Yadiel	Sheldon		ar 9	Calvin	Cruz	Justus	Karter	cnt 9		ar 9	Calvin,1	Cruz,1	Justus,1	Karter,1	cnt 9
29	Cruz	Yadiel	Karter	Yadiel			Mark	Milo	Quinn	Wade				Mark,3	Milo,1	Quinn,2	Wade,1
30	Bobby	Justus	Wade	Calvin			Yadiel							Yadiel,1
31	Justus	Quinn	Mark	Calvin		ar 10	Bobby	Calvin	Cruz	Justus	cnt 12		ar 10	Bobby,1	Calvin,1	Cruz,1	Justus,1	cnt 12
32	Cruz	Mark	Quinn	Yadiel			Karter	Mark	Milo	Quinn				Karter,1	Mark,1	Milo,1	Quinn,1
33	Mark	Karter	Milo	Wade			Richard	Sheldon	Wade	Yadiel				Richard,1	Sheldon,1	Wade,1	Yadiel,1
34	Wade	Mark	Cruz	Quinn		ar 11	Calvin	Karter	Mark	Milo	cnt 9		ar 11	Calvin,1	Karter,2	Mark,1	Milo,1	cnt 9
35	Karter	Bobby	Sheldon	Richard			Quinn	Richard	Sheldon	Wade				Quinn,1	Richard,1	Sheldon,2	Wade,1
36	Justus	Milo	Calvin	Yadiel			Yadiel							Yadiel,2
37	Karter	Wade	Calvin	Sheldon
38	Sheldon	Karter	Quinn	Milo
39	Richard	Mark	Yadiel	Yadiel
40
ATS 11

Cell Formulas
Range	Formula
B2	B2	=FORMULATEXT(G7)
B3	B3	=FORMULATEXT(N7)
G7:L36	G7	=ATEXTSPILL(SEQUENCE(11),LAMBDA(x,LET(u,SORT(UNIQUE(TOROW(INDEX(B7:E39,SEQUENCE(3,,3*(x-1)+1),SEQUENCE(,4))),1),,,1),HSTACK("ar "&x,WRAPROWS(u,4),"cnt "&COLUMNS(u)))))
N7:S36	N7	=ATEXTSPILL(SEQUENCE(11),LAMBDA(x,LET(a,INDEX(B7:E39,SEQUENCE(3,,3*(x-1)+1),SEQUENCE(,4)),u,SORT(UNIQUE(TOROW(a),1),,,1),m,MAP(u,LAMBDA(x,SUM(--(x=a)))),HSTACK("ar "&x,WRAPROWS(u&","&m,4),"cnt "&COLUMNS(u)))))
Dynamic array formulas.

 

[Xlambda]

It's Sunday, let's have some fun.
Deal a shuffled deck of cards in less than 1 min.
Note: Covered this before AXMAS thread post #11. At that time, I used custom-made lambdas like AFLAT, ARESIZE, now we have TOROW/TOCOL, WRAPCOLS/WRAPROWS.

ATEXTSPILL.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE
1		unicodes	suits
2		9824	♠		Deal a shuffled deck of cards in less than 1 min															CF formula entire spreadsheet:
3		9827	♣																	=OR(UNICODE(RIGHT(A1,1))=9829,UNICODE(RIGHT(A1,1))=9830)
4		9829	♥		=SEQUENCE(,13,2)+{0;0;0;0}
5		9830	♦		2	3	4	5	6	7	8	9	10	11	12	13	14
6					2	3	4	5	6	7	8	9	10	11	12	13	14
7					2	3	4	5	6	7	8	9	10	11	12	13	14
8					2	3	4	5	6	7	8	9	10	11	12	13	14
9
10					=SWITCH(E5#,11,"A",12,"J",13,"Q",14,"K",E5#)
11					2	3	4	5	6	7	8	9	10	A	J	Q	K
12					2	3	4	5	6	7	8	9	10	A	J	Q	K
13					2	3	4	5	6	7	8	9	10	A	J	Q	K
14					2	3	4	5	6	7	8	9	10	A	J	Q	K
15
16					=E11#&C2#
17					2♠	3♠	4♠	5♠	6♠	7♠	8♠	9♠	10♠	A♠	J♠	Q♠	K♠
18					2♣	3♣	4♣	5♣	6♣	7♣	8♣	9♣	10♣	A♣	J♣	Q♣	K♣
19					2♥	3♥	4♥	5♥	6♥	7♥	8♥	9♥	10♥	A♥	J♥	Q♥	K♥
20					2♦	3♦	4♦	5♦	6♦	7♦	8♦	9♦	10♦	A♦	J♦	Q♦	K♦
21
22					deck in order
23					=TOROW(E17#)
24					2♠	3♠	4♠	5♠	6♠	7♠	8♠	9♠	10♠	A♠	J♠	Q♠	K♠	2♣	3♣	4♣	5♣	6♣	7♣	8♣	9♣	10♣	A♣	J♣	Q♣	K♣	2♥	3♥	4♥	5♥	6♥	7♥	8♥	9♥	10♥	A♥	J♥	Q♥	K♥	2♦	3♦	4♦	5♦	6♦	7♦	8♦	9♦	10♦	A♦	J♦	Q♦	K♦
25
26					shuffling (no duplicates)
27					=SORTBY(E24#,RANDARRAY(,52))
28					5♣	A♥	7♠	Q♦	6♣	K♣	6♦	5♦	8♦	4♣	4♥	10♥	9♦	5♥	J♣	A♦	6♥	2♦	3♠	8♣	J♥	3♥	7♣	A♠	K♠	3♦	8♥	6♠	8♠	K♦	J♠	9♠	A♣	Q♠	2♠	J♦	10♦	Q♣	3♣	9♥	4♦	9♣	5♠	Q♥	K♥	10♣	2♥	10♠	4♠	7♥	2♣	7♦
29
30					deal the whole deck 5 cards/round(hand)										7 cards									13 cards														checking no dups
31					=IFNA(WRAPROWS(E28#,5),"")										=IFNA(WRAPROWS(E28#,7),"")									=IFNA(WRAPROWS(E28#,13),"")														=COUNTIF(X32#,X32#)
32					5♣	A♥	7♠	Q♦	6♣						5♣	A♥	7♠	Q♦	6♣	K♣	6♦			5♣	A♥	7♠	Q♦	6♣	K♣	6♦	5♦	8♦	4♣	4♥	10♥	9♦		1	1	1	1	1	1	1	1	1	1	1	1	1
33					K♣	6♦	5♦	8♦	4♣						5♦	8♦	4♣	4♥	10♥	9♦	5♥			5♥	J♣	A♦	6♥	2♦	3♠	8♣	J♥	3♥	7♣	A♠	K♠	3♦		1	1	1	1	1	1	1	1	1	1	1	1	1
34					4♥	10♥	9♦	5♥	J♣						J♣	A♦	6♥	2♦	3♠	8♣	J♥			8♥	6♠	8♠	K♦	J♠	9♠	A♣	Q♠	2♠	J♦	10♦	Q♣	3♣		1	1	1	1	1	1	1	1	1	1	1	1	1
35					A♦	6♥	2♦	3♠	8♣						3♥	7♣	A♠	K♠	3♦	8♥	6♠			9♥	4♦	9♣	5♠	Q♥	K♥	10♣	2♥	10♠	4♠	7♥	2♣	7♦		1	1	1	1	1	1	1	1	1	1	1	1	1
36					J♥	3♥	7♣	A♠	K♠						8♠	K♦	J♠	9♠	A♣	Q♠	2♠
37					3♦	8♥	6♠	8♠	K♦						J♦	10♦	Q♣	3♣	9♥	4♦	9♣
38					J♠	9♠	A♣	Q♠	2♠						5♠	Q♥	K♥	10♣	2♥	10♠	4♠
39					J♦	10♦	Q♣	3♣	9♥						7♥	2♣	7♦
40					4♦	9♣	5♠	Q♥	K♥
41					10♣	2♥	10♠	4♠	7♥
42					2♣	7♦
43
DECK 1

Cell Formulas
Range	Formula
C2:C5	C2	=UNICHAR(B2:B5)
E4,AL31,X31,O31,E31,E27,E23,E16,E10	E4	=FORMULATEXT(E5)
E5:Q8	E5	=SEQUENCE(,13,2)+{0;0;0;0}
E11:Q14	E11	=SWITCH(E5#,11,"A",12,"J",13,"Q",14,"K",E5#)
E17:Q20	E17	=E11#&C2#
E24:BD24	E24	=TOROW(E17#)
E28:BD28	E28	=SORTBY(E24#,RANDARRAY(,52))
E32:I42	E32	=IFNA(WRAPROWS(E28#,5),"")
O32:U39	O32	=IFNA(WRAPROWS(E28#,7),"")
X32:AJ35	X32	=IFNA(WRAPROWS(E28#,13),"")
AL32:AX35	AL32	=COUNTIF(X32#,X32#)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
1:1048576	Expression	=OR(UNICODE(RIGHT(A1,1))=9829,UNICODE(RIGHT(A1,1))=9830)	text	NO

 

[Xlambda]

Shuffling n decks of cards with ATEXTSPILL
DECK() argument free lambda, returns a deck of cards in order (row orientation)

=LAMBDA(
    LET(
        s, SEQUENCE(, 13, 2) + {0; 0; 0; 0},
        TOROW(SWITCH(s, 11, "A", 12, "J", 13, "Q", 14, "K", s) & {"♠"; "♣"; "♥"; "♦"})
    )
)

NDECK([n]) shuffles n decks of cards, using ATEXTSPILL as shuffling "engine", n rows, each row a shuffled deck. Also calls DECK()
[n]: nr. of decks, integer
- if n omitted or 0 => single deck in order
- if n<0 => n=1 => single deck shuffled
- if n>0 => n => n decks shuffled, each row a shuffled deck, (no dups/row)

=LAMBDA([n],
    IF(
        n,
        ATEXTSPILL(SEQUENCE(MAX(1, n)), LAMBDA(x, SORTBY(DECK(), RANDARRAY(, 52)))),
        DECK()
    )
)

ATEXTSPILL.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB
1
2		=DECK()
3		2♠	3♠	4♠	5♠	6♠	7♠	8♠	9♠	10♠	A♠	J♠	Q♠	K♠	2♣	3♣	4♣	5♣	6♣	7♣	8♣	9♣	10♣	A♣	J♣	Q♣	K♣	2♥	3♥	4♥	5♥	6♥	7♥	8♥	9♥	10♥	A♥	J♥	Q♥	K♥	2♦	3♦	4♦	5♦	6♦	7♦	8♦	9♦	10♦	A♦	J♦	Q♦	K♦
4
5		n,omitted => single deck in order (row)
6		=NDECK()
7		2♠	3♠	4♠	5♠	6♠	7♠	8♠	9♠	10♠	A♠	J♠	Q♠	K♠	2♣	3♣	4♣	5♣	6♣	7♣	8♣	9♣	10♣	A♣	J♣	Q♣	K♣	2♥	3♥	4♥	5♥	6♥	7♥	8♥	9♥	10♥	A♥	J♥	Q♥	K♥	2♦	3♦	4♦	5♦	6♦	7♦	8♦	9♦	10♦	A♦	J♦	Q♦	K♦
8
9		n <0 => n=1
10		=NDECK(-3)
11		Q♠	K♠	8♠	9♠	6♠	A♠	10♠	3♠	10♦	5♠	6♥	6♣	4♠	J♠	8♦	4♥	2♣	10♣	9♥	5♥	A♣	4♣	J♥	Q♥	10♥	3♥	K♣	4♦	A♦	5♣	8♣	2♠	7♣	2♦	A♥	8♥	J♦	7♦	6♦	9♣	9♦	Q♣	K♥	3♣	7♠	2♥	5♦	J♣	7♥	3♦	K♦	Q♦
12
13		n,1, => 1 deck shuffled
14		=NDECK(1)
15		8♥	8♦	5♠	9♥	4♣	5♣	2♠	3♥	5♦	5♥	9♠	K♥	3♠	J♠	6♦	Q♥	6♥	7♥	8♠	J♦	A♦	J♣	Q♣	9♦	8♣	A♠	4♥	7♦	A♣	10♥	3♣	4♠	2♣	J♥	6♣	6♠	K♠	9♣	Q♠	7♣	10♣	Q♦	2♥	2♦	4♦	K♦	3♦	A♥	10♦	10♠	K♣	7♠
16
17		n,>0 => n decks shuffled, each row represent a separate shuffled deck => no dups /row
18		=NDECK(3)
19		9♠	8♦	2♦	2♠	6♦	3♥	8♥	K♣	5♥	K♦	10♠	7♦	3♠	4♠	9♦	Q♣	3♣	J♣	4♦	6♣	4♥	6♠	Q♦	2♣	K♥	2♥	A♦	5♠	J♠	4♣	Q♥	10♣	9♥	5♣	K♠	6♥	9♣	A♥	A♣	5♦	A♠	8♣	8♠	Q♠	J♥	7♥	3♦	10♥	J♦	7♣	10♦	7♠
20		10♥	6♥	2♦	Q♥	A♥	8♣	6♦	2♠	3♣	J♥	7♠	10♣	3♦	2♣	8♦	Q♣	A♣	J♠	4♣	7♦	K♣	5♦	Q♠	A♦	9♥	5♠	3♥	6♣	9♦	5♥	10♦	K♥	3♠	7♣	4♦	K♠	J♣	8♥	4♠	9♠	J♦	K♦	8♠	A♠	6♠	7♥	9♣	4♥	5♣	2♥	Q♦	10♠
21		6♠	Q♠	3♠	Q♥	9♠	2♣	J♠	5♦	4♠	K♦	10♥	2♥	10♦	K♠	4♥	7♠	J♦	5♥	6♣	8♦	6♥	A♣	4♣	7♣	Q♣	8♣	9♦	J♥	A♦	3♦	9♥	K♥	10♣	Q♦	J♣	A♥	K♣	7♥	2♠	6♦	7♦	5♠	3♣	10♠	4♦	3♥	8♥	8♠	A♠	9♣	5♣	2♦
22
23		checking dups/row. If all 1 => no dups/row
24		B21:B23		=COUNTIF(B19:BA19,DECK())
25		1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
26		1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
27		1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
28
DECK 2

Cell Formulas
Range	Formula
B2,B18,B14,B10,B6	B2	=FORMULATEXT(B3)
B3:BA3	B3	=DECK()
B7:BA7	B7	=NDECK()
B11:BA11	B11	=NDECK(-3)
B15:BA15	B15	=NDECK(1)
B19:BA21	B19	=NDECK(3)
D24	D24	=FORMULATEXT(B25)
B25:BA27	B25	=COUNTIF(B19:BA19,DECK())
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
1:1048576	Expression	=OR(UNICODE(RIGHT(A1,1))=9829,UNICODE(RIGHT(A1,1))=9830)	text	NO