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.

 

[tboulden]

BYROW still cannot "spill" rows.

Correct, but as I mentioned in SCANBYROW, we can thunk the results and get them back out with MAKEARRAY; I think the usefulness of this to have an array-of-arrays will be important for some use-cases.

LAMBDA_SCANBY.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1	4	3	3	3	1	1	4
2	2	1	1	2	7	7	5
3	8	6	6	5	5	8	9
4
5									4	3	1	#REF!		=LET( thunks, BYROW(A1:G3, LAMBDA(row_, LAMBDA(TRANSPOSE(UNIQUE(TRANSPOSE(row_)))) )), rows_,ROWS(thunks), cols_,MAX(MAP(thunks,LAMBDA(thunk,COLUMNS(thunk())))), MAKEARRAY(rows_,cols_, LAMBDA(i,j, INDEX( INDEX(thunks,i,1)(), 1, j ) ) ) )
6									2	1	7	5
7									8	6	5	9
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Sheet1

Cell Formulas
Range	Formula
I5:L7	I5	=LET( thunks, BYROW(A1:G3, LAMBDA(row_, LAMBDA(TRANSPOSE(UNIQUE(TRANSPOSE(row_)))) )), rows_,ROWS(thunks), cols_,MAX(MAP(thunks,LAMBDA(thunk,COLUMNS(thunk())))), MAKEARRAY(rows_,cols_, LAMBDA(i,j, INDEX( INDEX(thunks,i,1)(), 1, j ) ) ) )
N5	N5	=FORMULATEXT(I5)
Dynamic array formulas.

 

[Xlambda]

Of course it can be tricked in even more ways, but still, not for real life. One day excel will make it dynamic.? ✌

 

[Xlambda]

SCAN vs BYROW

LAMBDA 1.1.4.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
1	SCAN vs BYROW versatility
2	In general SCAN is more versatile because we can control the "iteration argument".
3	Also we are not limited to one direction, we can use sections of an array, plus initial value could be "silent" or cumulative.
4	Examples:
5	1. First time seen on one of MrExcel YT videos , an unusual type of sequencial extraction.
6	Instead of a regular:				↓		he needed this :				↓		For SCAN versatility, very easy
7					↓						↓		=SCAN("",SEQUENCE(6),LAMBDA(v,i,LET(j,IF(MOD(i,2),1,-1),TEXTJOIN("|",,SORT(INDEX(A8#,i,),,j,1)))))
8	1	2	3	4	5		1	2	3	4	5		1|2|3|4|5
9	6	7	8	9	10		10	9	8	7	6		10|9|8|7|6		=ATEXTSPLIT(M8#,"|")
10	11	12	13	14	15		11	12	13	14	15		11|12|13|14|15		1	2	3	4	5
11	16	17	18	19	20		20	19	18	17	16		20|19|18|17|16		10	9	8	7	6
12	21	22	23	24	25		21	22	23	24	25		21|22|23|24|25		11	12	13	14	15
13	26	27	28	29	30		30	29	28	27	26		30|29|28|27|26		20	19	18	17	16
14							("snake" sequence)								21	22	23	24	25
15															30	29	28	27	26
16	MrExcel YT link
17	Better ESPN Fantasy Draft Recap In Excel Free Download - 2426
18
19	2. dynamic selection
20
21	sort ascending between rows						2
22							6
23
24	sample						=ATEXTSPLIT(SCAN("",SEQUENCE(G22-G21+1,,G21),LAMBDA(v,i,TEXTJOIN("|",,SORT(INDEX(A25:D31,i,),,,1)))),"|")
25	6	5	7	2			1	2	4	5
26	4	2	1	5			5	6	8	9
27	8	6	5	9			3	6	7	8
28	7	3	8	6			1	4	5	9
29	9	1	5	4			2	5	7	10
30	10	5	7	2
31	5	8	10	7
32
ASCAN post 8

Cell Formulas
Range	Formula
M7,G24,O9	M7	=FORMULATEXT(M8)
A8:E13	A8	=SEQUENCE(6,5)
M8:M13	M8	=SCAN("",SEQUENCE(6),LAMBDA(v,i,LET(j,IF(MOD(i,2),1,-1),TEXTJOIN("|",,SORT(INDEX(A8#,i,),,j,1)))))
O10:S15	O10	=ATEXTSPLIT(M8#,"|")
G25:J29	G25	=ATEXTSPLIT(SCAN("",SEQUENCE(G22-G21+1,,G21),LAMBDA(v,i,TEXTJOIN("|",,SORT(INDEX(A25:D31,i,),,,1)))),"|")
Dynamic array formulas.

 

[Xlambda]

TEXTJOIN vs ARRAYTOTEXT

LAMBDA 1.1.4.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
1	TEXTJOIN vs ARRAYTOTEXT
2	TEXTJOIN is more versatile in general because of its "ignore empty" argument, and its flexibility to choose any delimiter, even array delimiter.
3	ARRAYTOTEXT, only "," delimiter, no "ignore empty". "," could be a problem with the final splitting, if the cells contain already ","'s.
4	With textjoin, no problem, we choose any delim we want.
5
6	1. Extract only the unique numeric values of each row of the following array (ae)
7
8											unique all values
9									ae		=SCAN("",SEQUENCE(3),LAMBDA(v,i,TEXTJOIN("|",1,UNIQUE(INDEX(ae,i,),1))))
10	b	3	3	a	a	4	d	1	4		b|3|a|4|d|1
11	7	x	x	5	x	5	y	8	x		7|x|5|y|8
12	m	6	n	n	6	6	m	o	6		m|6|n|o
13
14		text									the only thing we have to modify for numeric values
15		numbers									is "ae" with:	=IF(ISTEXT(ae),"",ae)							, textjoin will ignore the gaps							=ARRAYTOTEXT(L16:T16) does not
16													3	3			4		1	4						, 3, 3, , , 4, , 1, 4
17												7			5		5		8							=ARRAYTOTEXT(L16:T16,1) still not good
18													6			6	6			6						{"",3,3,"","",4,"",1,4}
19
20											=SCAN("",SEQUENCE(3),LAMBDA(v,i,TEXTJOIN("|",1,UNIQUE(INDEX(IF(ISTEXT(ae),"",ae),i,),1))))
21											↓			=ATEXTSPLIT(K22#,"|")
22											3|4|1			3	4	1
23											7|5|8			7	5	8										other functions
24											6			6												ATEXTSPLIT
25
26	1. Extract only the unique non numeric values of each row of (ae)
27
28	single cell ( we use ae=IF(ISNUMBER(ae),"",ae)
29
30				=ATEXTSPLIT(SCAN("",SEQUENCE(3),LAMBDA(v,i,TEXTJOIN("|",1,UNIQUE(INDEX(IF(ISNUMBER(ae),"",ae),i,),1)))),"|")
31				b	a	d
32				x	y					Notes:
33				m	n	o				No need to say that whatever crazy conditions will apply to initial array, the formula will comply.
34										If we need, for example, unique extraction of numeric values >=10 and <100, no problem.
35										Rhetoric question: What custom-made function can ever cover everything that a 5" long lambda "formula" can do in 2 sec?
36										Very few lambda functions will survive.
37
ASCAN post 9

Cell Formulas
Range	Formula
K9,D30,N21,L15	K9	=FORMULATEXT(K10)
K10:K12	K10	=SCAN("",SEQUENCE(3),LAMBDA(v,i,TEXTJOIN("|",1,UNIQUE(INDEX(ae,i,),1))))
Z15	Z15	=FORMULATEXT(Z16)&" does not"
L16:T18	L16	=IF(ISTEXT(ae),"",ae)
Z16	Z16	=ARRAYTOTEXT(L16:T16)
Z17	Z17	=FORMULATEXT(Z18)&" still not good"
Z18	Z18	=ARRAYTOTEXT(L16:T16,1)
K20	K20	=FORMULATEXT(K22)
K22:K24	K22	=SCAN("",SEQUENCE(3),LAMBDA(v,i,TEXTJOIN("|",1,UNIQUE(INDEX(IF(ISTEXT(ae),"",ae),i,),1))))
N22:P24	N22	=ATEXTSPLIT(K22#,"|")
D31:F33	D31	=ATEXTSPLIT(SCAN("",SEQUENCE(3),LAMBDA(v,i,TEXTJOIN("|",1,UNIQUE(INDEX(IF(ISNUMBER(ae),"",ae),i,),1)))),"|")
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
ae	='ASCAN post 9'!$A$10:$I$12	L16, K22, K10, D31

 

[tboulden]

Below I perform practically the same snake-sequence calc with BYROW/TEXTJOIN that you're performing with SCAN/TEXTJOIN. The main difference is BYROW construction has SEQUENCE(,5) in the TEXTJOIN, not sure why your version doesn't also work with BYROW, any ideas?

The whole point of the LAMBDA helpers is to add more tools, so there will be multiple ways to do the same task generally speaking; I'm mainly concerned about clarity and best practices. In your SCAN construction, the init_value can be anything at all because it is ignored since the v param in your LAMBDA is never used.

The TEXTJOIN vs ARRAYTOTEXT points are good to note, but I skip needing either one by thunking the arrays and using MAKEARRAY and without needing ATEXTSPLIT.

Personally, I'd rather do snake-sequence calc using MAP/thunks/MAKEARRAY as below than BYROW/BYCOL.

LAMBDA_SCANBY.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	1	2	3	4	5		1|2|3|4|5					=SCAN( "", SEQUENCE(6), LAMBDA(v,i, LET( j,IF(MOD(i,2),1,-1), TEXTJOIN("|",,SORT(INDEX(A1#,i,),,j,1)) )) )
2	6	7	8	9	10		10|9|8|7|6
3	11	12	13	14	15		11|12|13|14|15
4	16	17	18	19	20		20|19|18|17|16
5	21	22	23	24	25		21|22|23|24|25
6	26	27	28	29	30		30|29|28|27|26
7
8
9
10
11							1|2|3|4|5					=BYROW( SEQUENCE(6), LAMBDA(i, LET( j,IF(MOD(i,2),1,-1), TEXTJOIN("|",,SORT(INDEX(A1#,i,SEQUENCE(,5)),,j,1)) )) )
12							10|9|8|7|6
13							11|12|13|14|15
14							20|19|18|17|16
15							21|22|23|24|25
16							30|29|28|27|26
17
18
19
20							1	2	3	4	5	=LET( array,TRANSPOSE(A1#),rows_,ROWS(array), cols_,COLUMNS(array), seq,SEQUENCE(,cols_),sort_params,IF(ISEVEN(seq),-1,1), thunks, MAP(seq,sort_params, LAMBDA(idx,order, LAMBDA(SORT(INDEX(array,,idx),,order)) )), expand, MAKEARRAY(rows_,cols_, LAMBDA(i,j, INDEX(INDEX(thunks,1,j)(),i,1) )), TRANSPOSE(expand) )
21							10	9	8	7	6
22							11	12	13	14	15
23							20	19	18	17	16
24							21	22	23	24	25
25							30	29	28	27	26
26
27
28
29
30
31
32
33
34
Sheet2

Cell Formulas
Range	Formula
A1:E6	A1	=SEQUENCE(6,5)
G1:G6	G1	=SCAN( "", SEQUENCE(6), LAMBDA(v,i, LET( j,IF(MOD(i,2),1,-1), TEXTJOIN("|",,SORT(INDEX(A1#,i,),,j,1)) )) )
L1,L20,L11	L1	=FORMULATEXT(G1)
G11:G16	G11	=BYROW( SEQUENCE(6), LAMBDA(i, LET( j,IF(MOD(i,2),1,-1), TEXTJOIN("|",,SORT(INDEX(A1#,i,SEQUENCE(,5)),,j,1)) )) )
G20:K25	G20	=LET( array,TRANSPOSE(A1#),rows_,ROWS(array), cols_,COLUMNS(array), seq,SEQUENCE(,cols_),sort_params,IF(ISEVEN(seq),-1,1), thunks, MAP(seq,sort_params, LAMBDA(idx,order, LAMBDA(SORT(INDEX(array,,idx),,order)) )), expand, MAKEARRAY(rows_,cols_, LAMBDA(i,j, INDEX(INDEX(thunks,1,j)(),i,1) )), TRANSPOSE(expand) )
Dynamic array formulas.

 

[Xlambda]

?if you noticed, my threads have, in general, more than one post. Sometimes they reveal more ways of solving same problem, sometimes they are based on a concept, they have a foundation and a structure that builds up in a flow of examples, from basic simple ones to more complex ones at the end. So, concept, foundation, flow, all consistent with each other. The concept here is: lambda "formula" (mandatory to be short), foundation is SCAN/REDUCE, flow....
The flow just started, and you came in with alternatives. Very nice! I am open to all alternatives, but here the "foundation" was SCAN as the thread name states (consistency again), pushing it to the limits. I will finish the flow other time, no problem.
Anyhow, single lambda like SCAN versus 2 other lambdas whatever they are BYROW and MAKEARRAY or MAP and other…come on, never. I did combinatorics with all possible formulas and methods, when we deal with 10M cells a simple comma added makes the difference between 5s and eternity.
Regarding ATEXTSPLIT; Why we use any function? Why we use UNIQUE? We can still do it old school. The answer is quite simple. We use them because we have them, and they do their job. As simple as that.
To conclude on a funny note. You know, obviously, what cab sharing does, more popular in US. I was in a cab heading to SCAN, you jump in asking me if we can detour to BYROW , normally is no problem, if I am not in a hurry.?✌

 

[Xlambda]

Snake sequence RxC . An example of what fits my personal "concept" of a lambda "formula" in general.
=MAKEARRAY(R,C,LAMBDA(r,c,C*(r-1)+c*ISODD(r)+(C-c+1)*ISEVEN(r)))

LAMBDA 1.1.4.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	rows	7
2	clms	8
3
4	=MAKEARRAY(B1,B2,LAMBDA(r,c,B2*(r-1)+c*ISODD(r)+(B2-c+1)*ISEVEN(r)))
5	1	2	3	4	5	6	7	8
6	16	15	14	13	12	11	10	9
7	17	18	19	20	21	22	23	24
8	32	31	30	29	28	27	26	25
9	33	34	35	36	37	38	39	40
10	48	47	46	45	44	43	42	41
11	49	50	51	52	53	54	55	56
12
Sheet11

Cell Formulas
Range	Formula
A4	A4	=FORMULATEXT(A5)
A5:H11	A5	=MAKEARRAY(B1,B2,LAMBDA(r,c,B2*(r-1)+c*ISODD(r)+(B2-c+1)*ISEVEN(r)))
Dynamic array formulas.

 

[tboulden]

To conclude on a funny note. You know, obviously, what cab sharing does, more popular in US. I was in a cab heading to SCAN, you jump in asking me if we can detour to BYROW , normally is no problem, if I am not in a hurry.?✌

This is my take of tboulden's SCANBYROW/BYCOL.

Not sure the car-sharing metaphor holds up since I inspired the thread

 

[Xlambda]

?You can own the cab company, still is cab sharing?

 

[Xlambda]

Back on track with more examples:

LAMBDA 1.1.4.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	Task: "scan" whole array by each product
2
3	sample array 6 rows, 3, products							unique prod.				"scan" whole array, not what we want															"scan" by row, not what we want
4	prod.							=UNIQUE(A5:A10)				=SCAN(0,B5:F10,LAMBDA(v,a,v+a))								or	=ASCAN(B5:F10)						=ASCAN(B5:F10,-1)
5	A	8	6	8	6	3		A				8	14	22	28	31					8	14	22	28	31		8	14	22	28	31
6	B	7	9	8	7	5		B				38	47	55	62	67					38	47	55	62	67		7	16	24	31	36
7	A	4	9	9	2	3		C				71	80	89	91	94					71	80	89	91	94		4	13	22	24	27
8	C	1	4	1	5	1						95	99	100	105	106					95	99	100	105	106		1	5	6	11	12
9	B	6	3	2	7	8						112	115	117	124	132					112	115	117	124	132		6	9	11	18	26
10	C	5	6	8	8	1						137	143	151	159	160					137	143	151	159	160		5	11	19	27	28
11
12										scan only for prod=A						scan only prod=B						scan only prod=C						sum all scans
13																												=J14#+P14#+V14#
14										8	14	22	28	31		0	0	0	0	0		0	0	0	0	0		8	14	22	28	31
15										0	0	0	0	0		7	16	24	31	36		0	0	0	0	0		7	16	24	31	36
16										35	44	53	55	58		0	0	0	0	0		0	0	0	0	0		35	44	53	55	58
17										0	0	0	0	0		0	0	0	0	0		1	5	6	11	12		1	5	6	11	12
18										0	0	0	0	0		42	45	47	54	62		0	0	0	0	0		42	45	47	54	62
19										0	0	0	0	0		0	0	0	0	0		17	23	31	39	40		17	23	31	39	40
20
21									K14:	=SCAN(0,(B5:F10)*(A5:A10=INDEX(H5#,1)),LAMBDA(v,a,v+a))*((A5:A10=INDEX(H5#,1)))
22									Q14:	=SCAN(0,(B5:F10)*(A5:A10=INDEX(H5#,2)),LAMBDA(v,a,v+a))*((A5:A10=INDEX(H5#,2)))
23									W14:	=SCAN(0,(B5:F10)*(A5:A10=INDEX(H5#,3)),LAMBDA(v,a,v+a))*((A5:A10=INDEX(H5#,3)))
24
25										single cell
26										=REDUCE(0,SEQUENCE(3),LAMBDA(v,i,v+SCAN(0,(B5:F10)*(A5:A10=INDEX(H5#,i)),LAMBDA(v,a,v+a))*((A5:A10=INDEX(H5#,i)))))
27										8	14	22	28	31
28										7	16	24	31	36
29										35	44	53	55	58
30										1	5	6	11	12
31										42	45	47	54	62
32										17	23	31	39	40
33
ASCAN post 10

Cell Formulas
Range	Formula
H4,L4,J26,AB13,AA4,U4	H4	=FORMULATEXT(H5)
H5:H7	H5	=UNIQUE(A5:A10)
L5:P10	L5	=SCAN(0,B5:F10,LAMBDA(v,a,v+a))
U5:Y10	U5	=ASCAN(B5:F10)
AA5:AE10	AA5	=ASCAN(B5:F10,-1)
J14:N19	J14	=SCAN(0,(B5:F10)*(A5:A10=INDEX(H5#,1)),LAMBDA(v,a,v+a))*((A5:A10=INDEX(H5#,1)))
P14:T19	P14	=SCAN(0,(B5:F10)*(A5:A10=INDEX(H5#,2)),LAMBDA(v,a,v+a))*((A5:A10=INDEX(H5#,2)))
V14:Z19	V14	=SCAN(0,(B5:F10)*(A5:A10=INDEX(H5#,3)),LAMBDA(v,a,v+a))*((A5:A10=INDEX(H5#,3)))
AB14:AF19	AB14	=J14#+P14#+V14#
J21	J21	=FORMULATEXT(J14)
J22	J22	=FORMULATEXT(P14)
J23	J23	=FORMULATEXT(V14)
J27:N32	J27	=REDUCE(0,SEQUENCE(3),LAMBDA(v,i,v+SCAN(0,(B5:F10)*(A5:A10=INDEX(H5#,i)),LAMBDA(v,a,v+a))*((A5:A10=INDEX(H5#,i)))))
Dynamic array formulas.