ASCAN

[Xlambda]

ASCAN, Array SCAN, 3 in 1 function, SCAN by row, by column, by array. This is my take of tboulden's SCANBYROW/BYCOL.
Uses only new!! SCAN lambda helper function, no need of byrow,bycol,makearray, or lambda as arguments.

=LAMBDA(a,[d],
    LET(y,IF(d=1,TRANSPOSE(a),a),s,SCAN(0,y,LAMBDA(v,a,v+a)),r,ROWS(s),c,COLUMNS(s),sr,SEQUENCE(r)-1,
       x,s-IF(d,IFERROR(INDEX(INDEX(s,,c),sr)*sr^0,0),0),
       IF(d=1,TRANSPOSE(x),x)
   )
)

LAMBDA 1.1.3.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1						d,omitted (by array)					d,-1 (by rows)					d,1 (by clms)
2	sample 1			a		=ASCAN(A3:D6)					=ASCAN(A3:D6,-1)					=ASCAN(A3:D6,1)
3	1	2	3	4		1	3	6	10		1	3	6	10		1	2	3	4
4	5	6	7	8		15	21	28	36		5	11	18	26		6	8	10	12
5	9	10	11	12		45	55	66	78		9	19	30	42		15	18	21	24
6	13	14	15	16		91	105	120	136		13	27	42	58		28	32	36	40
7
8						d,omitted (by array)					d,-1 (by rows)					d,1 (by clms)
9	sample 2			a		=ASCAN(A10#)					=ASCAN(A10#,-1)					=ASCAN(A10#,1)
10	1	2	3	4		1	3	6	10		1	3	6	10		1	2	3	4
11	5	6	7	8		15	21	28	36		5	11	18	26		6	8	10	12
12	9	10	11	12		45	55	66	78		9	19	30	42		15	18	21	24
13	13	14	15	16		91	105	120	136		13	27	42	58		28	32	36	40
14	17	18	19	20		153	171	190	210		17	35	54	74		45	50	55	60
15	21	22	23	24		231	253	276	300		21	43	66	90		66	72	78	84
16
17	Note: Did not set an extra argument for any kind of "initial value", since the only thing it does is too simple,
18	adds a constant value to the final outcome , like in 10+ASCAN(a)
19
ASCAN post

Cell Formulas
Range	Formula
F2,P9,K9,F9,P2,K2	F2	=FORMULATEXT(F3)
F3:I6	F3	=ASCAN(A3:D6)
K3:N6	K3	=ASCAN(A3:D6,-1)
P3:S6	P3	=ASCAN(A3:D6,1)
A10:D15	A10	=SEQUENCE(6,4)
F10:I15	F10	=ASCAN(A10#)
K10:N15	K10	=ASCAN(A10#,-1)
P10:S15	P10	=ASCAN(A10#,1)
Dynamic array formulas.

 

[Xlambda]

Extra challenge: Extract only dups and nr. of dups for each row.

EMT1793.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2		Data1	Data2	Data3	Data4	Data5	Data6	Data7	Data8	Data9
3		60	14	91	32	28	19	70	92	92		2,92
4		23	88	35	1	49	31	64	38	86
5		Fred	Chin	Chantel	Miki	Chantel	Jeno	Tyrone	Tyrone	Lim		2,Chantel	2,Tyrone
6		Fred	Chin	Chantel	Miki	Dino	Jeno	Biggy	Tyrone	Lim
7		Quad	Quad	MB	Carlota	Quad	Sunshine	Aspen	Carlota	Yanaki		3,Quad	2,Carlota
8		Qad	Carlota	Sunshine	Aspen	Yanaki	Sunset	Sunspot	Bellen	MB
9		19	63	70	22	5	29	45	84	41
10		51	88	52	18	41	75	13	80	47
11		29	85	70	17	81	32	84	83	98
12		80	12	26	50	88	62	27	15	25
13		27	62	66	59	38	97	13	84	57
14		24	43	40	29	87	21	99	26	19
15		95	37	24	90	10	1	43	47	1		2,1
16		90	11	65	78	20	33	65	49	97		2,65
17		13	24	46	29	23	38	38	73	71		2,38
18		73	97	61	91	29	3	74	11	23
19		99	2	71	95	85	31	6	24	48
20		76	62	28	21	46	62	73	22	14		2,62
21		87	41	41	16	70	74	76	69	71		2,41
22		95	50	69	25	93	50	28	50	39		3,50
23		11	90	63	17	20	93	48	86	90		2,90
24		92	53	32	19	72	15	14	3	67
25
26		Formulas range L3:L24, a formula for each row
27		=LET(x,B3:J3,u,UNIQUE(FILTER(x,ISNA(XMATCH(x,UNIQUE(x,1,1))),""),1),m,MAP(u,LAMBDA(y,SUM(--(y=x)))),IF(m,m&","&u,""))
28
1793 (3)

Cell Formulas
Range	Formula
L3:L4,L6,L8:L24,L7:M7,L5:M5	L3	=LET(x,B3:J3,u,UNIQUE(FILTER(x,ISNA(XMATCH(x,UNIQUE(x,1,1))),""),1),m,MAP(u,LAMBDA(y,SUM(--(y=x)))),IF(m,m&","&u,""))
B27	B27	=FORMULATEXT(L3)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:J24	Expression	=COUNTIFS($B3:$J3,B3)>1	text	NO

 

[Xlambda]

If we can write a formula for each row, no matter how complex it looks, we can use the same construction as the lambda helper part in SPILLBYROWS => a single cell formula that spills.

EMT1793.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1
2		Data1	Data2	Data3	Data4	Data5	Data6	Data7	Data8	Data9
3		60	14	91	32	28	19	70	92	92		2,92
4		23	88	35	1	49	31	64	38	86
5		Fred	Chin	Chantel	Miki	Chantel	Jeno	Tyrone	Tyrone	Lim		2,Chantel	2,Tyrone
6		Fred	Chin	Chantel	Miki	Dino	Jeno	Biggy	Tyrone	Lim
7		Quad	Quad	MB	Carlota	Quad	Sunshine	Aspen	Carlota	Yanaki		3,Quad	2,Carlota
8		Qad	Carlota	Sunshine	Aspen	Yanaki	Sunset	Sunspot	Bellen	MB
9		19	63	70	22	5	29	45	84	41
10		51	88	52	18	41	75	13	80	47
11		29	85	70	17	81	32	84	83	98
12		80	12	26	50	88	62	27	15	25
13		27	62	66	59	38	97	13	84	57
14		24	43	40	29	87	21	99	26	19
15		95	37	24	90	10	1	43	47	1		2,1
16		90	11	65	78	20	33	65	49	97		2,65
17		13	24	46	29	23	38	38	73	71		2,38
18		73	97	61	91	29	3	74	11	23
19		99	2	71	95	85	31	6	24	48
20		76	62	28	21	46	62	73	22	14		2,62
21		87	41	41	16	70	74	76	69	71		2,41
22		95	50	69	25	93	50	28	50	39		3,50
23		11	90	63	17	20	93	48	86	90		2,90
24		92	53	32	19	72	15	14	3	67
25
26		Single cell formula L3:
27		=SPILLBYROWS(B3:J24,LAMBDA(x,LET(u,UNIQUE(FILTER(x,ISNA(XMATCH(x,UNIQUE(x,1,1))),""),1),m,MAP(u,LAMBDA(y,SUM(--(y=x)))),IF(m,m&","&u,""))))
28
1793 (3)

Cell Formulas
Range	Formula
L3:M24	L3	=SPILLBYROWS(B3:J24,LAMBDA(x,LET(u,UNIQUE(FILTER(x,ISNA(XMATCH(x,UNIQUE(x,1,1))),""),1),m,MAP(u,LAMBDA(y,SUM(--(y=x)))),IF(m,m&","&u,""))))
B27	B27	=FORMULATEXT(L3)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:J24	Expression	=COUNTIFS($B3:$J3,B3)>1	text	NO

 

[Xlambda]

To do the same things using a construction BYROW(a,LAMBDA(x,MAP(UNIQUE(x,1),LAMBDA(y, .......
Because BYROW cannot spill we have to use TEXTJOIN

EMT1793.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1
2		Data1	Data2	Data3	Data4	Data5	Data6	Data7	Data8	Data9
3		60	14	91	32	28	19	70	92	92		2,92
4		23	88	35	1	49	31	64	38	86
5		Fred	Chin	Chantel	Miki	Chantel	Jeno	Tyrone	Tyrone	Lim		2,Chantel;2,Tyrone
6		Fred	Chin	Chantel	Miki	Dino	Jeno	Biggy	Tyrone	Lim
7		Quad	Quad	MB	Carlota	Quad	Sunshine	Aspen	Carlota	Yanaki		3,Quad;2,Carlota
8		Qad	Carlota	Sunshine	Aspen	Yanaki	Sunset	Sunspot	Bellen	MB
9		19	63	70	22	5	29	45	84	41
10		51	88	52	18	41	75	13	80	47
11		29	85	70	17	81	32	84	83	98
12		80	12	26	50	88	62	27	15	25
13		27	62	66	59	38	97	13	84	57
14		24	43	40	29	87	21	99	26	19
15		95	37	24	90	10	1	43	47	1		2,1
16		90	11	65	78	20	33	65	49	97		2,65
17		13	24	46	29	23	38	38	73	71		2,38
18		73	97	61	91	29	3	74	11	23
19		99	2	71	95	85	31	6	24	48
20		76	62	28	21	46	62	73	22	14		2,62
21		87	41	41	16	70	74	76	69	71		2,41
22		95	50	69	25	93	50	28	50	39		3,50
23		11	90	63	17	20	93	48	86	90		2,90
24		92	53	32	19	72	15	14	3	67
25
26		Single cell formula L3:
27		=LET(a,B3:J24,BYROW(a,LAMBDA(x,LET(m,MAP(UNIQUE(x,1),LAMBDA(y,LET(z,SUM(--(y=x)),a,z>1,b,FILTER(z,a,""),c,FILTER(y,a,""),r,b&","&c,r))),TEXTJOIN(";",,FILTER(m,m<>",",""))))))
28
1793 (3)

Cell Formulas
Range	Formula
L3:L24	L3	=LET(a,B3:J24,BYROW(a,LAMBDA(x,LET(m,MAP(UNIQUE(x,1),LAMBDA(y,LET(z,SUM(--(y=x)),a,z>1,b,FILTER(z,a,""),c,FILTER(y,a,""),r,b&","&c,r))),TEXTJOIN(";",,FILTER(m,m<>",",""))))))
B27	B27	=FORMULATEXT(L3)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:J24	Expression	=COUNTIFS($B3:$J3,B3)>1	text	NO

 

[Xlambda]

Extracting items byrow that appear exactly once, ascending order.

EMT1793.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1
2		Data1	Data2	Data3	Data4	Data5	Data6	Data7	Data8	Data9		=SPILLBYROWS(B3:J24,LAMBDA(x,SORT(UNIQUE(x,1,1),,,1)))
3		60	14	91	32	28	19	70	92	92		14	19	28	32	60	70	91
4		23	88	35	1	49	31	64	38	86		1	23	31	35	38	49	64	86	88
5		Fred	Chin	Chantel	Miki	Chantel	Jeno	Tyrone	Tyrone	Lim		Chin	Fred	Jeno	Lim	Miki
6		Fred	Chin	Chantel	Miki	Dino	Jeno	Biggy	Tyrone	Lim		Biggy	Chantel	Chin	Dino	Fred	Jeno	Lim	Miki	Tyrone
7		Quad	Quad	MB	Carlota	Quad	Sunshine	Aspen	Carlota	Yanaki		Aspen	MB	Sunshine	Yanaki
8		Qad	Carlota	Sunshine	Aspen	Yanaki	Sunset	Sunspot	Bellen	MB		Aspen	Bellen	Carlota	MB	Qad	Sunset	Sunshine	Sunspot	Yanaki
9		19	63	70	22	5	29	45	84	41		5	19	22	29	41	45	63	70	84
10		51	88	52	18	41	75	13	80	47		13	18	41	47	51	52	75	80	88
11		29	85	70	17	81	32	84	83	98		17	29	32	70	81	83	84	85	98
12		80	12	26	50	88	62	27	15	25		12	15	25	26	27	50	62	80	88
13		27	62	66	59	38	97	13	84	57		13	27	38	57	59	62	66	84	97
14		24	43	40	29	87	21	99	26	19		19	21	24	26	29	40	43	87	99
15		95	37	24	90	10	1	43	47	1		10	24	37	43	47	90	95
16		90	11	65	78	20	33	65	49	97		11	20	33	49	78	90	97
17		13	24	46	29	23	38	38	73	71		13	23	24	29	46	71	73
18		73	97	61	91	29	3	74	11	23		3	11	23	29	61	73	74	91	97
19		99	2	71	95	85	31	6	24	48		2	6	24	31	48	71	85	95	99
20		76	62	28	21	46	62	73	22	14		14	21	22	28	46	73	76
21		87	41	41	16	70	74	76	69	71		16	69	70	71	74	76	87
22		95	50	69	25	93	50	28	50	39		25	28	39	69	93	95
23		11	90	63	17	20	93	48	86	90		11	17	20	48	63	86	93
24		92	53	32	19	72	15	14	3	67		3	14	15	19	32	53	67	72	92
25
1793 (3)

Cell Formulas
Range	Formula
L2	L2	=FORMULATEXT(L3)
L3:T24	L3	=SPILLBYROWS(B3:J24,LAMBDA(x,SORT(UNIQUE(x,1,1),,,1)))
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:J25	Expression	=COUNTIFS($B3:$J3,B3)>1	text	NO

 

[Xlambda]

Another formula that extracts unique byrow and spills, not as elegant and not good for large arrays because textjoin in limited in chars joining, but works:

EMT1793.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
1
2		Data1	Data2	Data3	Data4	Data5	Data6	Data7	Data8	Data9		=IFNA(TEXTSPLIT(TEXTJOIN(";",,BYROW(B3:J24,LAMBDA(x,TEXTJOIN(",",,SORT(UNIQUE(x,1),,,1))))),",",";"),"")
3		60	14	91	32	28	19	70	92	92		14	19	28	32	60	70	91	92
4		23	88	35	1	49	31	64	38	86		1	23	31	35	38	49	64	86	88
5		Fred	Chin	Chantel	Miki	Chantel	Jeno	Tyrone	Tyrone	Lim		Chantel	Chin	Fred	Jeno	Lim	Miki	Tyrone
6		Fred	Chin	Chantel	Miki	Dino	Jeno	Biggy	Tyrone	Lim		Biggy	Chantel	Chin	Dino	Fred	Jeno	Lim	Miki	Tyrone
7		Quad	Quad	MB	Carlota	Quad	Sunshine	Aspen	Carlota	Yanaki		Aspen	Carlota	MB	Quad	Sunshine	Yanaki
8		Qad	Carlota	Sunshine	Aspen	Yanaki	Sunset	Sunspot	Bellen	MB		Aspen	Bellen	Carlota	MB	Qad	Sunset	Sunshine	Sunspot	Yanaki
9		19	63	70	22	5	29	45	84	41		5	19	22	29	41	45	63	70	84
10		51	88	52	18	41	75	13	80	47		13	18	41	47	51	52	75	80	88
11		29	85	70	17	81	32	84	83	98		17	29	32	70	81	83	84	85	98
12		80	12	26	50	88	62	27	15	25		12	15	25	26	27	50	62	80	88
13		27	62	66	59	38	97	13	84	57		13	27	38	57	59	62	66	84	97
14		24	43	40	29	87	21	99	26	19		19	21	24	26	29	40	43	87	99
15		95	37	24	90	10	1	43	47	1		1	10	24	37	43	47	90	95
16		90	11	65	78	20	33	65	49	97		11	20	33	49	65	78	90	97
17		13	24	46	29	23	38	38	73	71		13	23	24	29	38	46	71	73
18		73	97	61	91	29	3	74	11	23		3	11	23	29	61	73	74	91	97
19		99	2	71	95	85	31	6	24	48		2	6	24	31	48	71	85	95	99
20		76	62	28	21	46	62	73	22	14		14	21	22	28	46	62	73	76
21		87	41	41	16	70	74	76	69	71		16	41	69	70	71	74	76	87
22		95	50	69	25	93	50	28	50	39		25	28	39	50	69	93	95
23		11	90	63	17	20	93	48	86	90		11	17	20	48	63	86	90	93
24		92	53	32	19	72	15	14	3	67		3	14	15	19	32	53	67	72	92
25
1793 (4)

Cell Formulas
Range	Formula
L2	L2	=FORMULATEXT(L3)
L3:T24	L3	=IFNA(TEXTSPLIT(TEXTJOIN(";",,BYROW(B3:J24,LAMBDA(x,TEXTJOIN(",",,SORT(UNIQUE(x,1),,,1))))),",",";"),"")
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:J25	Expression	=COUNTIFS($B3:$J3,B3)>1	text	NO

 

[Xlambda]

To overcome the limitation of textjoining the whole array in a single cell we can use ATEXTSPLIT, a function that splits each row "analogical"

EMT1793.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												step 1		step 2
2		Data1	Data2	Data3	Data4	Data5	Data6	Data7	Data8	Data9		=BYROW(B3:J24,LAMBDA(x,TEXTJOIN(",",,SORT(UNIQUE(x,1),,,1))))		=ATEXTSPLIT(L3#,",")
3		60	14	91	32	28	19	70	92	92		14,19,28,32,60,70,91,92		14	19	28	32	60	70	91	92
4		23	88	35	1	49	31	64	38	86		1,23,31,35,38,49,64,86,88		1	23	31	35	38	49	64	86	88
5		Fred	Chin	Chantel	Miki	Chantel	Jeno	Tyrone	Tyrone	Lim		Chantel,Chin,Fred,Jeno,Lim,Miki,Tyrone		Chantel	Chin	Fred	Jeno	Lim	Miki	Tyrone
6		Fred	Chin	Chantel	Miki	Dino	Jeno	Biggy	Tyrone	Lim		Biggy,Chantel,Chin,Dino,Fred,Jeno,Lim,Miki,Tyrone		Biggy	Chantel	Chin	Dino	Fred	Jeno	Lim	Miki	Tyrone
7		Quad	Quad	MB	Carlota	Quad	Sunshine	Aspen	Carlota	Yanaki		Aspen,Carlota,MB,Quad,Sunshine,Yanaki		Aspen	Carlota	MB	Quad	Sunshine	Yanaki
8		Qad	Carlota	Sunshine	Aspen	Yanaki	Sunset	Sunspot	Bellen	MB		Aspen,Bellen,Carlota,MB,Qad,Sunset,Sunshine,Sunspot,Yanaki		Aspen	Bellen	Carlota	MB	Qad	Sunset	Sunshine	Sunspot	Yanaki
9		19	63	70	22	5	29	45	84	41		5,19,22,29,41,45,63,70,84		5	19	22	29	41	45	63	70	84
10		51	88	52	18	41	75	13	80	47		13,18,41,47,51,52,75,80,88		13	18	41	47	51	52	75	80	88
11		29	85	70	17	81	32	84	83	98		17,29,32,70,81,83,84,85,98		17	29	32	70	81	83	84	85	98
12		80	12	26	50	88	62	27	15	25		12,15,25,26,27,50,62,80,88		12	15	25	26	27	50	62	80	88
13		27	62	66	59	38	97	13	84	57		13,27,38,57,59,62,66,84,97		13	27	38	57	59	62	66	84	97
14		24	43	40	29	87	21	99	26	19		19,21,24,26,29,40,43,87,99		19	21	24	26	29	40	43	87	99
15		95	37	24	90	10	1	43	47	1		1,10,24,37,43,47,90,95		1	10	24	37	43	47	90	95
16		90	11	65	78	20	33	65	49	97		11,20,33,49,65,78,90,97		11	20	33	49	65	78	90	97
17		13	24	46	29	23	38	38	73	71		13,23,24,29,38,46,71,73		13	23	24	29	38	46	71	73
18		73	97	61	91	29	3	74	11	23		3,11,23,29,61,73,74,91,97		3	11	23	29	61	73	74	91	97
19		99	2	71	95	85	31	6	24	48		2,6,24,31,48,71,85,95,99		2	6	24	31	48	71	85	95	99
20		76	62	28	21	46	62	73	22	14		14,21,22,28,46,62,73,76		14	21	22	28	46	62	73	76
21		87	41	41	16	70	74	76	69	71		16,41,69,70,71,74,76,87		16	41	69	70	71	74	76	87
22		95	50	69	25	93	50	28	50	39		25,28,39,50,69,93,95		25	28	39	50	69	93	95
23		11	90	63	17	20	93	48	86	90		11,17,20,48,63,86,90,93		11	17	20	48	63	86	90	93
24		92	53	32	19	72	15	14	3	67		3,14,15,19,32,53,67,72,92		3	14	15	19	32	53	67	72	92
25
26		Extracting unique byrow ascending order												Extracting unique that appear only once, byrow, ascending order
27		single cell												single cell
28		=ATEXTSPLIT(BYROW(B3:J24,LAMBDA(x,TEXTJOIN(",",,SORT(UNIQUE(x,1),,,1)))),",")												=ATEXTSPLIT(BYROW(B3:J24,LAMBDA(x,TEXTJOIN(",",,SORT(UNIQUE(x,1,1),,,1)))),",")
29		14	19	28	32	60	70	91	92					14	19	28	32	60	70	91
30		1	23	31	35	38	49	64	86	88				1	23	31	35	38	49	64	86	88
31		Chantel	Chin	Fred	Jeno	Lim	Miki	Tyrone						Chin	Fred	Jeno	Lim	Miki
32		Biggy	Chantel	Chin	Dino	Fred	Jeno	Lim	Miki	Tyrone				Biggy	Chantel	Chin	Dino	Fred	Jeno	Lim	Miki	Tyrone
33		Aspen	Carlota	MB	Quad	Sunshine	Yanaki							Aspen	MB	Sunshine	Yanaki
34		Aspen	Bellen	Carlota	MB	Qad	Sunset	Sunshine	Sunspot	Yanaki				Aspen	Bellen	Carlota	MB	Qad	Sunset	Sunshine	Sunspot	Yanaki
35		5	19	22	29	41	45	63	70	84				5	19	22	29	41	45	63	70	84
36		13	18	41	47	51	52	75	80	88				13	18	41	47	51	52	75	80	88
37		17	29	32	70	81	83	84	85	98				17	29	32	70	81	83	84	85	98
38		12	15	25	26	27	50	62	80	88				12	15	25	26	27	50	62	80	88
39		13	27	38	57	59	62	66	84	97				13	27	38	57	59	62	66	84	97
40		19	21	24	26	29	40	43	87	99				19	21	24	26	29	40	43	87	99
41		1	10	24	37	43	47	90	95					10	24	37	43	47	90	95
42		11	20	33	49	65	78	90	97					11	20	33	49	78	90	97
43		13	23	24	29	38	46	71	73					13	23	24	29	46	71	73
44		3	11	23	29	61	73	74	91	97				3	11	23	29	61	73	74	91	97
45		2	6	24	31	48	71	85	95	99				2	6	24	31	48	71	85	95	99
46		14	21	22	28	46	62	73	76					14	21	22	28	46	73	76
47		16	41	69	70	71	74	76	87					16	69	70	71	74	76	87
48		25	28	39	50	69	93	95						25	28	39	69	93	95
49		11	17	20	48	63	86	90	93					11	17	20	48	63	86	93
50		3	14	15	19	32	53	67	72	92				3	14	15	19	32	53	67	72	92
51
1793 (5)

Cell Formulas
Range	Formula
L2,N2,N28,B28	L2	=FORMULATEXT(L3)
L3:L24	L3	=BYROW(B3:J24,LAMBDA(x,TEXTJOIN(",",,SORT(UNIQUE(x,1),,,1))))
N3:V24	N3	=ATEXTSPLIT(L3#,",")
B29:J50	B29	=ATEXTSPLIT(BYROW(B3:J24,LAMBDA(x,TEXTJOIN(",",,SORT(UNIQUE(x,1),,,1)))),",")
N29:V50	N29	=ATEXTSPLIT(BYROW(B3:J24,LAMBDA(x,TEXTJOIN(",",,SORT(UNIQUE(x,1,1),,,1)))),",")
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:J25,B26,I26:J26	Expression	=COUNTIFS($B3:$J3,B3)>1	text	NO

 

[Xlambda]

Cool example to share.
Mike asked me on the comments section of his latest YT:
"a formula that will enact a running sub-count for consecutive numbers. I have a none spill formula like this:

G10 contains: 2
and I use this formula in Column H starting in cell H10: =IF(G10=G9,H9+1,1) to get: 1
G11 contains: 1
and I use this formula in Column H starting in cell H10: =IF(G11=G10,H10+1,1) to get: 1
G12 contains: 3
and I use this formula in Column H starting in cell H10: =IF(G12=G11,H11+1,1) to get: 1
G13 contains: 1
and I use this formula in Column H starting in cell H10: =IF(G13=G12,H12+1,1) to get: 1
G14 contains: 3
and I use this formula in Column H starting in cell H10: =IF(G14=G13,H13+1,1) to get: 1
G15 contains: 3
and I use this formula in Column H starting in cell H10: =IF(G15=G14,H14+1,1) to get: 2
G16 contains: 2
and I use this formula in Column H starting in cell H10: =IF(G16=G15,H15+1,1) to get: 1
G17 contains: 2
and I use this formula in Column H starting in cell H10: =IF(G17=G16,H16+1,1) to get: 2 "

Since it will be a SCAN/REDUCE lambda formula scenario like so many other examples here, belongs to this thread.
1 on 1 as requested:

AUNIQUE.xlsx
	F	G	H	I	J	K	L	M	N	O	P
9
10		2	1	=IF(G10=G9,H9+1,1)
11		1	1	=IF(G11=G10,H10+1,1)
12		3	1	=IF(G12=G11,H11+1,1)
13		1	1	=IF(G13=G12,H12+1,1)
14		3	1	=IF(G14=G13,H13+1,1)
15		3	2	=IF(G15=G14,H14+1,1)
16		2	1	=IF(G16=G15,H15+1,1)
17		2	2	=IF(G17=G16,H16+1,1)
18
19			single cell lambda formula
20			=LET(a,G10:G17,SCAN(1,SEQUENCE(ROWS(a)),LAMBDA(v,i,IF(i=1,v,IF(INDEX(a,i-1)=INDEX(a,i),v+1,1)))))
21			1
22			1
23			1
24			1
25			1
26			2
27			1
28			2
29
ASCAN 1

Cell Formulas
Range	Formula
H10:H17	H10	=IF(G10=G9,H9+1,1)
I10:I17	I10	=FORMULATEXT(H10)
H20	H20	=FORMULATEXT(H21)
H21:H28	H21	=LET(a,G10:G17,SCAN(1,SEQUENCE(ROWS(a)),LAMBDA(v,i,IF(i=1,v,IF(INDEX(a,i-1)=INDEX(a,i),v+1,1)))))
Dynamic array formulas.

 

[Xlambda]

Happy New Year 2023 All !!
Last post 2022, inspired by a question on YT comment section. latest Mike's YT. Excel LAMBDA function – Every Single Things You Ever Wanted To Know - 365 MECS 10
Running totals for 2 columns array.
Lambda formulas, 3 scenarios:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1					Running totals double column array
2
3		sales 1	sales 2		=LET(a,B4:C10,r,REDUCE(0,SEQUENCE(ROWS(a)),LAMBDA(v,i,VSTACK(v,INDEX(v,i)+INDEX(a,i)))),DROP(r,1))
4		120	45		120	45
5		230	23		350	68
6		430	83		780	151
7		520	45		1300	196
8		712	89		2012	285
9		439	56		2451	341
10		492	37		2943	378
11
12					Running totals single column , 2 values joined
13
14					=LET(a,C15#,r,REDUCE(0,SEQUENCE(ROWS(a)),LAMBDA(v,i,VSTACK(v,INDEX(v,i,)+TEXTSPLIT(INDEX(a,i,1),",")))),DROP(r,1))
15			120,45		120	45
16			230,23		350	68
17			430,83		780	151
18			520,45		1300	196
19			712,89		2012	285
20			439,56		2451	341
21			492,37		2943	378
22
23					Running totals, cumulative 2 clms
24
25					=SCAN(0,B4:C10,LAMBDA(v,i,v+i))
26					120	165
27					395	418
28					848	931
29					1451	1496
30					2208	2297
31					2736	2792
32					3284	3321
33
Sheet1

Cell Formulas
Range	Formula
E3,E25,E14	E3	=FORMULATEXT(E4)
E4:F10	E4	=LET(a,B4:C10,r,REDUCE(0,SEQUENCE(ROWS(a)),LAMBDA(v,i,VSTACK(v,INDEX(v,i)+INDEX(a,i)))),DROP(r,1))
C15:C21	C15	=B4:B10&","&C4:C10
E15:F21	E15	=LET(a,C15#,r,REDUCE(0,SEQUENCE(ROWS(a)),LAMBDA(v,i,VSTACK(v,INDEX(v,i,)+TEXTSPLIT(INDEX(a,i,1),",")))),DROP(r,1))
E26:F32	E26	=SCAN(0,B4:C10,LAMBDA(v,i,v+i))
Dynamic array formulas.

 

[Xlambda]

...one year later..first post 2023
When comes to running totals for multiple columns we have 3 ways,3 different techniques/concepts
- main function of this thread ASCAN
- adding row by row
- scanning column by column

last post 2022 ASCAN.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													defined function, any scenario, very eficient
2		=RANDARRAY(10,10,10,99,1)											=ASCAN(B3#,1)
3		70	50	93	58	52	59	47	27	72	63		70	50	93	58	52	59	47	27	72	63
4		25	35	94	83	35	41	14	35	14	18		95	85	187	141	87	100	61	62	86	81
5		77	91	82	71	82	56	68	87	18	87		172	176	269	212	169	156	129	149	104	168
6		63	37	43	81	80	98	28	60	55	26		235	213	312	293	249	254	157	209	159	194
7		17	72	77	90	99	26	21	87	98	49		252	285	389	383	348	280	178	296	257	243
8		62	82	47	18	45	33	75	87	58	58		314	367	436	401	393	313	253	383	315	301
9		99	30	69	11	26	76	46	54	44	54		413	397	505	412	419	389	299	437	359	355
10		19	43	54	36	17	19	62	77	42	61		432	440	559	448	436	408	361	514	401	416
11		66	90	16	83	18	54	21	25	59	79		498	530	575	531	454	462	382	539	460	495
12		11	99	43	98	41	66	22	76	60	17		509	629	618	629	495	528	404	615	520	512
13
14
15										lambda formula when nr of columns way larger than the number of rows
16										=LET(a,B3#,r,REDUCE(0,SEQUENCE(ROWS(a)),LAMBDA(v,i,VSTACK(v,INDEX(v,i)+INDEX(a,i)))),DROP(r,1))
17													70	50	93	58	52	59	47	27	72	63
18													95	85	187	141	87	100	61	62	86	81
19													172	176	269	212	169	156	129	149	104	168
20													235	213	312	293	249	254	157	209	159	194
21													252	285	389	383	348	280	178	296	257	243
22													314	367	436	401	393	313	253	383	315	301
23													413	397	505	412	419	389	299	437	359	355
24													432	440	559	448	436	408	361	514	401	416
25													498	530	575	531	454	462	382	539	460	495
26													509	629	618	629	495	528	404	615	520	512
27
28				lambda formula when nr. of rows way larger than nr. of columns
29				=LET(a,B3#,LET(s,LAMBDA(x,SCAN(0,x,LAMBDA(v,i,v+i))),r,REDUCE(0,SEQUENCE(COLUMNS(a)),LAMBDA(v,i,HSTACK(v,s(INDEX(a,,i))))),DROP(r,,1)))
30													70	50	93	58	52	59	47	27	72	63
31													95	85	187	141	87	100	61	62	86	81
32													172	176	269	212	169	156	129	149	104	168
33													235	213	312	293	249	254	157	209	159	194
34													252	285	389	383	348	280	178	296	257	243
35													314	367	436	401	393	313	253	383	315	301
36													413	397	505	412	419	389	299	437	359	355
37													432	440	559	448	436	408	361	514	401	416
38													498	530	575	531	454	462	382	539	460	495
39													509	629	618	629	495	528	404	615	520	512
40
Sheet2

Cell Formulas
Range	Formula
B2,M2	B2	=FORMULATEXT(B3)
B3:K12	B3	=RANDARRAY(10,10,10,99,1)
M3:V12	M3	=ASCAN(B3#,1)
J16	J16	=FORMULATEXT(M17)
M17:V26	M17	=LET(a,B3#,r,REDUCE(0,SEQUENCE(ROWS(a)),LAMBDA(v,i,VSTACK(v,INDEX(v,i)+INDEX(a,i)))),DROP(r,1))
D29	D29	=FORMULATEXT(M30)
M30:V39	M30	=LET(a,B3#,LET(s,LAMBDA(x,SCAN(0,x,LAMBDA(v,i,v+i))),r,REDUCE(0,SEQUENCE(COLUMNS(a)),LAMBDA(v,i,HSTACK(v,s(INDEX(a,,i))))),DROP(r,,1)))
Dynamic array formulas.

 

[bines53]

Hi Xlambda,

regarding to Running totals for 2 columns array,I tried to do with the function TAKE, I think you can develop this without the need for recursion.
=MAP(TAKE(B4:C10,SEQUENCE(7,,1,1)),LAMBDA(x,SUM(INDEX(x,,1))))

TAKE(B4:C10,SEQUENCE(7,,1,1)) ,
Creates the cumulative amount.

David