ASCAN

ASCAN(a,[d])
a
array
[d]
direction argument ; 0 or omitted scan by array, -1 by rows, 1 by clms
Array SCAN, 3 in 1 function, SCAN by row, by column, by array
Xlambda

Xlambda

Well-known Member
Joined
Mar 8, 2021
Messages
837
Office Version
  1. 365
Platform
  1. Windows
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.
Excel Formula: 
=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
ABCDEFGHIJKLMNOPQRST
1d,omitted (by array)d,-1 (by rows)d,1 (by clms)
2sample 1a=ASCAN(A3:D6)=ASCAN(A3:D6,-1)=ASCAN(A3:D6,1)
3123413610136101234
45678152128365111826681012
5910111245556678919304215182124
613141516911051201361327425828323640
7
8d,omitted (by array)d,-1 (by rows)d,1 (by clms)
9sample 2a=ASCAN(A10#)=ASCAN(A10#,-1)=ASCAN(A10#,1)
10123413610136101234
115678152128365111826681012
12910111245556678919304215182124
1313141516911051201361327425828323640
14171819201531711902101735547445505560
15212223242312532763002143669066727884
16
17Note: Did not set an extra argument for any kind of "initial value", since the only thing it does is too simple,
18adds a constant value to the final outcome , like in 10+ASCAN(a)
19
ASCAN post
Cell Formulas
RangeFormula
F2,P9,K9,F9,P2,K2F2=FORMULATEXT(F3)
F3:I6F3=ASCAN(A3:D6)
K3:N6K3=ASCAN(A3:D6,-1)
P3:S6P3=ASCAN(A3:D6,1)
A10:D15A10=SEQUENCE(6,4)
F10:I15F10=ASCAN(A10#)
K10:N15K10=ASCAN(A10#,-1)
P10:S15P10=ASCAN(A10#,1)
Dynamic array formulas.
 
Upvote 0
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))
 
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
ABCD
1Country Calling Codes:
2=cc
3441624Isle of Man
4441534Jersey
5441481Guernsey
61939Puerto Rico
71905Canada
81902Canada
91876Jamaica
101873Canada
111869Saint Kitts and Nevis
121868Trinidad and Tobago
131867Canada
141849Dominican Republic
151829Dominican Republic
161825Canada
171819Canada
181809Dominican Republic
191807Canada
201787Puerto Rico
211784Saint Vincent and the Grenadines
221782Canada
231780Canada
241778Canada
251767Dominica
261758Saint Lucia
271753Canada
281742Canada
291721Sint Maarten
301709Canada
311705Canada
321684American Samoa
331683Canada
341672Canada
351671Guam
361670Northern Mariana Islands
371664Montserrat
381649Turks and Caicos Islands
391647Canada
401639Canada
411613Canada
421604Canada
431587Canada
441584Canada
451581Canada
461579Canada
471548Canada
481519Canada
491514Canada
501506Canada
511474Canada
521473Grenada
531468Canada
541450Canada
551441Bermuda
561438Canada
571437Canada
581431Canada
591418Canada
601416Canada
611403Canada
621368Canada
631367Canada
641365Canada
651354Canada
661345Cayman Islands
671343Canada
681340U.S. Virgin Islands
691306Canada
701289Canada
711284British Virgin Islands
721268Antigua and Barbuda
731264Anguilla
741263Canada
751250Canada
761249Canada
771246Barbados
781242Bahamas
791236Canada
801226Canada
811204Canada
82998Uzbekistan
83996Kyrgyzstan
84995Georgia
85994Azerbaijan
86993Turkmenistan
87992Tajikistan
88977Nepal
89976Mongolia
90975Bhutan
91974Qatar
92973Bahrain
93972Israel
94971United Arab Emirates
95970Palestine
96968Oman
97967Yemen
98966Saudi Arabia
99965Kuwait
100964Iraq
101963Syria
102962Jordan
103961Lebanon
104960Maldives
105886Taiwan
106880Bangladesh
107856Laos
108855Cambodia
109853Macau
110852Hong Kong
111850North Korea
112692Marshall Islands
113691Micronesia
114690Tokelau
115689French Polynesia
116688Tuvalu
117687New Caledonia
118686Kiribati
119685Samoa
120683Niue
121682Cook Islands
122681Wallis and Futuna
123680Palau
124679Fiji
125678Vanuatu
126677Solomon Islands
127676Tonga
128675Papua New Guinea
129674Nauru
130673Brunei
131672Antarctica
132670East Timor
133599Curacao or Netherlands Antilles
134598Uruguay
135597Suriname
136595Paraguay
137593Ecuador
138592Guyana
139591Bolivia
140590Saint Martin or Saint Bart
141509Haiti
142508Saint Pierre and Miquelon
143507Panama
144506Costa Rica
145505Nicaragua
146504Honduras
147503El Salvador
148502Guatemala
149501Belize
150500Falkland Islands
151423Liechtenstein
152421Slovakia
153420Czech Republic
154389Macedonia
155387Bosnia and Herzegovina
156386Slovenia
157385Croatia
158383Kosovo
159382Montenegro
160381Serbia
161380Ukraine
162379Vatican
163378San Marino
164377Monaco
165376Andorra
166375Belarus
167374Armenia
168373Moldova
169372Estonia
170371Latvia
171370Lithuania
172359Bulgaria
173358Finland
174357Cyprus
175356Malta
176355Albania
177354Iceland
178353Ireland
179352Luxembourg
180351Portugal
181350Gibraltar
182299Greenland
183298Faroe Islands
184297Aruba
185291Eritrea
186290Saint Helena
187269Comoros
188268Swaziland
189267Botswana
190266Lesotho
191265Malawi
192264Namibia
193263Zimbabwe
194262Mayotte or Reunion
195261Madagascar
196260Zambia
197258Mozambique
198257Burundi
199256Uganda
200255Tanzania
201254Kenya
202253Djibouti
203252Somalia
204251Ethiopia
205250Rwanda
206249Sudan
207248Seychelles
208246British Indian Ocean Territory
209245Guinea-Bissau
210244Angola
211243Democratic Republic of the Congo
212242Republic of the Congo
213241Gabon
214240Equatorial Guinea
215239Sao Tome and Principe
216238Cape Verde
217237Cameroon
218236Central African Republic
219235Chad
220234Nigeria
221233Ghana
222232Sierra Leone
223231Liberia
224230Mauritius
225229Benin
226228Togo
227227Niger
228226Burkina Faso
229225Ivory Coast
230224Guinea
231223Mali
232222Mauritania
233221Senegal
234220Gambia
235218Libya
236216Tunisia
237213Algeria
238212Morocco or Western Sahara
239211South Sudan
24098Iran
24195Myanmar
24294Sri Lanka
24393Afghanistan
24492Pakistan
24591India
24690Turkey
24786China
24884Vietnam
24982South Korea
25081Japan
25166Thailand
25265Singapore
25364New Zealand, Pitcairn
25463Philippines
25562Indonesia
25661Australia, Christmas Island, Cocos Islands
25760Malaysia
25858Venezuela
25957Colombia
26056Chile
26155Brazil
26254Argentina
26353Cuba
26452Mexico
26551Peru
26649Germany
26748Poland
26847Norway, Svalbard and Jan Mayen
26946Sweden
27045Denmark
27144United Kingdom
27243Austria
27341Switzerland
27440Romania
27539Italy
27636Hungary
27734Spain
27833France
27932Belgium
28031Netherlands
28130Greece
28227South Africa
28320Egypt
2847Russia or Kazakhstan
2851United States
286
Sheet1
Cell Formulas
RangeFormula
B2B2=FORMULATEXT(B3)
B3:C285B3=cc
Dynamic array formulas.
Named Ranges
NameRefers ToCells
cc=Sheet1!$G$3:$H$285B3
 
- lambda "formula" to solve it using MAP and XMATCH match mode argument: wildcard character match
Book1
IJKLMNOPQRST
1
2sample phone nr.=MAP(SUBSTITUTE(J3:J21,"+",""),LAMBDA(x,XLOOKUP(1,XMATCH(TAKE(cc,,1)&"*",x,2),DROP(cc,,1))))
3+447594361587United Kingdom
4+41769482539Switzerland
5+35056369249Gibraltar
6+972536992461Israel
7+12017961582United States
8447594361587United Kingdom
941769482539Switzerland
1035056369249Gibraltar
11972536992461Israel
1212017961582United States
1333143122222France
1414166961000Canada
1513305551212United States
162344603600Nigeria
17441624680680Isle of Man
1841416323030Switzerland
195342350300Cuba
20+35056369249Gibraltar
215342350300Cuba
22
Sheet1
Cell Formulas
RangeFormula
L2L2=FORMULATEXT(L3)
L3:L21L3=MAP(SUBSTITUTE(J3:J21,"+",""),LAMBDA(x,XLOOKUP(1,XMATCH(TAKE(cc,,1)&"*",x,2),DROP(cc,,1))))
Dynamic array formulas.
Named Ranges
NameRefers ToCells
cc=Sheet1!$G$3:$H$285L3
 
Hi ,
Xlambda said:
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
FGHIJKLMNOP
9
1021=IF(G10=G9,H9+1,1)
1111=IF(G11=G10,H10+1,1)
1231=IF(G12=G11,H11+1,1)
1311=IF(G13=G12,H12+1,1)
1431=IF(G14=G13,H13+1,1)
1532=IF(G15=G14,H14+1,1)
1621=IF(G16=G15,H15+1,1)
1722=IF(G17=G16,H16+1,1)
18
19single 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)))))
211
221
231
241
251
262
271
282
29
ASCAN 1
Cell Formulas
RangeFormula
H10:H17H10=IF(G10=G9,H9+1,1)
I10:I17I10=FORMULATEXT(H10)
H20H20=FORMULATEXT(H21)
H21:H28H21=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.
Click to expand...
 
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,
 
Last edited:
bines53 said:
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,
Click to expand...
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,

Click to expand...
 
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. 😉
 
Link does not work, saying page not found...What is about? Other reference? Or explain me in simple terms what does. ✌
 
You must log in or register to reply here.

Forum statistics

Threads
1,216,505
Messages
6,131,026
Members
449,616
Latest member
PsychoCube

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top