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]

Details .xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1			Calling DET with dif. arguments				main example			=DET(To[Order],Tb[Order ID],Tb[[Product Name]:[Qty]],Tb[[#Headers],[Product Name]:[Qty]],To[Details])
2							3 clms details			=DET(To[Order],Tb[Order ID],Tb[[Product Name]:[Price]],Tb[[#Headers],[Product Name]:[Price]],To[Details])
3						clms not next to ech oth.				=DET(To[Order],Tb[Order ID],HSTACK(Tb[Customer ID],Tb[Category]),{"ID","Cat"},To[Details])
4		Show Invoice Details								3 clm details
5										=DET(To[Order],Tb[Order ID],Tb[[Product Name]:[Price]],Tb[[#Headers],[Product Name]:[Price]],To[Details])
6		Order	Company Name	Date	Paid	Shipping	Items	Details		↓↓↓↓↓
7		202025	Ocean's Edge Surf	14-07-24	Yes	Ground	1
8		202126	Oceanfront Surf	10-07-24	Yes	Ground	3			Product Name	Qty	Price
9		202037	Ocean Trail	20-07-24	Yes	Ground	2			AquaLume LED Light Kit	4	18.99
10		202106	Pacific Waves	16-07-24	Yes	Next Day	5			TideProof Paddle Jacket	5	64.99	Product Name	Qty	Price
11		202140	Breaker Point Surf	20-07-24	No	Next Day	1			ShoreLine Collapsible Paddle	1	59.99	TideLock Deck Bag	1	79.99
12		202121	Ocean Explorer	13-07-24	Yes	Ground	5			Product Name	Qty	Price	Reef Master Eco Board	1	189
13		201921	Coastline Surf	19-07-24	Yes	Ground	1			Wave Rider Surf Series	1	449	Wave Rider Surf Series	1	449
14		201950	Maritime Surf	18-07-24	Yes	Air	2			ReefFlex Anti-Slip Deck Pad	1	64.99	ShoreLine Collapsible Paddle	2	59.99
15		201934	Ocean Mist Surf	18-07-24	No	Ground	1			Logo Tshirt	1	21.99	Logo Tshirt	1	21.99
16		202011	Surf Gateway	19-07-24	Yes	Air	5			SunBask Leisure Paddle	1	279	Product Name	Qty	Price
17		201990	Oceanfront Surf	09-07-24	No	Ground	5			Logo Tshirt	1	21.99	HydroThrive Safety Leash	1	18.99
18		202167	Surf Expedition	19-07-24	No	Ground	4			Product Name	Qty	Price	Ocean Sprint Racing Pro	3	1499
19		202081	Wave Serenity	17-07-24	No	Ground	5			Logo Tshirt	5	21.99	ShoreLine Collapsible Paddle	3	59.99	Product Name	Qty	Price
20		202148	Ocean's Edge Surf	13-07-24	Yes	Ground	4			Logo Tshirt	4	21.99	Logo Tshirt	2	21.99	SunShield Rash Guard	4	29.99
21		202051	Surf Sanctuary	18-07-24	No	Ground	1			SunShield Rash Guard	1	29.99	SunShield Rash Guard	1	29.99	Carve Performance Board	1	799	Product Name	Qty	Price
22		201912	Swell Surf Co.	08-07-24	No	Air	1			SunShield Rash Guard	1	29.99				AquaGrip Paddle Gloves	2	29.99	Logo Tshirt	2	21.99
23		202008	Surf Sanctuary	10-07-24	No	Ground	4			Product Name	Qty	Price				HydroThrive Safety Leash	1	18.99
24		202049	Beach Breeze Surf	11-07-24	No	Ground	1			ReefFlex Anti-Slip Deck Pad	1	64.99	Product Name	Qty	Price	Carve Performance Board	3	799
25		202003	Beach Patrol Surf	12-07-24	Yes	Ground	1			Wave Rider Surf Series	1	449	HydroThrive Safety Leash	2	18.99	Product Name	Qty	Price
26		202095	Surfside Shack	14-07-24	No	Ground	1			Carve Performance Board	1	799				AquaLume LED Light Kit	1	18.99
27		201972	Breaker Point Surf	09-07-24	No	Ground	1			Blue Horizon Explorer	1	1299	Product Name	Qty	Price
28		202169	Wave Reef	09-07-24	No	Ground	5			Product Name	Qty	Price	HydroThrive Safety Leash	1	18.99
29		202130	Maritime Surf	15-07-24	No	Next Day	1			HydroThrive Safety Leash	3	18.99	Product Name	Qty	Price
30		202085	Surf Escape	11-07-24	Yes	Ground	2			TideProof Paddle Jacket	1	64.99	HydroThrive Safety Leash	1	18.99
31		201986	Wave Bliss	19-07-24	No	Ground	1			Wave Rider Surf Series	1	449
32		202059	Surf Melody	19-07-24	No	Ground	4			Wave Rider Surf Series	3	449	Product Name	Qty	Price
33		202137	Ocean Explorer	13-07-24	No	Air	1			Carve Performance Board	1	799	Carve Performance Board	1	799	Product Name	Qty	Price
34		201945	Surf Crafters	11-07-24	No	Ground	4						Reef Master Eco Board	2	189	Wave Rider Surf Series	4	449
35		201982	Surf Coast	12-07-24	Yes	Ground	1			Product Name	Qty	Price	HydroThrive Safety Leash	2	18.99
36		202151	Sea Rider Surf	11-07-24	No	Ground	6			ClearVue Underwater Camera Mount	2	29.99	SunShield Rash Guard	1	29.99	Product Name	Qty	Price
37		201993	Surf Gateway	13-07-24	Yes	Ground	1			Product Name	Qty	Price				ReefSafe UV Leggings	1	35.99
38		202036	Tide Pools Surf	13-07-24	No	Ground	1			HydroThrive Safety Leash	2	18.99	Product Name	Qty	Price	ShoreLine Collapsible Paddle	2	59.99
39		202073	Ocean Mist Surf	13-07-24	No	Ground	6			Product Name	Qty	Price	Logo Tshirt	4	21.99	ReefFlex Anti-Slip Deck Pad	2	64.99
40										ReefFlex Anti-Slip Deck Pad	4	64.99				SunShield Rash Guard	1	29.99
41										TideProof Paddle Jacket	2	64.99				HydroThrive Safety Leash	1	18.99
42										SeaPulse GPS Tracker	2	129.99				AquaGrip Paddle Gloves	1	29.99
43										SeaPulse GPS Tracker	1	129.99
44										Logo Tshirt	1	21.99
45										Sea Bird Cruiser	2	249
46
Sheet1

Cell Formulas
Range	Formula
J5	J5	=FORMULATEXT(J7)
J7:U45	J7	=DET(To[Order],Tb[Order ID],Tb[[Product Name]:[Price]],Tb[[#Headers],[Product Name]:[Price]],To[Details])
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J7:AA50	Expression	=OR(J7="Product Name",J7="qty",J7="price",J7="id",J7="cat")	text	NO
J7:AA50	Cell Value	<>""	text	NO
B7:H39	Expression	=$H7	text	NO

Details .xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1			Calling DET with dif. arguments				main example			=DET(To[Order],Tb[Order ID],Tb[[Product Name]:[Qty]],Tb[[#Headers],[Product Name]:[Qty]],To[Details])
2							3 clms details			=DET(To[Order],Tb[Order ID],Tb[[Product Name]:[Price]],Tb[[#Headers],[Product Name]:[Price]],To[Details])
3						clms not next to ech oth.				=DET(To[Order],Tb[Order ID],HSTACK(Tb[Customer ID],Tb[Category]),{"ID","Cat"},To[Details])
4		Show Invoice Details								clms not next to ech oth. (not adjacent)
5										=DET(To[Order],Tb[Order ID],HSTACK(Tb[Customer ID],Tb[Category]),{"ID","Cat"},To[Details])
6		Order	Company Name	Date	Paid	Shipping	Items	Details		↓↓↓↓↓
7		202025	Ocean's Edge Surf	14-07-24	Yes	Ground	1
8		202126	Oceanfront Surf	10-07-24	Yes	Ground	3			ID	Cat
9		202037	Ocean Trail	20-07-24	Yes	Ground	2			108529	Accessories
10		202106	Pacific Waves	16-07-24	Yes	Next Day	5			108529	Apparel
11		202140	Breaker Point Surf	20-07-24	No	Next Day	1			108529	Accessories
12		202121	Ocean Explorer	13-07-24	Yes	Ground	5			ID	Cat
13		201921	Coastline Surf	19-07-24	Yes	Ground	1			130926	Boards	ID	Cat
14		201950	Maritime Surf	18-07-24	Yes	Air	2			130926	Accessories	117258	Boards	ID	Cat
15		201934	Ocean Mist Surf	18-07-24	No	Ground	1			130926	Apparel	ID	Cat	120035	Accessories
16		202011	Surf Gateway	19-07-24	Yes	Air	5			130926	Boards	115057	Apparel	120035	Apparel
17		201990	Oceanfront Surf	09-07-24	No	Ground	5			130926	Apparel
18		202167	Surf Expedition	19-07-24	No	Ground	4
19		202081	Wave Serenity	17-07-24	No	Ground	5
20		202148	Ocean's Edge Surf	13-07-24	Yes	Ground	4			ID	Cat
21		202051	Surf Sanctuary	18-07-24	No	Ground	1			124489	Apparel
22		201912	Swell Surf Co.	08-07-24	No	Air	1			124489	Apparel	ID	Cat
23		202008	Surf Sanctuary	10-07-24	No	Ground	4			124489	Accessories	117897	Apparel
24		202049	Beach Breeze Surf	11-07-24	No	Ground	1			124489	Apparel
25		202003	Beach Patrol Surf	12-07-24	Yes	Ground	1
26		202095	Surfside Shack	14-07-24	No	Ground	1			ID	Cat
27		201972	Breaker Point Surf	09-07-24	No	Ground	1			116424	Accessories
28		202169	Wave Reef	09-07-24	No	Ground	5
29		202130	Maritime Surf	15-07-24	No	Next Day	1
30		202085	Surf Escape	11-07-24	Yes	Ground	2			ID	Cat
31		201986	Wave Bliss	19-07-24	No	Ground	1			104034	Accessories
32		202059	Surf Melody	19-07-24	No	Ground	4			104034	Accessories	ID	Cat
33		202137	Ocean Explorer	13-07-24	No	Air	1					121611	Boards
34		201945	Surf Crafters	11-07-24	No	Ground	4			ID	Cat	121611	Boards
35		201982	Surf Coast	12-07-24	Yes	Ground	1			118065	Boards	121611	Accessories
36		202151	Sea Rider Surf	11-07-24	No	Ground	6			118065	Apparel	121611	Apparel	ID	Cat
37		201993	Surf Gateway	13-07-24	Yes	Ground	1			118065	Accessories			107928	Apparel
38		202036	Tide Pools Surf	13-07-24	No	Ground	1			118065	Accessories			107928	Accessories
39		202073	Ocean Mist Surf	13-07-24	No	Ground	6			ID	Cat			107928	Accessories
40										123406	Accessories			107928	Apparel
41										123406	Apparel			107928	Accessories
42										123406	Accessories			107928	Accessories
43										123406	Accessories
44										123406	Apparel
45										123406	Boards
46
Sheet1

Cell Formulas
Range	Formula
J5	J5	=FORMULATEXT(J7)
J7:O45	J7	=DET(To[Order],Tb[Order ID],HSTACK(Tb[Customer ID],Tb[Category]),{"ID","Cat"},To[Details])
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J7:AA50	Expression	=OR(J7="Product Name",J7="qty",J7="price",J7="id",J7="cat")	text	NO
J7:AA50	Cell Value	<>""	text	NO
B7:H39	Expression	=$H7	text	NO

 

[Xlambda]

And this is how it looks with all the checkboxes selected, (too clustered but works acurate). It's like playing Tetris with arrays to the left side
Next will be about the tri state (not only bi) functionality of checkboxes or as I call it Yes, No, Maybe

Details .xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1			Calling DET with dif. arguments				main example			=DET(To[Order],Tb[Order ID],Tb[[Product Name]:[Qty]],Tb[[#Headers],[Product Name]:[Qty]],To[Details])
2							3 clms details			=DET(To[Order],Tb[Order ID],Tb[[Product Name]:[Price]],Tb[[#Headers],[Product Name]:[Price]],To[Details])
3						clms not next to ech oth.				=DET(To[Order],Tb[Order ID],HSTACK(Tb[Customer ID],Tb[Category]),{"ID","Cat"},To[Details])
4		Show Invoice Details								clms not next to ech oth. (not adjacent)
5										=DET(To[Order],Tb[Order ID],HSTACK(Tb[Customer ID],Tb[Category]),{"ID","Cat"},To[Details])
6		Order	Company Name	Date	Paid	Shipping	Items	Details		↓↓↓↓↓
7		202025	Ocean's Edge Surf	14-07-24	Yes	Ground	1			ID	Cat
8		202126	Oceanfront Surf	10-07-24	Yes	Ground	3			113697	Boards	ID	Cat
9		202037	Ocean Trail	20-07-24	Yes	Ground	2			ID	Cat	108529	Accessories
10		202106	Pacific Waves	16-07-24	Yes	Next Day	5			118762	Accessories	108529	Apparel	ID	Cat
11		202140	Breaker Point Surf	20-07-24	No	Next Day	1			118762	Apparel	108529	Accessories	106698	Accessories	ID	Cat
12		202121	Ocean Explorer	13-07-24	Yes	Ground	5			ID	Cat			106698	Boards	108374	Apparel
13		201921	Coastline Surf	19-07-24	Yes	Ground	1			130926	Boards	ID	Cat	106698	Boards
14		201950	Maritime Surf	18-07-24	Yes	Air	2			130926	Accessories	117258	Boards	106698	Accessories	ID	Cat
15		201934	Ocean Mist Surf	18-07-24	No	Ground	1			130926	Apparel	ID	Cat	106698	Apparel	120035	Accessories
16		202011	Surf Gateway	19-07-24	Yes	Air	5			130926	Boards	115057	Apparel	ID	Cat	120035	Apparel
17		201990	Oceanfront Surf	09-07-24	No	Ground	5			130926	Apparel	ID	Cat	118499	Accessories
18		202167	Surf Expedition	19-07-24	No	Ground	4			ID	Cat	103467	Boards	118499	Boards
19		202081	Wave Serenity	17-07-24	No	Ground	5			108046	Apparel	103467	Accessories	118499	Accessories	ID	Cat
20		202148	Ocean's Edge Surf	13-07-24	Yes	Ground	4			108046	Apparel	103467	Apparel	118499	Apparel	103843	Apparel	ID	Cat
21		202051	Surf Sanctuary	18-07-24	No	Ground	1			108046	Apparel	103467	Apparel	118499	Apparel	103843	Boards	124489	Apparel	ID	Cat
22		201912	Swell Surf Co.	08-07-24	No	Air	1			108046	Apparel	103467	Boards	ID	Cat	103843	Accessories	124489	Apparel	102309	Apparel
23		202008	Surf Sanctuary	10-07-24	No	Ground	4			ID	Cat			117897	Apparel	103843	Accessories	124489	Accessories
24		202049	Beach Breeze Surf	11-07-24	No	Ground	1			124781	Accessories	ID	Cat			103843	Boards	124489	Apparel
25		202003	Beach Patrol Surf	12-07-24	Yes	Ground	1			124781	Boards	131827	Accessories	ID	Cat
26		202095	Surfside Shack	14-07-24	No	Ground	1			124781	Boards	ID	Cat	131370	Accessories
27		201972	Breaker Point Surf	09-07-24	No	Ground	1			124781	Boards	116424	Accessories	ID	Cat
28		202169	Wave Reef	09-07-24	No	Ground	5			ID	Cat			106326	Accessories
29		202130	Maritime Surf	15-07-24	No	Next Day	1			127808	Accessories	ID	Cat
30		202085	Surf Escape	11-07-24	Yes	Ground	2			127808	Apparel	128291	Accessories	ID	Cat
31		201986	Wave Bliss	19-07-24	No	Ground	1			127808	Boards	ID	Cat	104034	Accessories
32		202059	Surf Melody	19-07-24	No	Ground	4			127808	Boards	114740	Accessories	104034	Accessories	ID	Cat
33		202137	Ocean Explorer	13-07-24	No	Air	1			127808	Boards	ID	Cat			121611	Boards
34		201945	Surf Crafters	11-07-24	No	Ground	4			ID	Cat	104703	Boards			121611	Boards
35		201982	Surf Coast	12-07-24	Yes	Ground	1			118065	Boards	ID	Cat			121611	Accessories
36		202151	Sea Rider Surf	11-07-24	No	Ground	6			118065	Apparel	104234	Accessories	ID	Cat	121611	Apparel
37		201993	Surf Gateway	13-07-24	Yes	Ground	1			118065	Accessories	ID	Cat	107928	Apparel
38		202036	Tide Pools Surf	13-07-24	No	Ground	1			118065	Accessories	130011	Accessories	107928	Accessories	ID	Cat
39		202073	Ocean Mist Surf	13-07-24	No	Ground	6			ID	Cat			107928	Accessories	108874	Apparel
40										123406	Accessories			107928	Apparel
41										123406	Apparel			107928	Accessories
42										123406	Accessories			107928	Accessories
43										123406	Accessories
44										123406	Apparel
45										123406	Boards
46
Sheet1

Cell Formulas
Range	Formula
J5	J5	=FORMULATEXT(J7)
J7:U45	J7	=DET(To[Order],Tb[Order ID],HSTACK(Tb[Customer ID],Tb[Category]),{"ID","Cat"},To[Details])
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
J7:AA50	Expression	=OR(J7="Product Name",J7="qty",J7="price",J7="id",J7="cat")	text	NO
J7:AA50	Cell Value	<>""	text	NO
B7:H39	Expression	=$H7	text	NO

 

[mgirvin]

Xlambda, that is mindbogglingly magic that you just check, and the details stack up vertically, but if there is not enough room: BAM! The formula is polite, it stacks it off to the right. : ) : ) : ) : ) : )

 

[Xlambda]

Great challenge. Great PQ solution:

Extract all item codes that are repeated at least in 3 out of 4 lists (columns) presented in the question table.
And the formula alternative:

=LET(a,B4:E17,UNIQUE(SORT(TOCOL(IFS(MAP(a,LAMBDA(x,SUM(--BYCOL(x=a, OR))>2)),a),2))))

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1							Concept for 1 cell (B4)					Concept for all cells with MAP
2
3							=B5=B4:E17					=MAP(B4:E17,LAMBDA(x,SUM(--BYCOL(x=B4:E17,OR))>2))
4		X-005	X-004	X-020	X-015		FALSE	FALSE	FALSE	FALSE		FALSE	FALSE	FALSE	TRUE
5		X-017	X-002	X-025	X-011		TRUE	FALSE	FALSE	FALSE		TRUE	FALSE	FALSE	FALSE
6		X-011	X-003	X-012	X-002		FALSE	FALSE	FALSE	FALSE		FALSE	FALSE	TRUE	FALSE
7		X-023	X-017	X-025	X-022		FALSE	TRUE	FALSE	FALSE		TRUE	TRUE	FALSE	FALSE
8		X-015	X-012	X-012	X-017		FALSE	FALSE	FALSE	TRUE		TRUE	TRUE	TRUE	TRUE
9		X-004	X-023	X-012	X-006		FALSE	FALSE	FALSE	FALSE		FALSE	TRUE	TRUE	FALSE
10		X-015	X-019	X-010	X-022		FALSE	FALSE	FALSE	FALSE		TRUE	FALSE	FALSE	FALSE
11		X-008	X-008	X-019	X-018		FALSE	FALSE	FALSE	FALSE		FALSE	FALSE	FALSE	FALSE
12		X-012	X-016	X-016	X-003		FALSE	FALSE	FALSE	FALSE		TRUE	FALSE	FALSE	FALSE
13		X-021	X-020	X-025	X-013		FALSE	FALSE	FALSE	FALSE		FALSE	FALSE	FALSE	FALSE
14		X-012	X-009	X-014	X-023		FALSE	FALSE	FALSE	FALSE		TRUE	FALSE	FALSE	TRUE
15		X-014	X-004	X-025	X-003		FALSE	FALSE	FALSE	FALSE		FALSE	FALSE	FALSE	FALSE
16		X-013	X-016	X-024	X-021		FALSE	FALSE	FALSE	FALSE		FALSE	FALSE	FALSE	FALSE
17		X-004	X-001	X-015	X-012		FALSE	FALSE	FALSE	FALSE		FALSE	FALSE	TRUE	TRUE
18
19							=--BYCOL(G4#,OR)					Extract all TRUEs
20							1	1	0	1		=UNIQUE(SORT(TOCOL(IFS(L4#,B4:E17),2)))
21												X-012
22							=SUM(G20#)>2					X-015
23							TRUE					X-017
24												X-023
25		Single cell formula
26		=LET(a,B4:E17,UNIQUE(SORT(TOCOL(IFS(MAP(a,LAMBDA(x,SUM(--BYCOL(x=a,OR))>2)),a),2))))
27		X-012
28		X-015
29		X-017
30		X-023
31
Sheet1

Cell Formulas
Range	Formula
G3,B26,G22,L20,G19,L3	G3	=FORMULATEXT(G4)
G4:J17	G4	=B5=B4:E17
L4:O17	L4	=MAP(B4:E17,LAMBDA(x,SUM(--BYCOL(x=B4:E17,OR))>2))
G20:J20	G20	=--BYCOL(G4#,OR)
L21:L24	L21	=UNIQUE(SORT(TOCOL(IFS(L4#,B4:E17),2)))
G23	G23	=SUM(G20#)>2
B27:B30	B27	=LET(a,B4:E17,UNIQUE(SORT(TOCOL(IFS(MAP(a,LAMBDA(x,SUM(--BYCOL(x=a,OR))>2)),a),2))))
Dynamic array formulas.