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]

Hi Bill,
Something is wrong with the input data.
As reference, my formulas work with indexes (k) instead of text strings. Since there are unique names there is unique correspondence btw sorted names and seq(146). We can both use this as common reference.
Got your data, used XMATCH for the distrib of indexes and checked with my DS (function that calculates the consecutive distances of a sequence of indexes) and
got a total route of 96550 not 76326. That distance is bigger than the worst distance I got using the non cluster method (MK)
Then I checked the names, everything was ok, then I realized that your Lat and Lon clms are inversed. Can you check how your distances were calculated?
From Wiki Great Wall coord: "Latitude and longitude coordinates are: 40.431908, 116.570374."
Here is the data (hope that mini-sheet will get it entirely.

Book2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1		Bill's data					=XMATCH(B3:B148,TAKE(pl,,1))
2	ID	Location	Lat	Lon	Distance		↓↓		=DS(G3#)
3	1	Great Wall of China, China	116.5704	40.4319			40		0		=DS(G3#,1)
4	1	Gungnamji Pond, Republic of Korea	126.9122	36.26932	729.8757		41		629.1726		96550.27
5	1	Mount Fuji, Japan	138.7319	35.3606	817.6004		85		664.6623
6	1	Uzon Caldera, Russia	159.9156	54.51167	1844.549		129		1666.471		check
7	2	Matanuska Glacier, Alaska	-148.909	61.77	3567.842		75		1877.88		=SUM(I:I)
8	2	Wedgemount Lake, British Columbia, Canada	-122.882	50.1452	1882.128		139		1272.683		96550.27
9	2	Mount Shuksan, North Cascades National Park	-121.779	48.8252	90.46116		87		103.7521
10	2	Tom **** & Harry Mountain, Oregon	-121.743	45.3345	126.9405		127		241.1439
11	5	Clear Lake, Oregon	-121.97	44.5049	34.06401		21		58.37273
12	5	Crater Lake, Oregon	-122.133	42.93	58.82315		25		109.0986
13	5	Sonora Peak, California	-119.55	38.18	245.1277		113		354.9908
14	6	Mirror Lake, Yosemite National Park, California	-119.55	37.7457	14.79625		79		30.0015
15	6	Taft Point, Yosemite National Park, California	-119.594	37.7174	3.156072		115		3.077359
16	9	Vasquez Rocks Natural Area Park, California	-118.313	34.4908	139.4993		132		234.0639
17	9	Racetrack Playa, Death Valley, California	-117.554	36.7167	89.08942		102		159.5652
18	9	Badwater Basin, Death Valley, California	-116.841	36.239	51.55098		10		51.57622
19	9	Waterton Lake, Alberta, Canada	-113.915	49.06	428.9485		138		897.8817
20	10	Palisades Beach, Grand Canyon National Park, Arizona	-112.86	36.0978	362.1161		94		897.0111
21	11	Lake Powell, Hite Overlook	-110.731	37.388	150.7807		64		147.7788
22	12	Lake Powell, Escalante River	-110.6	37	13.07636		63		27.75128
23	13	Monument Valley Navajo Tribal Park, Arizona	-110.204	36.9986	27.38338		82		21.86973
24	13	Warren Canyon, Utah	-110.084	38.6599	40.37035		137		114.9471
25	15	Abajo Peak, Utah	-109.383	37.8809	51.72286		1		65.88887
26	15	Maroon Lake, Aspen, Colorado	-106.949	39.0988	170.2136		74		156.2236
27	15	Mirror Lake, Wyoming	-106.319	41.3382	62.04048		78		158.2161
28	16	Dunas De Bilbao, Mexico	-102.869	26.0094	355.8736		30		1077.08
29	17	White Sands, New Mexico	-106.171	32.7872	256.1869		142		508.5571
30	17	Playa El Requesón, Mexico	-111.832	26.63762	414.2635		99		543.6626
31	17	Motu Rimatiai, Tetiaroa	-149.535	-17.0015	3133.621		83		3934.874
32	17	Matavai Bay, Tahiti, French Polynesia	-149.6	-17.5594	33.532		76		38.77756
33	18	Ahu Tongariki, Rapa Nui (Easter Island)	-109.168	-27.1126	2816.932		2		2654.183
34	18	Isabela Island, Galápagos	-91.417	-0.8	1237.116		47		2165.585
35	19	Chichen Itza, Yucatán State, Mexico	-88.5686	20.684	193.3192		20		1496.558
36	20	Parque Nacional Palo Verde, Costa Rica	-85.3276	10.4094	226.1616		96		741.7176
37	21	Crego Park Lake, Lansing, Michigan	-84.5577	42.7518	200.9703		26		2234.7
38	21	Mural de la Prehistoria, Viñales, Cuba	-83.7008	22.6167	153.0801		88		1391.807
39	24	Islote Sucre (Johnny Cay), Colombia	-81.6895	12.5999	163.9517		49		704.4787
40	26	Machu Picchu, Peru	-72.545	-13.1631	731.2635		71		1886.707
41	27	Vinicunca, Peru (Rainbow Mountain)	-71.4042	-13.8496	80.15995		134		90.11446
42	28	Atacama Desert, Chile	-70	-24	250.8279		8		707.1482
43	30	Laguna Santa Rosa, Nevado Tres Cruces National Park, Chile	-69.766	-27.2256	78.31427		57		223.3009
44	30	El Chaltén, Los Glaciares National Park, Argentina	-72.9131	-49	524.2322		31		1513.538
45	30	Rio De Las Vueltas, Argentina	-72.8577	-49.3395	7.891779		104		23.58581
46	31	Laguna Torre, Santa Cruz Province, Argentina	-73.0726	-49.288	14.88186		58		10.31109
47	32	Salar de Uyuni, Bolivia	-67.5	-20.1333	769.5608		107		2037.401
48	36	Cayo Muerto, Venezuela	-68.2605	10.92978	801.1134		19		2146.464
49	36	Lencois Maranhenses, Brazil	-42.8333	-2.5333	1824.11		66		1979.668
50	37	Namib Desert, Namibia	15	-24	4220.958		89		4127.145
51	39	Cape of Good Hope, South Africa	18.4174	-34	702.3068		16		720.8518
52	41	Golden Gate Highlands, South Africa	28.58	-28.5219	782.8034		38		708.8748
53	41	Bell Park Pier, South Africa	29.43389	-28.9588	64.62293		12		59.88166
54	42	Mafia Island Marine Park, Tanzania	39.755	-7.9111	1387.895		72		1601.418
55	43	Erta Ale Volcano, Ethiopia	40.52	13.6	1134.45		32		1486.916
56	45	Forgotten Pyramids, Meroë, Sudan	33.74777	16.93627	502.5044		35		506.6696
57	45	The Great Temple of Ramesses II, Abu Simbel, Egypt	31.6328	22.336	346.1956		120		397.5617
58	46	The Temple of Horus at Edfu, Egypt	32.874	24.9759	176.4479		123		198.5529
59	46	Court of Amenhotep III, Luxor Temple, Egypt	32.6572	25.6991	44.59953		23		51.75995
60	49	Wadi Rum, Jordan	35.4259	29.5349	291.1423		136		314.504
61	50	The Monastery Ad Deir, Petra	35.4442	30.3251	44.49397		122		54.59811
62	51	Dead Sea, Wadi Mujib, Jordan	35.5	31.5	66.21029		27		81.22959
63	52	Jebel El Tih, Egypt	33.53667	29.14778	190.5642		50		200.2625
64	52	Funerary Complex of Djoser, Egypt	31.2152	29.8715	165.831		36		148.2438
65	52	Baharyia Oasis Salt Lake, Eqypt	28.932	28.352	182.0049		11		173.2164
66	55	White Desert National Park (Sahara el Beyda), Egypt	28.45	28.5744	35.92069		141		33.05862
67	55	Pamukkale, Turkey	29.1217	37.9222	567.6969		95		646.9063
68	55	Malyovishki Lakes, Bulgaria	23.31588	42.19838	480.5963		73		425.8137
69	57	Varlaam Monastery, Meteora, Greece	21.6286	39.7212	196.4368		131		192.4239
70	59	Sahara Desert, Algeria	9.799276	24.6947	1288.961		106		1244.615
71	60	Great Pyramid Of Giza, Egypt	31.1343	29.9792	1512.349		39		1356.811
72	60	Deryouk Plain, Iran	52.0561	36.0811	1477.979		29		1279.787
73	60	Harireh Historical City, Kish, Iran	53.99	26.5437	417.8143		42		668.6105
74	63	The Lut Desert, Iran	58.5	29.5	331.4927		121		342.5012
75	65	Tang Mud Volcano, Iran	59.93298	25.46699	173.5336		117		292.1041
76	65	Yenkit Beach, Oman	58.70473	23.6152	107.0415		144		149.3997
77	65	Lake Bucura, Retezat Mountains, Romania	22.9167	45.3667	2694.287		61		2498.73
78	66	Monte Piana, Italy	12.3	46.5822	737.7407		81		516.1575
79	68	The Dolomites, Italy	11.9139	46.4346	28.47413		119		20.99846
80	68	Hörnle Mountain, Germany	10.668	47.677	120.3777		44		103.9398
81	69	Alblittkopf Mountain, Austria	10.33855	47.08464	46.22499		4		43.72553
82	71	Julier Pass, Switzerland	9.79	46.4478	57.55205		53		51.07941
83	72	Mittelgipfel, Bernese Alps, Switzerland	8.019	46.571	122.6295		80		84.62657
84	73	Col Du Lautaret, Hautes-Alpes, France	6.4072	45.0333	153.3047		22		131.5563
85	74	Lac Lérié, Plateau d'Emparis, France	6.3	45.0511	7.505549		56		5.37508
86	75	Dent de Crolles from La Pravouta, France	5.872	45.3206	34.88359		28		27.9435
87	76	Aiguestortes National Park, Spain	0.99	42.6	385.8888		3		306.9104
88	76	Lac de Peyrelade, Hautes-Pyrénées, France	0.112472	42.9428	65.08045		55		50.40737
89	78	Port Esportiu de Calafat, Spain	0.8678	40.9234	148.9336		100		144.7983
90	79	Cala Tuent Mallorca, Spain	2.786	39.85	151.8255		14		125.2359
91	80	Todra Gorge, Morocco	-5.5859	31.5944	811.7471		126		737.9588
92	81	Ursa Beach, Portugal	-9.4824	38.7794	560.7745		128		542.756
93	82	Llyn Idwall, Wales, United Kingdom	-3.9386	53.08	1052.809		68		1022.395
94	82	High Cup Nick, England	-2.4579	54.648	148.8562		43		123.9736
95	84	Loch Grannoch, Scotland	-4.15	55.119	121.3195		70		74.69593
96	84	Glencoe, Scotland	-5.01	56.6822	122.9465		37		113.0034
97	86	Loch Coruisk, Scotland	-6.148	57.2	86.29814		69		55.84199
98	87	Isle of Skye, Scotland	-6.2	57.5359	23.34742		48		23.28473
99	88	Vestrahorn, Iceland	-16.6512	64.2437	852.5727		133		579.9279
100	89	Skaftafell Glacier, Iceland	-16.9993	64.0167	28.34687		111		18.86758
101	89	Jokulsarlon Glacier Lagoon, Iceland	-17.1931	64.0491	13.55758		52		6.274645
102	89	Virkisfell, Iceland	-16.6	64.73333	61.02512		135		50.47485
103	90	Hverfjall Volcano Crater, Iceland	-16.888	65.643	63.37901		45		63.39189
104	91	Thorsmork, South Highlands of Iceland	-19.4392	63.67	218.6936		125		155.7507
105	93	Þingvellir (Thingvellir) National Park, Iceland	-21.0764	64.28217	119.8571		124		65.19452
106	94	Blue Lagoon, Iceland	-22.017	63.8806	69.90899		13		39.69906
107	95	Snæfellsjökull Volcano, Iceland	-23.78	64.8	135.1113		112		82.54712
108	96	Kulusuk, Greenland	-37.1974	65.56742	927.9875		54		391.8444
109	97	Ililissat Icefjord - Greenland	-19.3098	71.89778	1293.437		46		621.6724
110	98	Eysturoy, Faroe Islands	-6.8	62.15	1084.004		33		749.6623
111	98	Finnøya, Norway	6.513205	62.79961	920.7672		34		426.6057
112	99	Ytresand Beach, Norway	13.13478	68.09911	582.4018		145		411.9637
113	100	Sennesvik, Norway	13.70969	68.10805	39.71983		108		14.82384
114	100	Andenes, Norway	16.11369	69.31923	184.7006		6		103.1101
115	101	Nordenskjold Glacier, Svalbard	15.11955	77.9091	575.5489		90		593.6849
116	102	Nuorgam, Finland	27.87166	70.08308	1013.636		91		589.5812
117	103	Lake Kel Suu, Kyrgyzstan	76.38998	40.7008	3490.883		62		2649.628
118	104	Taj Mahal, Yamuna River, India	78.0419	27.1751	235.3833		116		939.0888
119	105	Vabbinfaru Island, North Malé Atoll, Republic of Maldives	73.4527	4.27	496.5454		130		1610.966
120	107	Ameenpur, Telangana, India	78.3196	17.5	401.3132		5		971.4581
121	108	Sibinskie Lakes, Kazakhstan	82.62326	49.43387	460.2577		110		2219.048
122	108	Path to Everest, Nepal, Himalayas	86.925	28.0014	321.3165		97		1497.982
123	109	Rongbuk Glacier, Mt. Everest, Tibet	86.93	28	0.34544		105		0.319936
124	111	Lhoknga Beach, Indonesia	95.34	5.5321	570.6342		67		1647.205
125	115	Yuanyang Rice Terraces, China	102.7	23.12	537.1725		146		1310.165
126	116	Jiuzhaigou Valley, China	103.9136	33.2	180.5547		51		700.2207
127	117	Orkhon Valley, Mongolia	103.8467	46.8625	225.9525		93		943.8136
128	118	Lake Baikal Sunset, Russia	107.805	53.16296	297.7097		60		469.1876
129	118	Xe Bang Fai River, Laos	105.99	18.718	692.5757		143		2381.455
130	120	Tengger Caldera Volcano, Indonesia	112.95	-7.94	768.4251		118		1901.611
131	121	Cathedral Gorge, Australia	128.3727	-17.4805	1114.144		18		1229.346
132	123	Raja Ampat Island, New Guinea	130.8547	-0.5	765.4304		103		1185.081
133	125	Port Olry, Vanuatu	167.2	-15.4667	2651.868		101		2685.041
134	126	Plage de Ouano - New Caledonia	165	-21	400.9327		98		408.5611
135	127	South Piha Beach, New Zealand	174.4744	-36.9545	1265.437		114		1240.275
136	128	Auroa Point, New Zealand	177.9018	-39.0715	278.1027		9		236.9954
137	129	Mount Ruapehu, New Zealand	175.5636	-39.28	162.1627		86		126.0375
138	130	Wharariki Beach, Nelson, New Zealand	172.8065	-40.53	208.9346		140		169.7012
139	131	Onetahua, New Zealand (Farewell Spit)	172.734	-40.5594	5.39839		92		4.313811
140	131	Lake Rotoiti, New Zealand	172.8373	-41.815	86.34343		65		86.90091
141	134	Lake Angelus, New Zealand	173.056	-41.9862	19.13457		59		16.32192
142	134	Castle Hill, New Zealand	172.7613	-43.2516	89.10205		17		88.68822
143	134	Craigieburn Range, Southern Alps, New Zealand	171.7667	-43.18	68.88168		24		50.31574
144	136	Mount Binser, Arthur's Pass National Park, New Zealand	171.701	-42.9538	16.11586		84		15.97383
145	136	Aoraki / Mount Cook, Southern Alps, New Zealand	170.15	-43.7344	119.6451		7		94.75602
146	138	Shotover River, Queenstown, New Zealand	168.693	-45.0294	133.6527		109		114.7883
147	139	Milford Sound, The South Island of New Zealand	167.9259	-44.6416	59.12929		77		46.14909
148	140	Cape Evans, Ross Island, Antarctica	168.515	-76.7829	2172.71		15		2220.388
149
Sheet7

Cell Formulas
Range	Formula
G1	G1	=FORMULATEXT(G3)
G3:G148	G3	=XMATCH(B3:B148,TAKE(pl,,1))
I2,K7,K3	I2	=FORMULATEXT(I3)
I3:I148	I3	=DS(G3#)
K4	K4	=DS(G3#,1)
K8	K8	=SUM(I:I)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
pl	=Sheet1!$B$2:$D$147	G3, I3, K4

 

[Xlambda]

I have checked if both our distance calculation formulas are consistent, and they are.
I know that because I have switched lat and long columns, to my input data, like you did, and my distance function returned the exact value like yours.
=> the small data is obtained with the columns switched
It will be very interesting to run Solver again for the proper data set and compare the results.
All data I got so far (from top to bottom, all values (mini-sheet was great to take it all)

Book2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		MK method, minimum distance method						CK method, cluster method
2		for each indx total dist						for each indx total dist			best route CK method
3		in asc. order			best route MK method			in asc. order			=CK(15,300,1)
4		=MC()			=MK(77,,1)			=MC(,1,300)			15	83274.89
5		77	87570.87		77	0		15	83274.889		61	0
6		109	87582.82		109	46.14909		2	84635.893		73	219.7705
7		103	87619.99		7	114.7883		8	84635.893		131	192.4239
8		18	87726.1		24	89.65742		107	84635.893		95	421.8409
9		47	87733.62		84	15.97383		84	84793.458		39	560.6473
10		62	87761.3		17	57.29867		24	84816.366		36	8.87761
11		17	87808.29		59	88.68822		7	84921.29		50	148.2438
12		65	87855.17		65	16.32192		77	84983.026		136	116.8656
13		140	87938.38		92	86.90091		109	85026.509		122	54.59811
14		92	87940.26		140	4.313811		57	85055.669		27	81.22959
15		75	87957.44		86	169.7012		20	85151.084		11	449.2174
16		9	87963.37		9	126.0375		54	85151.084		141	33.05862
17		40	87964.18		114	236.9954		88	85151.084		23	326.0616
18		114	88023.43		98	1240.275		112	85151.084		123	51.75995
19		86	88063.52		101	408.5611		127	85151.084		120	198.5529
20		35	88275.36		18	2571.559		139	85151.084		35	397.5617
21		59	88361.61		103	1185.081		124	85343.208		32	506.6696
22		84	88396.22		118	1335.456		13	85358.53		42	1248.356
23		24	88403.2		67	1529.213		102	85668.402		121	342.5012
24		7	88520.41		143	1159.49		132	85692.251		117	292.1041
25		118	88761.44		146	370.8181		95	85867.571		144	149.3997
26		2	88816.41		51	700.2207		1	85923.257		29	948.1424
27		104	88929.16		40	858.5219		5	86042.728		62	1350.888
28		58	88931.58		41	629.1726		40	86042.728		110	675.0347
29		31	88952.09		85	664.6623		51	86042.728		93	990.7464
30		101	89283.6		129	1666.471		60	86042.728		60	469.1876
31		51	89517.56		75	1877.88		62	86042.728		40	970.5594
32		126	89543.48		139	1272.683		75	86042.728		41	629.1726
33		98	89593.32		87	103.7521		93	86042.728		85	664.6623
34		128	89712.73		127	241.1439		144	86042.728		129	1666.471
35		106	89734.5		21	58.37273		26	86065.454		75	1877.88
36		67	89850.91		25	109.0986		74	86065.454		139	1272.683
37		91	90058.86		113	354.9908		25	86219.608		87	103.7521
38		105	90131.41		79	30.0015		85	86355.144		127	241.1439
39		97	90131.71		115	3.077359		129	86355.144		21	58.37273
40		89	90239.91		102	131.783		41	86387.238		25	109.0986
41		15	90253.22		10	51.57622		42	86387.238		138	578.0349
42		130	90353.58		132	146.5118		105	86387.238		113	802.1158
43		116	90371.46		94	326.8178		116	86387.238		79	30.0015
44		25	90544.1		63	140.0607		117	86387.238		115	3.077359
45		12	90616.66		82	21.86973		121	86387.238		102	131.783
46		143	90621.84		64	39.55923		97	86387.776		10	51.57622
47		38	90643.7		1	81.19424		80	86857.278		132	146.5118
48		90	90728.18		137	65.88887		33	86863.641		94	326.8178
49		138	90760.95		74	171.3241		34	86863.641		63	140.0607
50		16	90850.89		78	158.2161		46	86863.641		82	21.86973
51		132	90899.24		142	590.7603		135	86863.641		64	39.55923
52		146	90930.24		30	508.5571		145	86863.641		1	81.19424
53		102	91000.48		99	556.5347		108	86881.431		137	65.88887
54		57	91239.53		20	1525.707		29	86895.374		74	171.3241
55		137	91308.51		88	339.8356		53	86914.036		78	158.2161
56		5	91322.44		49	704.4787		81	86933.753		142	590.7603
57		1	91339.64		96	289.0288		120	86960.921		30	508.5571
58		60	91396.48		47	880.1986		31	86987.863		99	556.5347
59		71	91397.41		71	1548.17		47	86987.863		20	1525.707
60		8	91438.6		134	90.11446		71	86987.863		88	339.8356
61		110	91463.89		107	504.8411		104	86998.64		49	704.4787
62		134	91478.37		8	311.3674		58	86998.808		96	289.0288
63		74	91490.24		57	223.3009		45	87036.974		47	880.1986
64		45	91570.13		31	1513.538		6	87075.909		71	1548.17
65		93	91592.78		58	21.16045		125	87140.719		134	90.11446
66		78	91602.43		104	10.31109		67	87158.67		107	504.8411
67		95	91630.63		2	2459.431		110	87158.67		8	311.3674
68		41	91661.21		76	2654.183		111	87158.67		57	223.3009
69		107	91664.72		83	38.77756		130	87158.67		31	1513.538
70		13	91695.33		15	4381.858		143	87158.67		58	21.16045
71		124	91710.15		16	4674.134		146	87158.67		104	10.31109
72		52	91735.42		38	708.8748		134	87158.929		2	2459.431
73		111	91737.69		12	59.88166		52	87162.653		76	2654.183
74		133	91752.03		89	955.0811		90	87168.22		83	38.77756
75		94	91753.79		72	1978.17		133	87168.22		101	2865.815
76		27	91800.42		32	1486.916		98	87187.367		98	408.5611
77		136	91883.81		35	506.6696		101	87187.367		77	1641.691
78		34	91902.61		120	397.5617		137	87343.192		7	126.7401
79		141	91916.85		123	198.5529		14	87345.029		24	89.65742
80		11	91925.14		23	51.75995		49	87395.31		84	15.97383
81		50	91930.44		50	244.259		91	87401.113		17	57.29867
82		64	91943.63		136	116.8656		28	87427.574		59	88.68822
83		82	91949.07		122	54.59811		43	87427.574		65	16.32192
84		63	91960.88		27	81.22959		55	87442.956		92	86.90091
85		122	92023.42		36	278.2697		83	87443.177		140	4.313811
86		26	92069.48		39	8.87761		3	87444.029		86	169.7012
87		68	92223.64		11	174.0138		22	87461.927		9	126.0375
88		120	92264.34		141	33.05862		56	87462.66		114	236.9954
89		43	92311.17		95	646.9063		76	87462.66		109	633.6432
90		113	92325.26		131	421.8409		106	87462.66		15	2193.54
91		115	92342.46		73	192.4239		126	87462.66		18	4329.153
92		79	92344.51		61	219.7705		128	87462.66		103	1185.081
93		70	92363.08		81	516.1575		73	87515.528		118	1335.456
94		10	92443.77		119	20.99846		19	87537.01		67	1529.213
95		117	92484.73		4	87.02866		30	87537.01		143	1159.49
96		144	92499.31		44	43.72553		66	87537.01		146	370.8181
97		121	92514.45		53	94.42991		82	87537.01		51	700.2207
98		14	92534.39		80	84.62657		99	87537.01		105	1070.255
99		48	92539.5		22	131.5563		142	87537.01		97	0.319936
100		69	92557.62		56	5.37508		10	87563.152		116	546.7356
101		37	92610.98		28	27.9435		63	87563.152		5	668.5918
102		55	92614.2		3	306.9104		64	87570.81		130	971.4581
103		61	92621.74		55	50.40737		44	87596.514		72	2469.96
104		3	92643.01		100	144.7983		100	87615.152		38	1599.065
105		29	92666.75		14	125.2359		113	87617.837		12	59.88166
106		100	92743.57		128	659.3221		115	87627.975		16	735.9147
107		42	92778.84		126	542.756		79	87628.225		89	720.8518
108		28	92847.46		106	1049.886		27	87633.059		106	3381.804
109		22	92850.42		68	2087.142		122	87633.059		126	1049.886
110		56	92855.23		43	123.9736		61	87638.756		128	542.756
111		44	93044.02		70	74.69593		4	87652.249		100	568.1451
112		81	93064.79		37	113.0034		94	87702.508		3	115.9907
113		119	93076.74		69	55.84199		48	87731.697		55	50.40737
114		4	93087.37		48	23.28473		68	87759.109		14	254.6058
115		53	93156.99		33	319.4184		69	87773.096		28	409.1247
116		80	93209.97		133	338.7727		70	87778.355		56	27.9435
117		39	93216.93		111	18.86758		138	87788.242		22	5.37508
118		36	93224.03		52	6.274645		119	87809.347		80	131.5563
119		32	93271.1		135	50.47485		141	87824.013		53	84.62657
120		129	93303.95		45	63.39189		136	87826.922		4	51.07941
121		20	93343.51		124	154.3228		87	87843.414		44	43.72553
122		88	93359.25		13	39.69906		11	87846.599		119	103.9398
123		127	93432.17		125	80.01764		50	87847.755		81	20.99846
124		85	93443.89		112	151.8848		131	87849.645		69	1069.045
125		21	93490.29		54	391.8444		37	87866.638		37	55.84199
126		99	93492.31		46	621.6724		39	87896.519		70	113.0034
127		139	93642.98		90	728.6931		36	87911.881		43	74.69593
128		83	93683.58		91	589.5812		21	87959.14		68	123.9736
129		76	93713.57		6	286.204		59	88081.457		48	320.3523
130		87	93738.33		108	103.1101		78	88108.231		33	319.4184
131		131	93746.29		145	14.82384		23	88141.195		34	426.6057
132		73	93921.97		34	411.9637		96	88175.115		145	411.9637
133		19	93981.15		29	2660.034		65	88210.264		108	14.82384
134		145	93993.52		121	588.4977		123	88221.021		6	103.1101
135		108	94003.02		117	292.1041		12	88281.081		91	286.204
136		46	94032.36		144	149.3997		9	88305.768		90	589.5812
137		142	94051.79		42	357.6361		16	88305.768		46	728.6931
138		6	94094.44		116	1480.639		18	88305.768		112	503.0422
139		33	94095.19		97	546.7356		32	88305.768		13	82.54712
140		23	94128.17		105	0.319936		38	88305.768		124	39.69906
141		30	94153.86		5	908.6441		72	88305.768		125	65.19452
142		123	94170.76		130	971.4581		89	88305.768		52	73.19948
143		96	94182.78		62	2523.257		103	88305.768		111	6.274645
144		112	94278.23		110	675.0347		118	88305.768		133	18.86758
145		125	94408.09		93	990.7464		140	88357.963		135	33.85822
146		135	94422.58		60	469.1876		92	88364.154		45	63.39189
147		49	94470.11		138	4964.089		114	88471.319		54	576.963
148		54	94578.15		26	1466.344		86	88523.674		26	2385.739
149		72	94719.08		19	2406.961		17	88664.456		19	2406.961
150		66	95959.65		66	1979.668		35	88906.393		66	1979.668
151
Sheet6

Cell Formulas
Range	Formula
K3,B4,H4,E4	K3	=FORMULATEXT(K4)
K4:L150	K4	=CK(15,300,1)
B5:C150	B5	=MC()
E5:F150	E5	=MK(77,,1)
H5:I150	H5	=MC(,1,300)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
pl	=Sheet1!$B$2:$D$147	B5, E5, H5, K4

 

[mgirvin]

ABYROW.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Moving Average , different approach, unpivoting, using BYROW and ABYROW
2
3					=LET(a,C5:C35,nm,6,r,ROWS(a)-nm+1,s,SEQUENCE(,nm)+SEQUENCE(r)-1,x,INDEX(a,s),BYROW(x,LAMBDA(a,AVERAGE(a))))
4		Month End	Sales		↓		=LET(a,C5:C35,nm,6,ABYROW(SEQUENCE(ROWS(a)-nm+1),LAMBDA(i,AVERAGE(INDEX(a,SEQUENCE(,6)+i-1)))))
5		31-07-22	614,212		494,631		494,631
6		30-06-22	551,250		456,528		456,528		To prove that ABYROW is "natively" capable (BYROW is not)
7		31-05-22	352,220		410,297		410,297		to "spread" (spill) a horizontal pattern from a vertical array (single values/row).
8		30-04-22	607,028		405,090		405,090		ABYROW is can embed a pattern created from a column.
9		31-03-22	494,401		342,149		342,149		=ABYROW(SEQUENCE(26),LAMBDA(i,SEQUENCE(,6)+i-1))
10		28-02-22	348,672		283,648		283,648		1	2	3	4	5	6
11		31-01-22	385,596		273,681		273,681		2	3	4	5	6	7
12		31-12-21	273,864		279,941		279,941		3	4	5	6	7	8
13		30-11-21	320,980		282,871		282,871		4	5	6	7	8	9
14		31-10-21	229,383		328,734		328,734		5	6	7	8	9	10
15		30-09-21	143,395		337,529		337,529		6	7	8	9	10	11
16		31-08-21	288,865		394,946		394,946		7	8	9	10	11	12
17		31-07-21	423,159		393,964		393,964		8	9	10	11	12	13
18		30-06-21	291,445		385,404		385,404		9	10	11	12	13	14
19		31-05-21	596,158		366,708		366,708		10	11	12	13	14	15
20		30-04-21	282,149		314,443		314,443		11	12	13	14	15	16
21		31-03-21	487,897		358,810		358,810		12	13	14	15	16	17
22		28-02-21	282,977		315,192		315,192		13	14	15	16	17	18
23		31-01-21	371,800		307,968		307,968		14	15	16	17	18	19
24		31-12-20	179,265		289,454		289,454		15	16	17	18	19	20
25		30-11-20	282,570		282,478		282,478		16	17	18	19	20	21
26		31-10-20	548,348		268,041		268,041		17	18	19	20	21	22
27		30-09-20	226,192		219,677		219,677		18	19	20	21	22	23
28		31-08-20	239,634		260,147		260,147		19	20	21	22	23	24
29		31-07-20	260,717		295,960		295,960		20	21	22	23	24	25
30		30-06-20	137,409		278,240		278,240		21	22	23	24	25	26
31		31-05-20	195,947						22	23	24	25	26	27
32		30-04-20	258,165						23	24	25	26	27	28
33		31-03-20	469,009						24	25	26	27	28	29
34		29-02-20	454,513						25	26	27	28	29	30
35		31-01-20	154,396						26	27	28	29	30	31
36
mov avrg 2

Cell Formulas
Range	Formula
E3	E3	=FORMULATEXT(E5)
G4,I9	G4	=FORMULATEXT(G5)
E5:E30	E5	=LET(a,C5:C35,nm,6,r,ROWS(a)-nm+1,s,SEQUENCE(,nm)+SEQUENCE(r)-1,x,INDEX(a,s),BYROW(x,LAMBDA(a,AVERAGE(a))))
G5:G30	G5	=LET(a,C5:C35,nm,6,ABYROW(SEQUENCE(ROWS(a)-nm+1),LAMBDA(i,AVERAGE(INDEX(a,SEQUENCE(,6)+i-1)))))
I10:N35	I10	=ABYROW(SEQUENCE(26),LAMBDA(i,SEQUENCE(,6)+i-1))
Dynamic array formulas.

Xlambda, I am going through this old post one message at a time, clicking the thumbs ups (which I never noticed before), and I ran into this spilled moving average post. So I immediately tried to do it. Then, because I have not been using Excel for almost 5 months, I am so rusty that it took me over 2 hours to come up with a formula lol*100 Here is what I imagined:

=LET(v,C5:C35,b,7,rs,ROWS(v),s,SEQUENCE(rs),IF(s<b,"",MAP(s,LAMBDA(r,AVERAGE(INDEX(v,SEQUENCE(b,,r-G1+1)))))))

after two hours, I can now move on to the next post...

 

[Xlambda]

Hi Mike, the Excel Legend Himself. You are not rusty at all; you came back like a storm. I am so unimaginably honored to have you here. But these are sooooo old.
Today we can solve any of those old tasks only with 3 functions,
I think that for the last 3 years, every YT you've made, that implied formulas, has a study posted around here on a thread.
What is amazing and nostalgic, is that after all the experience accumulated these years, to solve this, I will still use the "pivot" concept to this day.
=LET(v,C3:C33,n,6,BYROW(INDEX(v,SEQUENCE(ROWS(v)-n+1)+SEQUENCE(,n)-1),AVERAGE))

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1					single cell formula
2		Month End	Sales		=LET(v,C3:C33,n,6,BYROW(INDEX(v,SEQUENCE(ROWS(v)-n+1)+SEQUENCE(,n)-1),AVERAGE))
3		31-07-22	614212		494630.5
4		30-06-22	551250		456527.8		Concept: Moving average for n=6 elements for a clm vector that has r rows 3 steps
5		31-05-22	352220		410296.8
6		30-04-22	607028		405090.2		creating the index pattern n=6, r=rows(c3:c33)=31
7		31-03-22	494401		342149.3		=seq(r-n+1)+seq(,n)-1
8		28-02-22	348672		283648.3									extracting values
9		31-01-22	385596		273680.5		=SEQUENCE(26)+SEQUENCE(,6)-1							=INDEX(C3:C33,G10#)							=BYROW(N10#,AVERAGE)
10		31-12-21	273864		279941		1	2	3	4	5	6		614212	551250	352220	607028	494401	348672		494630.5
11		30-11-21	320980		282871.2		2	3	4	5	6	7		551250	352220	607028	494401	348672	385596		456527.8
12		31-10-21	229383		328734.2		3	4	5	6	7	8		352220	607028	494401	348672	385596	273864		410296.8
13		30-09-21	143395		337528.5		4	5	6	7	8	9		607028	494401	348672	385596	273864	320980		405090.2
14		31-08-21	288865		394945.5		5	6	7	8	9	10		494401	348672	385596	273864	320980	229383		342149.3
15		31-07-21	423159		393964.2		6	7	8	9	10	11		348672	385596	273864	320980	229383	143395		283648.3
16		30-06-21	291445		385404.3		7	8	9	10	11	12		385596	273864	320980	229383	143395	288865		273680.5
17		31-05-21	596158		366707.7		8	9	10	11	12	13		273864	320980	229383	143395	288865	423159		279941
18		30-04-21	282149		314443		9	10	11	12	13	14		320980	229383	143395	288865	423159	291445		282871.2
19		31-03-21	487897		358809.5		10	11	12	13	14	15		229383	143395	288865	423159	291445	596158		328734.2
20		28-02-21	282977		315192		11	12	13	14	15	16		143395	288865	423159	291445	596158	282149		337528.5
21		31-01-21	371800		307968.2		12	13	14	15	16	17		288865	423159	291445	596158	282149	487897		394945.5
22		31-12-20	179265		289454.3		13	14	15	16	17	18		423159	291445	596158	282149	487897	282977		393964.2
23		30-11-20	282570		282478.3		14	15	16	17	18	19		291445	596158	282149	487897	282977	371800		385404.3
24		31-10-20	548348		268041.2		15	16	17	18	19	20		596158	282149	487897	282977	371800	179265		366707.7
25		30-09-20	226192		219677.3		16	17	18	19	20	21		282149	487897	282977	371800	179265	282570		314443
26		31-08-20	239634		260146.8		17	18	19	20	21	22		487897	282977	371800	179265	282570	548348		358809.5
27		31-07-20	260717		295960		18	19	20	21	22	23		282977	371800	179265	282570	548348	226192		315192
28		30-06-20	137409		278239.8		19	20	21	22	23	24		371800	179265	282570	548348	226192	239634		307968.2
29		31-05-20	195947				20	21	22	23	24	25		179265	282570	548348	226192	239634	260717		289454.3
30		30-04-20	258165				21	22	23	24	25	26		282570	548348	226192	239634	260717	137409		282478.3
31		31-03-20	469009				22	23	24	25	26	27		548348	226192	239634	260717	137409	195947		268041.2
32		29-02-20	454513				23	24	25	26	27	28		226192	239634	260717	137409	195947	258165		219677.3
33		31-01-20	154396				24	25	26	27	28	29		239634	260717	137409	195947	258165	469009		260146.8
34							25	26	27	28	29	30		260717	137409	195947	258165	469009	454513		295960
35							26	27	28	29	30	31		137409	195947	258165	469009	454513	154396		278239.8
36
Sheet1

Cell Formulas
Range	Formula
E2,U9,N9,G9	E2	=FORMULATEXT(E3)
E3:E28	E3	=LET(v,C3:C33,n,6,BYROW(INDEX(v,SEQUENCE(ROWS(v)-n+1)+SEQUENCE(,n)-1),AVERAGE))
G10:L35	G10	=SEQUENCE(26)+SEQUENCE(,6)-1
N10:S35	N10	=INDEX(C3:C33,G10#)
U10:U35	U10	=BYROW(N10#,AVERAGE)
Dynamic array formulas.

 

[Xlambda]

And this is with MAP alternative , (your concept)
byrow/map
=LET(v,C3:C33,n,6,BYROW(INDEX(v,SEQUENCE(ROWS(v)-n+1)+SEQUENCE(,n)-1),AVERAGE))
=LET(v,C3:C33,n,6,MAP(SEQUENCE(ROWS(v)-n+1),LAMBDA(x,AVERAGE(INDEX(v,SEQUENCE(n,,x))))))

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1					single cell formula
2		Month End	Sales		=LET(v,C3:C33,n,6,BYROW(INDEX(v,SEQUENCE(ROWS(v)-n+1)+SEQUENCE(,n)-1),AVERAGE))
3		31-07-22	614212		494630.5
4		30-06-22	551250		456527.8		=LET(v,C3:C33,n,6,MAP(SEQUENCE(ROWS(v)-n+1),LAMBDA(x,AVERAGE(INDEX(v,SEQUENCE(n,,x))))))
5		31-05-22	352220		410296.8		494630.5
6		30-04-22	607028		405090.2		456527.8
7		31-03-22	494401		342149.3		410296.8
8		28-02-22	348672		283648.3		405090.2
9		31-01-22	385596		273680.5		342149.3
10		31-12-21	273864		279941		283648.3
11		30-11-21	320980		282871.2		273680.5
12		31-10-21	229383		328734.2		279941
13		30-09-21	143395		337528.5		282871.2
14		31-08-21	288865		394945.5		328734.2
15		31-07-21	423159		393964.2		337528.5
16		30-06-21	291445		385404.3		394945.5
17		31-05-21	596158		366707.7		393964.2
18		30-04-21	282149		314443		385404.3
19		31-03-21	487897		358809.5		366707.7
20		28-02-21	282977		315192		314443
21		31-01-21	371800		307968.2		358809.5
22		31-12-20	179265		289454.3		315192
23		30-11-20	282570		282478.3		307968.2
24		31-10-20	548348		268041.2		289454.3
25		30-09-20	226192		219677.3		282478.3
26		31-08-20	239634		260146.8		268041.2
27		31-07-20	260717		295960		219677.3
28		30-06-20	137409		278239.8		260146.8
29		31-05-20	195947				295960
30		30-04-20	258165				278239.8
31		31-03-20	469009
32		29-02-20	454513
33		31-01-20	154396
34
Sheet1

Cell Formulas
Range	Formula
E2,G4	E2	=FORMULATEXT(E3)
E3:E28	E3	=LET(v,C3:C33,n,6,BYROW(INDEX(v,SEQUENCE(ROWS(v)-n+1)+SEQUENCE(,n)-1),AVERAGE))
G5:G30	G5	=LET(v,C3:C33,n,6,MAP(SEQUENCE(ROWS(v)-n+1),LAMBDA(x,AVERAGE(INDEX(v,SEQUENCE(n,,x))))))
Dynamic array formulas.

 

[mgirvin]

Thanks for the reply back. Of course: Eta Lambdas : ) : ) : ) I can't wait to try these!!!! Thanks, Excel Lambda!

 

[Xlambda]

Hi everyone, something unbelievable happened and Mike needs our support: Microsoft Revokes excelisfun MVP Status!

If you were missing solving real life Excel problems with simple formulas, alternative concept of using REDUCE, check this out, Mike's latest excel magic trick:

Solutions if codes are consistent (6digits only) , but matching pos different

EMT1860.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1					consistent patterns (codes same digits and same pos)				codes consistent but start pos of "/" varies
2		Value	Code		Description				Description				=XLOOKUP(--MID(E3:E26,FIND("/",E3:E26)+1,6),VC[Code],VC[Value])
3		A1	700200		555/720100 WA sreet PND-Exempt Wages		B3		555/720100 WA sreet PND-Exempt Wages		B3		B3
4		A2	700300		555/720100 QN sreet PND- Communication		B3		555/720100 QN sreet PND- Communication		B3		B3
5		A3	700301		555/710300 QM sreet PND-Exempt		B1		5551/710300 QM sreet PND-Exempt		#VALUE!		B1
6		B1	710300		555/580100 CD sreet PND- G		C2		555/580100 CD sreet PND- G		C2		C2
7		B2	710400		555/720300 VJ sreet PND- Communication		B4		555/720300 VJ sreet PND- Communication		B4		B4
8		B3	720100		555/710400 VI sreet PND-WA Paid Medic		B2		555/710400 VI sreet PND-WA Paid Medic		B2		B2
9		B4	720300		555/947000 VS sreet PND- G		B6		555/947000 VS sreet PND- G		B6		B6
10		B5	720500		555/700301 CW sreet PND- Communication		A3		555023/700301 CW sreet PND- Communication		#VALUE!		A3
11		B6	947000		555/947000 PM sreet PND- Communication		B6		555/947000 PM sreet PND- Communication		B6		B6
12		C1	810601		555/947000 KK sreet PND-Exempt Wages		B6		555/947000 KK sreet PND-Exempt Wages		B6		B6
13		C2	580100		555/700200 XX sreet PND-Overtime Wage		A1		555/700200 XX sreet PND-Overtime Wage		A1		A1
14		C3	712000		555/700300 KA sreet PND-Overtime Wage		A2		555/700300 KA sreet PND-Overtime Wage		A2		A2
15					555/700200 YB sreet PND-Exempt Wages		A1		555/700200 YB sreet PND-Exempt Wages		A1		A1
16					555/947000 EO sreet PND-Medical		B6		555/947000 EO sreet PND-Medical		B6		B6
17					555/710400 QS sreet 580100 PND- Communication		B2		555/710400 QS sreet 580100 PND- Communication		B2		B2
18					555/720300 XQ sreet PND-Overtime Wage		B4		555/720300 XQ sreet PND-Overtime Wage		B4		B4
19					555/810601 VC sreet PND-Exempt Wages		C1		555/810601 VC sreet PND-Exempt Wages		C1		C1
20					555/700200 EX sreet PND-Exempt		A1		555/700200 EX sreet PND-Exempt		A1		A1
21					555/700301 CH sreet PND-WA Paid Medic		A3		555/700301 CH sreet PND-WA Paid Medic		A3		A3
22					555/700200 DW sreet PND-Overtime Wage		A1		555/700200 DW sreet PND-Overtime Wage		A1		A1
23					555/720500 RH sreet PND-Medical		B5		555/720500 RH sreet PND-Medical		B5		B5
24					555/710400 ZB sreet PND-Exempt		B2		555/710400 ZB sreet PND-Exempt		B2		B2
25					555/580100 RC sreet PND-Exempt		C2		555/580100 RC sreet PND-Exempt		C2		C2
26					555/710400 ZQ sreet PND- G		B2		555/710400 ZQ sreet PND- G		B2		B2
27							↑↑↑				↑↑↑
28							=XLOOKUP(--MID(E3:E26,5,6),VC[Code],VC[Value])				=XLOOKUP(--MID(I3:I26,5,6),VC[Code],VC[Value])
29
1860

Cell Formulas
Range	Formula
M2	M2	=FORMULATEXT(M3)
G3:G26,K3:K26	G3	=XLOOKUP(--MID(E3:E26,5,6),VC[Code],VC[Value])
M3:M26	M3	=XLOOKUP(--MID(E3:E26,FIND("/",E3:E26)+1,6),VC[Code],VC[Value])
G28,K28	G28	=FORMULATEXT(G3)
Dynamic array formulas.

Solution to cover any scenarios, codes text or numbers, diff lengths, posible dups matching in a cell

EMT1860.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1
2							=TRIM(REDUCE("",SEQUENCE(ROWS(VC)),LAMBDA(v,i,v&" "&IF(REGEXTEST(E5:E28,INDEX(Vb,i,2)),INDEX(Vb,i,1),""))))
3		codes dif len, text or nr.			cells have dups		↓↓↓↓		if we want "," delim
4		Value	Code		Description		↓↓↓↓		=SUBSTITUTE(TRIM(REDUCE("",SEQUENCE(ROWS(VC)),LAMBDA(v,i,v&" "&IF(REGEXTEST(E5:E28,INDEX(Vb,i,2)),INDEX(Vb,i,1),""))))," ",",")
5		A1	700200		555/720100 WA sreet PND-Exempt Wages		B3		B3
6		A2	a700300		555/720100 QN sreet PND- Communication		B3		B3
7		A3	700301		555/710300 QM sreet PND-Exempt		B1		B1
8		B1	710300		555/580100 CD sreet PND- G		C2		C2
9		B2	c710400d		555/720300 VJ sreet PND- Communication		B4		B4
10		B3	720100		xx c710400d VI sreet PND-WA Paid Medic 720300/ 947000		B2 B4 B6		B2,B4,B6
11		B4	720300		555/947000 VS sreet PND- G		B6		B6
12		B5	720500		555/700301 CW sreet PND- Communication		A3		A3
13		B6	947000		555rtyiuyt 947000 fg /580100 PM sreet PND- Communication		B6 C2		B6,C2
14		C1	810601		555/947000 KK sreet PND-Exempt Wages		B6		B6
15		C2	580100		555/700200 XX sreet PND-Overtime Wage		A1		A1
16		C3	712000		3xr a700300 KA sreet PND-Overtime Wage		A2		A2
17					555/700200 YB sreet PND-Exempt Wages		A1		A1
18					555/947000 EO sreet PND-Medical		B6		B6
19					555/710400 QS sreet 580100 PND- Communication		C2		C2
20					555/720300 a700300 XQ sreet PND-Overtime Wage 720500		A2 B4 B5		A2,B4,B5
21					555/810601 VC sreet PND-Exempt Wages		C1		C1
22					555/700200 EX sreet PND-Exempt		A1		A1
23					555/700301 CH sreet PND-WA Paid Medic		A3		A3
24					555/700200 DW sreet PND-Overtime Wage		A1		A1
25					555/720500 RH sreet PND-Medical		B5		B5
26					555/457 c710400d ZB sreet PND-Exempt		B2		B2
27					555/580100 RC sreet PND-Exempt		C2		C2
28					c710400d ZQ sreet PND- G a700300		A2 B2		A2,B2
29
1860

Cell Formulas
Range	Formula
G2	G2	=FORMULATEXT(G5)
I4	I4	=FORMULATEXT(I5)
G5:G28	G5	=TRIM(REDUCE("",SEQUENCE(ROWS(VC)),LAMBDA(v,i,v&" "&IF(REGEXTEST(E5:E28,INDEX(Vb,i,2)),INDEX(Vb,i,1),""))))
I5:I28	I5	=SUBSTITUTE(TRIM(REDUCE("",SEQUENCE(ROWS(VC)),LAMBDA(v,i,v&" "&IF(REGEXTEST(E5:E28,INDEX(Vb,i,2)),INDEX(Vb,i,1),""))))," ",",")
Dynamic array formulas.

 

[mgirvin]

Thank you for the wonderful alternative formulas, Teammate Excel Lambda!!!!! It is wonderful to be on the Team with you. Now we are both non-MVPs. But guess what? We still make the world a better place every day by helping others with Excel and having fun doing it!!!! Go Team!!!!!

 

[Xlambda]

A quicky one, alternative to PQ using SCAN

Challenge: Group the rows from top which in each group the total cost be lower than 130

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2					single cell formula
3					=SCAN(0,C4:C20=SCAN(0,C4:C20,LAMBDA(v,i,IF(v+i<130,v+i,i))),SUM)
4		03-01-24	33		1
5		04-01-24	54		1
6		05-01-24	54		2
7		06-01-24	45		2
8		07-01-24	21		2
9		08-01-24	42		3
10		09-01-24	60		3
11		10-01-24	78		4
12		11-01-24	33		4
13		12-01-24	44		5
14		13-01-24	12		5
15		14-01-24	31		5
16		15-01-24	30		5
17		16-01-24	47		6
18		17-01-24	110		7
19		18-01-24	55		8
20		19-01-24	47		8
21
22					Concept.
23					step 1.		step 2.		step 3.
24					=SCAN(0,C4:C20,LAMBDA(v,i,IF(v+i<130,v+i,i)))
25							=C4:C20=E26#		=SCAN(0,--G26#,SUM)
26					33		TRUE		1
27					87		FALSE		1
28					54		TRUE		2
29					99		FALSE		2
30					120		FALSE		2
31					42		TRUE		3
32					102		FALSE		3
33					78		TRUE		4
34					111		FALSE		4
35					44		TRUE		5
36					56		FALSE		5
37					87		FALSE		5
38					117		FALSE		5
39					47		TRUE		6
40					110		TRUE		7
41					55		TRUE		8
42					102		FALSE		8
43
Sheet1

Cell Formulas
Range	Formula
E3,G25,I25	E3	=FORMULATEXT(E4)
E4:E20	E4	=SCAN(0,C4:C20=SCAN(0,C4:C20,LAMBDA(v,i,IF(v+i<130,v+i,i))),SUM)
E24	E24	=FORMULATEXT(E26)
E26:E42	E26	=SCAN(0,C4:C20,LAMBDA(v,i,IF(v+i<130,v+i,i)))
G26:G42	G26	=C4:C20=E26#
I26:I42	I26	=SCAN(0,--G26#,SUM)
Dynamic array formulas.

 

[Xlambda]

Sunday short. first draft. Jon Excel Campus had a super cool idea using checkboxes that I thought deserved to be shared.

The challenge was triggered by the fact that only one checkbox details was shown at a time even if's enough room to show more chkbxes at the same time natural aligned, whatever checkboxes are selected.
Is super fun to play with them and useful easy to compare the revealed details.
Main function DET (Details) is based on a simple REDUCE and calls a tool function KI (k Insert) that was designed to insert an array at "k" index row level of other array to the nearest gap that has enough room for it.
Jon's file download link : The Show Details Checkbox: Advanced Excel Formulas - Excel Campus

DET(lv,la,ra,h,cx) Details Function, calls KI where: lv: lookup vect/val ; la: lookup array ; ra: return array ; h: headers ; cx: checkboxes vector (or simple true/false or 0/1 array)

=LAMBDA(lv, la, ra, h, cx,
    LET(
        s, SEQUENCE(ROWS(lv)) * cx,
        f, FILTER(s, s),
        r, REDUCE(0, f, LAMBDA(v, i, LET(x, INDEX(lv, i), y, VSTACK(h, FILTER(ra, x = la, "")), IF(AND(v = 0), KI(y, i), KI(y, i, v))))),
        IFERROR(r, "")
    )
)

KI(a,k,[ b]) Kindex Insert , inserts array "a" into array's "b" closest gaps that can acomodate "a" at "k" row level.

=LAMBDA(a, k, [b],
    LET(
        d, IF(k < 2, a, IFNA(VSTACK(T(SEQUENCE(k - 1)), a), "")),
        IF(
            ISOMITTED(b),
            d,
            LET(
                r, ROWS(a),
                l, COLUMNS(b),
                e, EXPAND(b, MAX(ROWS(d), ROWS(b)), , ""),
                y, IFNA(XMATCH(TRUE, BYCOL(e, LAMBDA(y, IFNA(XMATCH(1, --(DROP(y, k - 1) <> "")), r + 1))) > r), l + 1),
                f, IF(y - 1, HSTACK(T(SEQUENCE(k + r - 1, y - 1)), d), d),
                IFNA(IFNA(IF(f <> "", f, b), b), "")
            )
        )
    )
)

Note: Checkboxes grafic does not show in minisheet. Check photo.

Details .xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
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								main example
5										=DET(To[Order],Tb[Order ID],Tb[[Product Name]:[Qty]],Tb[[#Headers],[Product Name]:[Qty]],To[Details])
6		Order	Company Name	Date	Paid	Shipping	Items	Details		↓↓↓↓↓
7		202025	Ocean's Edge Surf	14-07-24	Yes	Ground	1			Product Name	Qty
8		202126	Oceanfront Surf	10-07-24	Yes	Ground	3			SwiftStream Adventure	2
9		202037	Ocean Trail	20-07-24	Yes	Ground	2			Product Name	Qty
10		202106	Pacific Waves	16-07-24	Yes	Next Day	5			SurfStream Balance Cushion	1
11		202140	Breaker Point Surf	20-07-24	No	Next Day	1			SunShield Rash Guard	1
12		202121	Ocean Explorer	13-07-24	Yes	Ground	5			Product Name	Qty
13		201921	Coastline Surf	19-07-24	Yes	Ground	1			Wave Rider Surf Series	1
14		201950	Maritime Surf	18-07-24	Yes	Air	2			ReefFlex Anti-Slip Deck Pad	1	Product Name	Qty
15		201934	Ocean Mist Surf	18-07-24	No	Ground	1			Logo Tshirt	1	ReefFlex Anti-Slip Deck Pad	1
16		202011	Surf Gateway	19-07-24	Yes	Air	5			SunBask Leisure Paddle	1	ReefSafe UV Leggings	2
17		201990	Oceanfront Surf	09-07-24	No	Ground	5			Logo Tshirt	1
18		202167	Surf Expedition	19-07-24	No	Ground	4			Product Name	Qty
19		202081	Wave Serenity	17-07-24	No	Ground	5			Logo Tshirt	5
20		202148	Ocean's Edge Surf	13-07-24	Yes	Ground	4			Logo Tshirt	4	Product Name	Qty
21		202051	Surf Sanctuary	18-07-24	No	Ground	1			SunShield Rash Guard	1	SunShield Rash Guard	1
22		201912	Swell Surf Co.	08-07-24	No	Air	1			SunShield Rash Guard	1	SunShield Rash Guard	2
23		202008	Surf Sanctuary	10-07-24	No	Ground	4			Product Name	Qty	ShoreLine Collapsible Paddle	1
24		202049	Beach Breeze Surf	11-07-24	No	Ground	1			ReefFlex Anti-Slip Deck Pad	1	HydroCap Waterproof Hat	1
25		202003	Beach Patrol Surf	12-07-24	Yes	Ground	1			Wave Rider Surf Series	1
26		202095	Surfside Shack	14-07-24	No	Ground	1			Carve Performance Board	1	Product Name	Qty
27		201972	Breaker Point Surf	09-07-24	No	Ground	1			Blue Horizon Explorer	1	HydroThrive Safety Leash	1
28		202169	Wave Reef	09-07-24	No	Ground	5
29		202130	Maritime Surf	15-07-24	No	Next Day	1			Product Name	Qty
30		202085	Surf Escape	11-07-24	Yes	Ground	2			HydroThrive Safety Leash	1	Product Name	Qty
31		201986	Wave Bliss	19-07-24	No	Ground	1			Product Name	Qty	ReefFlex Anti-Slip Deck Pad	1
32		202059	Surf Melody	19-07-24	No	Ground	4			HydroThrive Safety Leash	1	HydroThrive Safety Leash	5
33		202137	Ocean Explorer	13-07-24	No	Air	1			Product Name	Qty
34		201945	Surf Crafters	11-07-24	No	Ground	4			Wave Rider Surf Series	4
35		201982	Surf Coast	12-07-24	Yes	Ground	1
36		202151	Sea Rider Surf	11-07-24	No	Ground	6
37		201993	Surf Gateway	13-07-24	Yes	Ground	1			Product Name	Qty
38		202036	Tide Pools Surf	13-07-24	No	Ground	1			HydroThrive Safety Leash	2	Product Name	Qty
39		202073	Ocean Mist Surf	13-07-24	No	Ground	6			Product Name	Qty	Logo Tshirt	4
40										ReefFlex Anti-Slip Deck Pad	4
41										TideProof Paddle Jacket	2
42										SeaPulse GPS Tracker	2
43										SeaPulse GPS Tracker	1
44										Logo Tshirt	1
45										Sea Bird Cruiser	2
46
Sheet1

Cell Formulas
Range	Formula
J5	J5	=FORMULATEXT(J7)
J7:M45	J7	=DET(To[Order],Tb[Order ID],Tb[[Product Name]:[Qty]],Tb[[#Headers],[Product Name]:[Qty]],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