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

Well-known Member
Joined
Mar 8, 2021
Messages
832
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
Second draft, shorter. The concept of SCAN by row or by col is extremely simple. We SCAN the whole array with whatever operation and then we amend the result with the values that keep first row or first column intact.
Excel Formula:
=LAMBDA(a,[d],
    LET(y,IF(d=1,TRANSPOSE(a),a),s,SCAN(0,y,LAMBDA(v,a,v+a)),
       x,s-IF(d,INDEX(s,,1)-INDEX(y,,1)),
       IF(d=1,TRANSPOSE(x),x)
    )
)
LAMBDA 1.1.3.xlsx
ABCDEFGHIJKLMNOPQRST
1same outcome as befored,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
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.
 
Third draft. More versatility for real life scenarios, can handle blanks, null strings, text, errors, d argument wrong input.
Excel Formula:
=LAMBDA(a,[d],
    LET(n,ISNUMBER(a),r,IF(n,a,0),o,IF(d,d^0*SIGN(d),0),y,IF(o=1,TRANSPOSE(r),r),
      s,SCAN(0,y,LAMBDA(v,a,v+a)),x,s-IF(o,INDEX(s,,1)-INDEX(y,,1)),
      IF(n,IF(o=1,TRANSPOSE(x),x),a)
    )
)
LAMBDA 1.1.3.xlsx
ABCDEFGHIJKLMNOPQRST
1d,omitted (by array)d,-1 (by rows)d,1 (by clms)
2sample=ASCAN(A3:D7)=ASCAN(A3:D7,-1)=ASCAN(A3:D7,1)
31a341a481a481a34
45678131926345111826661012
591011#N/A435364#N/A91930#N/A151621#N/A
613 15167792108132844283628
7171820125143016317350554534048
8
9Note: Blanks will be returned 0's because a blnk is a 0 and a null string at the same time.
10null string B6=""Null strings will remain intact as any other text value.
11blankThe functions works only with the numeric layer, everything else returned as in the initial array
12text
13errorwhen d out of range, d <> {0,-1,1} , if d<0,d=-1, if d>0, d=1
14d,-3 <=> d=-1d,4 <=> d=1
15=ASCAN(A3:D7,-3)=ASCAN(A3:D7,4)
161a481a34
175111826661012
1891930#N/A151621#N/A
19132844283628
2017350554534048
21
ASCAN post 3
Cell Formulas
RangeFormula
F2,K15,F15,P2,K2F2=FORMULATEXT(F3)
F3:I7F3=ASCAN(A3:D7)
K3:N7K3=ASCAN(A3:D7,-1)
P3:S7P3=ASCAN(A3:D7,1)
D5D5=NA()
B6B6=""
F16:I20F16=ASCAN(A3:D7,-3)
K16:N20K16=ASCAN(A3:D7,4)
Dynamic array formulas.
 
Following first concept (we use only SCAN for the whole array, and we amend the result to keep the first row or first clm of initial array intact) , this is how it looks for appending by row.
The trick using this concept in general is finding the proper "amending" formula.
Since I do not see any practicability in real life, did not allocate a dedicated lambda function.
Excel Formula:
=LAMBDA(a,LET(s,SCAN("",A3:D5,LAMBDA(v,a,v&a)),RIGHT(s,LEN(s)-LEN(INDEX(s,,1))+LEN(INDEX(a,,1)))))
LAMBDA 1.1.3.xlsx
ABCDEFGHIJKLMNOPQ
1
2samplea=LAMBDA(a,LET(s,SCAN("",A3:D5,LAMBDA(v,a,v&a)),RIGHT(s,LEN(s)-LEN(INDEX(s,,1))+LEN(INDEX(a,,1)))))(A3:D5)
3abcdaababcabcd
4x234xx2x23x234
5rt678967rtrt67rt6789rt678967
6
ASCAN post 4
Cell Formulas
RangeFormula
F2F2=FORMULATEXT(F3)
F3:I5F3=LAMBDA(a,LET(s,SCAN("",A3:D5,LAMBDA(v,a,v&a)),RIGHT(s,LEN(s)-LEN(INDEX(s,,1))+LEN(INDEX(a,,1)))))(A3:D5)
Dynamic array formulas.
 
If I said no lambda, then should be a formula, a simple let:
=LET(a,A3:D5,s,SCAN("",a,LAMBDA(v,a,v&a)),RIGHT(s,LEN(s)-LEN(INDEX(s,,1))+LEN(INDEX(a,,1))))
LAMBDA 1.1.3.xlsx
ABCDEFGHIJKLMNO
1
2samplea=LET(a,A3:D5,s,SCAN("",a,LAMBDA(v,a,v&a)),RIGHT(s,LEN(s)-LEN(INDEX(s,,1))+LEN(INDEX(a,,1))))
3abcdaababcabcd
4x234xx2x23x234
5rt678967rtrt67rt6789rt678967
6
ASCAN post 4
Cell Formulas
RangeFormula
F2F2=FORMULATEXT(F3)
F3:I5F3=LET(a,A3:D5,s,SCAN("",a,LAMBDA(v,a,v&a)),RIGHT(s,LEN(s)-LEN(INDEX(s,,1))+LEN(INDEX(a,,1))))
Dynamic array formulas.
 
Second concept. Does not require a lambda, is a simple formula that we can write anywhere according to context.
Uses SCAN embedded in MAKEARRAY (similar concept in AMORTIZE)
LAMBDA 1.1.3.xlsx
ABCDEFGHIJKLMN
1adding by row
2sample array 5x4=MAKEARRAY(5,4,LAMBDA(r,c,LET(x,INDEX(A3#,r,),s,SCAN(0,x,LAMBDA(v,a,v+a)),INDEX(s,1,c))))
3123413610
456785111826
591011129193042
61314151613274258
71718192017355474
8
9appending by row
10=MAKEARRAY(5,4,LAMBDA(r,c,LET(x,INDEX(A3#,r,),s,SCAN("",x,LAMBDA(v,a,v&a)),INDEX(s,1,c))))
111121231234
125565675678
139910910119101112
1413131413141513141516
1517171817181917181920
16
17multiplication by row
18=MAKEARRAY(5,4,LAMBDA(r,c,LET(x,INDEX(A3#,r,),s,SCAN(1,x,LAMBDA(v,a,a*v)),INDEX(s,1,c))))
1912624
205302101680
2199099011880
2213182273043680
23173065814116280
24
25division by row
26=MAKEARRAY(5,4,LAMBDA(r,c,LET(x,INDEX(A3#,r,),s,SCAN(1,x,LAMBDA(v,a,a/v)),INDEX(s,1,c))))
27121.52.666667
2851.25.8333331.371429
2991.1111119.91.212121
30131.07692313.928571.148718
31171.05882417.944441.114551
32
ASCAN post 5
Cell Formulas
RangeFormula
F2,F26,F18,F10F2=FORMULATEXT(F3)
A3:D7A3=SEQUENCE(5,4)
F3:I7F3=MAKEARRAY(5,4,LAMBDA(r,c,LET(x,INDEX(A3#,r,),s,SCAN(0,x,LAMBDA(v,a,v+a)),INDEX(s,1,c))))
F11:I15F11=MAKEARRAY(5,4,LAMBDA(r,c,LET(x,INDEX(A3#,r,),s,SCAN("",x,LAMBDA(v,a,v&a)),INDEX(s,1,c))))
F19:I23F19=MAKEARRAY(5,4,LAMBDA(r,c,LET(x,INDEX(A3#,r,),s,SCAN(1,x,LAMBDA(v,a,a*v)),INDEX(s,1,c))))
F27:I31F27=MAKEARRAY(5,4,LAMBDA(r,c,LET(x,INDEX(A3#,r,),s,SCAN(1,x,LAMBDA(v,a,a/v)),INDEX(s,1,c))))
Dynamic array formulas.
 
Humble Conclusion!!
I think that these new lambda helper functions are so intrinsic versatile and powerful that their main purpose is not to help us in lambda constructions, quite the opposite, they were created to help us in lambdas deconstruction.
I know, first instinct is, wow, I can write a lambda doing this. If we'll write a lambda for every construction that these new functions are capable of, soon, our name manager will be oversaturated with a tone of lambdas, variables, fancy arguments, doing nothing, never used in real life, never embraced by other users, doing stuff that a simple short formula can do. It's so unproductive as gluing lego pieces. And these exercises here are a proof of how simple is to omit a lambda. Even if these examples have no use in real life, they help us understand how simple and versatile these constructions can be. We are witnessing a paradigm shift. My 2 cents. Sorry for the long talk. To end on a funny note, ASCAN will survive, running totals are practical. ?✌
LAMBDA 1.1.3.xlsx
ABCDEFGHIJKLMN
1adding by col
2sample array 5x4=MAKEARRAY(5,4,LAMBDA(r,c,LET(x,INDEX(A3#,,c),s,SCAN(0,x,LAMBDA(v,a,v+a)),INDEX(s,r,1))))
312341234
45678681012
5910111215182124
61314151628323640
71718192045505560
8
9appending by col
10=MAKEARRAY(5,4,LAMBDA(r,c,LET(x,INDEX(A3#,,c),s,SCAN("",x,LAMBDA(v,a,v&a)),INDEX(s,r,1))))
111234
1215263748
13159261037114812
1415913261014371115481216
151591317261014183711151948121620
16
17multiplication by col
18=MAKEARRAY(5,4,LAMBDA(r,c,LET(x,INDEX(A3#,,c),s,SCAN(1,x,LAMBDA(v,a,v*a)),INDEX(s,r,1))))
191234
205122132
2145120231384
22585168034656144
2399453024065835122880
24
25division by col
26=MAKEARRAY(5,4,LAMBDA(r,c,LET(x,INDEX(A3#,,c),s,SCAN(1,x,LAMBDA(v,a,a/v)),INDEX(s,r,1))))
271234
28532.33333332
291.83.33333334.71428576
307.22222224.23.18181822.6666667
312.35384624.28571435.97142867.5
32
ASCAN post 6
Cell Formulas
RangeFormula
F2,F26,F18,F10F2=FORMULATEXT(F3)
A3:D7A3=SEQUENCE(5,4)
F3:I7F3=MAKEARRAY(5,4,LAMBDA(r,c,LET(x,INDEX(A3#,,c),s,SCAN(0,x,LAMBDA(v,a,v+a)),INDEX(s,r,1))))
F11:I15F11=MAKEARRAY(5,4,LAMBDA(r,c,LET(x,INDEX(A3#,,c),s,SCAN("",x,LAMBDA(v,a,v&a)),INDEX(s,r,1))))
F19:I23F19=MAKEARRAY(5,4,LAMBDA(r,c,LET(x,INDEX(A3#,,c),s,SCAN(1,x,LAMBDA(v,a,v*a)),INDEX(s,r,1))))
F27:I31F27=MAKEARRAY(5,4,LAMBDA(r,c,LET(x,INDEX(A3#,,c),s,SCAN(1,x,LAMBDA(v,a,a/v)),INDEX(s,r,1))))
Dynamic array formulas.
 
What if we want to apply various functions to each row of an array, (UNIQUE or FILTER or SORT), instead of only simple operations?
(Exactly what AFUSBYROW does without the lambda helper functions)
It's quite easy, using a trick. Since in this case, SCAN cannot return "row" arrays, as we need, we can TEXTJOIN the result of each row, in a single value, and split them afterwards.
Another argument in favor of how a simple and short lambda "formula" can replace the functionality of custom-made dedicated functions.
LAMBDA 1.1.4.xlsx
ABCDEFGHIJKLMNOPQRSTUVW
11. Extracting UNIQUE values of each row of an array
2
3sampleadother functions
44333114AFUSBYROW
52112775ATEXTSPLIT
68665589
7
8the result we are looking for:
9=AFUSBYROW(ad,,1)=SCAN("",SEQUENCE(3),LAMBDA(v,i,TEXTJOIN(",",,UNIQUE(INDEX(ad,i,),1))))
104314,3,1
1121752,1,7,5next step, splitting
1286598,6,5,9=ATEXTSPLIT(H10#)
13431
142175
158659
16as we see, we get the same result
172. SORTing in ascending order, each row
18
19the result we are looking for:lambda formula
20=AFUSBYROW(ad,,,1)=SCAN("",SEQUENCE(3),LAMBDA(v,i,TEXTJOIN(",",,SORT(INDEX(ad,i,),,,1))))
2111333441,1,3,3,3,4,4
2211225771,1,2,2,5,7,7next step, splitting
2355668895,5,6,6,8,8,9=ATEXTSPLIT(I21#)
241133344
251122577
265566889
27as we see, we get the same result
283. SORTing in descending order, each row
29
30the result we are looking for:lambda formula
31=AFUSBYROW(ad,,,-1)=SCAN("",SEQUENCE(3),LAMBDA(v,i,TEXTJOIN(",",,SORT(INDEX(ad,i,),,-1,1))))
3244333114,4,3,3,3,1,1
3377522117,7,5,2,2,1,1next step, splitting
3498866559,8,8,6,6,5,5=ATEXTSPLIT(I32#)
354433311
367752211
379886655
38as we see, we get the same result
39
ASCAN post 7
Cell Formulas
RangeFormula
A9,K34,I31,A31,K23,I20,A20,K12,H9A9=FORMULATEXT(A10)
A10:D12A10=AFUSBYROW(ad,,1)
H10:H12H10=SCAN("",SEQUENCE(3),LAMBDA(v,i,TEXTJOIN(",",,UNIQUE(INDEX(ad,i,),1))))
K13:N15K13=ATEXTSPLIT(H10#)
A21:G23A21=AFUSBYROW(ad,,,1)
I21:I23I21=SCAN("",SEQUENCE(3),LAMBDA(v,i,TEXTJOIN(",",,SORT(INDEX(ad,i,),,,1))))
K24:Q26,K35:Q37K24=ATEXTSPLIT(I21#)
A32:G34A32=AFUSBYROW(ad,,,-1)
I32:I34I32=SCAN("",SEQUENCE(3),LAMBDA(v,i,TEXTJOIN(",",,SORT(INDEX(ad,i,),,-1,1))))
Dynamic array formulas.
Named Ranges
NameRefers ToCells
ad='ASCAN post 7'!$A$4:$G$6I32, I21, H10, A32, A21, A10
 
SCAN cannot return "row" arrays, as we need, we can TEXTJOIN
A little more readable using BYROW and ARRAYTOTEXT:
LAMBDA_SCANBY.xlsx
ABCDEFGHIJKLMN
14333114
22112775
38665589
4
54, 3, 1=BYROW(A1:G3, LAMBDA(row_, ARRAYTOTEXT(UNIQUE(TRANSPOSE(row_))) ))
62, 1, 7, 5
78, 6, 5, 9
8
9
101, 1, 3, 3, 3, 4, 4=BYROW(A1:G3, LAMBDA(row_, ARRAYTOTEXT(SORT(TRANSPOSE(row_))) ))
111, 1, 2, 2, 5, 7, 7
125, 5, 6, 6, 8, 8, 9
13
14
154, 4, 3, 3, 3, 1, 1=BYROW(A1:G3, LAMBDA(row_, ARRAYTOTEXT(SORT(TRANSPOSE(row_),,-1)) ))
167, 7, 5, 2, 2, 1, 1
179, 8, 8, 6, 6, 5, 5
18
Sheet1
Cell Formulas
RangeFormula
I5:I7I5=BYROW(A1:G3, LAMBDA(row_, ARRAYTOTEXT(UNIQUE(TRANSPOSE(row_))) ))
J5,J15,J10J5=FORMULATEXT(I5)
I10:I12I10=BYROW(A1:G3, LAMBDA(row_, ARRAYTOTEXT(SORT(TRANSPOSE(row_))) ))
I15:I17I15=BYROW(A1:G3, LAMBDA(row_, ARRAYTOTEXT(SORT(TRANSPOSE(row_),,-1)) ))
Dynamic array formulas.
 
Super cool!! ✌ There are so many ways. BYROW still cannot "spill" rows. And I have used TEXTJOIN because of its "ignore empty argument". It's good for handling blanks/null strings. Examples will follow.
 

Forum statistics

Threads
1,213,558
Messages
6,114,297
Members
448,564
Latest member
ED38

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