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.

 

[st001]

For the current project / assignment I am working on, this is what (following a principled approach), worked for me.
Repeats each column of an array 3 times, before sequentially repeating the next column 3 times.
I have more formally turned this into a function, and given it a name like COLSRPT_N_TIMES, so I can rapidly reuse and apply it to the other places I need to, to get to the right output view, to lead on with the other calculations that I need to do.

=INDEX(F11#,SEQUENCE(ROWS(F11#),1,1),QUOTIENT(SEQUENCE(, COLUMNS(F11#)*3,3,1),3))

 

[Xlambda]

Super cool and useful video, latest MrExcel's YT: Excel Formula To Extract Country From Phone Number - 2370
All country calling codes stacked in descending order, defined name: "cc"

Book1
	A	B	C	D
1		Country Calling Codes:
2		=cc
3		441624	Isle of Man
4		441534	Jersey
5		441481	Guernsey
6		1939	Puerto Rico
7		1905	Canada
8		1902	Canada
9		1876	Jamaica
10		1873	Canada
11		1869	Saint Kitts and Nevis
12		1868	Trinidad and Tobago
13		1867	Canada
14		1849	Dominican Republic
15		1829	Dominican Republic
16		1825	Canada
17		1819	Canada
18		1809	Dominican Republic
19		1807	Canada
20		1787	Puerto Rico
21		1784	Saint Vincent and the Grenadines
22		1782	Canada
23		1780	Canada
24		1778	Canada
25		1767	Dominica
26		1758	Saint Lucia
27		1753	Canada
28		1742	Canada
29		1721	Sint Maarten
30		1709	Canada
31		1705	Canada
32		1684	American Samoa
33		1683	Canada
34		1672	Canada
35		1671	Guam
36		1670	Northern Mariana Islands
37		1664	Montserrat
38		1649	Turks and Caicos Islands
39		1647	Canada
40		1639	Canada
41		1613	Canada
42		1604	Canada
43		1587	Canada
44		1584	Canada
45		1581	Canada
46		1579	Canada
47		1548	Canada
48		1519	Canada
49		1514	Canada
50		1506	Canada
51		1474	Canada
52		1473	Grenada
53		1468	Canada
54		1450	Canada
55		1441	Bermuda
56		1438	Canada
57		1437	Canada
58		1431	Canada
59		1418	Canada
60		1416	Canada
61		1403	Canada
62		1368	Canada
63		1367	Canada
64		1365	Canada
65		1354	Canada
66		1345	Cayman Islands
67		1343	Canada
68		1340	U.S. Virgin Islands
69		1306	Canada
70		1289	Canada
71		1284	British Virgin Islands
72		1268	Antigua and Barbuda
73		1264	Anguilla
74		1263	Canada
75		1250	Canada
76		1249	Canada
77		1246	Barbados
78		1242	Bahamas
79		1236	Canada
80		1226	Canada
81		1204	Canada
82		998	Uzbekistan
83		996	Kyrgyzstan
84		995	Georgia
85		994	Azerbaijan
86		993	Turkmenistan
87		992	Tajikistan
88		977	Nepal
89		976	Mongolia
90		975	Bhutan
91		974	Qatar
92		973	Bahrain
93		972	Israel
94		971	United Arab Emirates
95		970	Palestine
96		968	Oman
97		967	Yemen
98		966	Saudi Arabia
99		965	Kuwait
100		964	Iraq
101		963	Syria
102		962	Jordan
103		961	Lebanon
104		960	Maldives
105		886	Taiwan
106		880	Bangladesh
107		856	Laos
108		855	Cambodia
109		853	Macau
110		852	Hong Kong
111		850	North Korea
112		692	Marshall Islands
113		691	Micronesia
114		690	Tokelau
115		689	French Polynesia
116		688	Tuvalu
117		687	New Caledonia
118		686	Kiribati
119		685	Samoa
120		683	Niue
121		682	Cook Islands
122		681	Wallis and Futuna
123		680	Palau
124		679	Fiji
125		678	Vanuatu
126		677	Solomon Islands
127		676	Tonga
128		675	Papua New Guinea
129		674	Nauru
130		673	Brunei
131		672	Antarctica
132		670	East Timor
133		599	Curacao or Netherlands Antilles
134		598	Uruguay
135		597	Suriname
136		595	Paraguay
137		593	Ecuador
138		592	Guyana
139		591	Bolivia
140		590	Saint Martin or Saint Bart
141		509	Haiti
142		508	Saint Pierre and Miquelon
143		507	Panama
144		506	Costa Rica
145		505	Nicaragua
146		504	Honduras
147		503	El Salvador
148		502	Guatemala
149		501	Belize
150		500	Falkland Islands
151		423	Liechtenstein
152		421	Slovakia
153		420	Czech Republic
154		389	Macedonia
155		387	Bosnia and Herzegovina
156		386	Slovenia
157		385	Croatia
158		383	Kosovo
159		382	Montenegro
160		381	Serbia
161		380	Ukraine
162		379	Vatican
163		378	San Marino
164		377	Monaco
165		376	Andorra
166		375	Belarus
167		374	Armenia
168		373	Moldova
169		372	Estonia
170		371	Latvia
171		370	Lithuania
172		359	Bulgaria
173		358	Finland
174		357	Cyprus
175		356	Malta
176		355	Albania
177		354	Iceland
178		353	Ireland
179		352	Luxembourg
180		351	Portugal
181		350	Gibraltar
182		299	Greenland
183		298	Faroe Islands
184		297	Aruba
185		291	Eritrea
186		290	Saint Helena
187		269	Comoros
188		268	Swaziland
189		267	Botswana
190		266	Lesotho
191		265	Malawi
192		264	Namibia
193		263	Zimbabwe
194		262	Mayotte or Reunion
195		261	Madagascar
196		260	Zambia
197		258	Mozambique
198		257	Burundi
199		256	Uganda
200		255	Tanzania
201		254	Kenya
202		253	Djibouti
203		252	Somalia
204		251	Ethiopia
205		250	Rwanda
206		249	Sudan
207		248	Seychelles
208		246	British Indian Ocean Territory
209		245	Guinea-Bissau
210		244	Angola
211		243	Democratic Republic of the Congo
212		242	Republic of the Congo
213		241	Gabon
214		240	Equatorial Guinea
215		239	Sao Tome and Principe
216		238	Cape Verde
217		237	Cameroon
218		236	Central African Republic
219		235	Chad
220		234	Nigeria
221		233	Ghana
222		232	Sierra Leone
223		231	Liberia
224		230	Mauritius
225		229	Benin
226		228	Togo
227		227	Niger
228		226	Burkina Faso
229		225	Ivory Coast
230		224	Guinea
231		223	Mali
232		222	Mauritania
233		221	Senegal
234		220	Gambia
235		218	Libya
236		216	Tunisia
237		213	Algeria
238		212	Morocco or Western Sahara
239		211	South Sudan
240		98	Iran
241		95	Myanmar
242		94	Sri Lanka
243		93	Afghanistan
244		92	Pakistan
245		91	India
246		90	Turkey
247		86	China
248		84	Vietnam
249		82	South Korea
250		81	Japan
251		66	Thailand
252		65	Singapore
253		64	New Zealand, Pitcairn
254		63	Philippines
255		62	Indonesia
256		61	Australia, Christmas Island, Cocos Islands
257		60	Malaysia
258		58	Venezuela
259		57	Colombia
260		56	Chile
261		55	Brazil
262		54	Argentina
263		53	Cuba
264		52	Mexico
265		51	Peru
266		49	Germany
267		48	Poland
268		47	Norway, Svalbard and Jan Mayen
269		46	Sweden
270		45	Denmark
271		44	United Kingdom
272		43	Austria
273		41	Switzerland
274		40	Romania
275		39	Italy
276		36	Hungary
277		34	Spain
278		33	France
279		32	Belgium
280		31	Netherlands
281		30	Greece
282		27	South Africa
283		20	Egypt
284		7	Russia or Kazakhstan
285		1	United States
286
Sheet1

Cell Formulas
Range	Formula
B2	B2	=FORMULATEXT(B3)
B3:C285	B3	=cc
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
cc	=Sheet1!$G$3:$H$285	B3

 

[Xlambda]

- lambda "formula" to solve it using MAP and XMATCH match mode argument: wildcard character match

Book1
	I	J	K	L	M	N	O	P	Q	R	S	T
1
2		sample phone nr.		=MAP(SUBSTITUTE(J3:J21,"+",""),LAMBDA(x,XLOOKUP(1,XMATCH(TAKE(cc,,1)&"*",x,2),DROP(cc,,1))))
3		+447594361587		United Kingdom
4		+41769482539		Switzerland
5		+35056369249		Gibraltar
6		+972536992461		Israel
7		+12017961582		United States
8		447594361587		United Kingdom
9		41769482539		Switzerland
10		35056369249		Gibraltar
11		972536992461		Israel
12		12017961582		United States
13		33143122222		France
14		14166961000		Canada
15		13305551212		United States
16		2344603600		Nigeria
17		441624680680		Isle of Man
18		41416323030		Switzerland
19		5342350300		Cuba
20		+35056369249		Gibraltar
21		5342350300		Cuba
22
Sheet1

Cell Formulas
Range	Formula
L2	L2	=FORMULATEXT(L3)
L3:L21	L3	=MAP(SUBSTITUTE(J3:J21,"+",""),LAMBDA(x,XLOOKUP(1,XMATCH(TAKE(cc,,1)&"*",x,2),DROP(cc,,1))))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
cc	=Sheet1!$G$3:$H$285	L3

 

[bines53]

Hi ,

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.

 

[bines53]

Hi Xlambda,
Regarding to running count,If we start in reverse order,
Is it possible to do with function scan or another way?And also is possible with several columns?

Thanks

=IF(G9=G10,H9+1,1) = IF(G10=G11,H10+1,1) We will get the result,1;1;1;1;2;1;2;1,

 

[bines53]

Hi Xlambda,
Regarding to running count,If we start in reverse order,
Is it possible to do with function scan or another way?And also is possible with several columns?

Thanks

=IF(G9=G10,H9+1,1) = IF(G10=G11,H10+1,1) We will get the result,1;1;1;1;2;1;2;1,

Fix,

=IF(G9=G10,H10+1,1) = IF(G10=G11,H11+1,1) We will get the result,1;1;1;1;2;1;2;1,

 

[Xlambda]

Hi @bines53,
In general, with lambda functions anything is possible if you have a "healthy" consistent algorithm concept. At that time, I did some lambda helper functions to cover all scenarios.
A function to identify the "mod" and "quotient" pattern of consecutive values in any array (vectors, 2D and 2D byrow) and in any relationship, not only "=". For example, how many consecutive times sales registered a growth bigger than 10% of the previous. Very useful type of reports, difficult to get with other tools.
Having the mod and quotient distrib. another function can extract these distributions in an array form, very easy to filter and interpret.
Mike agreed to make a video on the matter but then we decided that it was too niched for his channel, even if the functions are quite simple.
If you do care to get into them, the reasoning, and concepts behind them, I will be happy to prepare a presentation for you and anybody else interested. Trust me, the simplicity of it is striking. No quantum anything.

 

[bines53]

Hi Xlambda,

I found this,

https://www.linkedin.com/pulse/reverse-scan-excel-ryan-hubscher,

Very impressive!

If you can help with consecutive values,I would be very happy!

 

[Xlambda]

Link does not work, saying page not found...What is about? Other reference? Or explain me in simple terms what does. ✌

 

[bines53]

I think now the link works.

https://www.linkedin.com/pulse/reverse-scan-excel

If the link doesn't work, I did a Google search, I did a search "reverse scan excel" and I arrived.