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
Hi again,
We do not need to design any function in reverse like reverse scan or any other function in reverse. We can apply to any function the principle I called before the double transpose principle.
The principle is very simple, If we have a function fbr(a) that does something to an array by rows, if you call =TRANSPOSE(fbr(TRANSPOSE(a))) will calculate same things but this time by columns.
Or in other words bycol(a)=transpose(byrow(transpose(a)))
For reversing or flipping (have this function by the way AFLIP ) we need more versatility than a transpose because there are 3 ways of doing it: by row, by col and by array. These can produce 9 different outcomes, because we can flip the input in a way and the output in a diffrent way or not at all. It is common to do running totals of a flipped column and not to flip back the results. So a good flip function that covers all scenarios gives you the flexibility to choose whatever satisfies your needs without defining a reverse function for a single operation that does a double flip in one way only. By manipulating the input and the output arrays as you wish is more versatile. =AFLIP(fn(AFLIP(a,k)),k)
Makes sense?
Do not worry, buckle up and we will go together on the consecutive distributions journey.
PS I will do it as fast as I can considering my spare time. Being lucky today to be able to post from work.✌😉
 
Last edited:
Hi Xlambda,

Do you have an idea, or a solution, how to work with two ranges with the byrow function, inside the countifs function?

Thanks,
David
 
Hi David,

Can you be more specific please? Examples? Need a structure, to get from this to that example and will be doable and without ...IFS functions. ✌.
In general, I do not use COUNTIFS or SUMIFS function or any other ...IFS function because of their limited versatility to work with ranges only and not with array calculations. This will limit the eventuality of modular lambda "programming", using functions as routines for other functions.
Excel could have changed them extremely easily to allow array arguments, but they chose not to for safety reasons. They are powerful basic tools for financial or any other reports and should be kept in open sight on the real estate of the spreadsheets as ranges, easily checkable by a supervisor. There are enough famous Excel tiny errors that caused disasters in real life. 😉
PS Still need few good hours to finish the consecutive distribution presentation I've promised. I have joined it with a similar related study about manipulating pattern distributions, very useful for other scenarios stuff, not only consecutive.
 
Hi Xlambda,

I will give two examples about the same two ranges of data, in the ranges there are numbers between 0 and 3 .A1:D23 ,and F1:I23.The ranges has similar values.

first function,
=COUNTIFS(A1:D1,SEQUENCE(,4,0,1),F1:I1,SEQUENCE(,4,0,1)),
I want to sum each line, in one formula.

Second function

=SUM(BYCOL(SEQUENCE(,4,0),LAMBDA(x,MIN(COUNTIF(A1:D1,x),COUNTIF(F1:I1,x)))))
OR
=SUM(BYCOL(UNIQUE(A1:D1,1),LAMBDA(x,MIN(COUNTIF(A1:D1,x),COUNTIF(F1:I1,x)))))

The formula sum, how many common values are there in both ranges, I want to sum each line, in one formula.
I hope my explanation is clear.

Thanks,
David
 
It will be a major help to post proper spreadsheet example using the mini sheet interface.
It is not that I cannot figure it out eventually, but it saves time that I do not have especially when I'm at work. Help me help you.😉
Also, we have an "audience" here that deserves and would be nice for them to understand what we are doing. 😉✌
Like this, for first function
Your formula is
=BYROW(MAP(A1#,F1#,LAMBDA(x,y,SUM(N(x=y)))),LAMBDA(x,SUM(x)))
You do know how to use the mini-sheet interface? Is very well explained and easy at the top of the forum.
Use it for the second function example.🙏
Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
111221333010011
231200023001011
312331101010011
420121030100011checking outcome
520010233000000manual countifs vs sigle cell formula
630320200000000=AND(P1:P23=R1#)
710020032101022TRUE
833101132000000
921300312000000 => same outcome every time
1020301200100011
1100231022101022
1230101233000000
1321313302000000
1430321022101022
1520310313000000
1621113032000000
1722022302102033
1831210332000000
1902033333000111
2022033113000111
2123033222000000
2212230030000000
2330010210000000
24=RANDARRAY(23,4,0,3,1)=RANDARRAY(23,4,0,3,1)K1:K23=BYROW(MAP(A1#,F1#,LAMBDA(x,y,SUM(N(x=y)))),LAMBDA(x,SUM(x)))
25=COUNTIFS(A1:D1,SEQUENCE(,4,0,1),F1:I1,SEQUENCE(,4,0,1))
26
27P1:P23
28=SUM(K1:N1)
29
Sheet1
Cell Formulas
RangeFormula
A1:D23,F1:I23A1=RANDARRAY(23,4,0,3,1)
R1:R23R1=BYROW(MAP(A1#,F1#,LAMBDA(x,y,SUM(N(x=y)))),LAMBDA(x,SUM(x)))
U6U6=FORMULATEXT(U7)
U7U7=AND(P1:P23=R1#)
K1:N23K1=COUNTIFS(A1:D1,SEQUENCE(,4,0,1),F1:I1,SEQUENCE(,4,0,1))
P1:P23P1=SUM(K1:N1)
A24,F24,R24A24=FORMULATEXT(A1)
K25K25=FORMULATEXT(K1)
P28P28=FORMULATEXT(P1)
Dynamic array formulas.
 
Hi Xlambda,

The first function works perfectly!
I'm still in diapers, in complex functions with excel 365.👍
I will try to understand how to use the mini-sheet interface.

Thanks,
David
 
Last edited:
Hi Xlambda,

Recently, I don't have a computer, I work with my mobile phone and an Excel application, it is not possible to use the mini-sheet interface.
Is it possible to upload an Excel file?

Thanks,
David
 
Hi Xlambda,

In the second function with map,

First line
=SUM(MAP(COUNTIF(A1:D1,SEQUENCE(,4,0)),COUNTIF(F1:I1,SEQUENCE(,4,0)),LAMBDA(x,y,MIN(x,y))))

Second line
=SUM(MAP(COUNTIF(A2:D2,SEQUENCE(,4,0)),COUNTIF(F2:I2,SEQUENCE(,4,0)),LAMBDA(x,y,MIN(x,y))))
........
last line
=SUM(MAP(COUNTIF(A23:D23,SEQUENCE(,4,0)),COUNTIF(F23:I23,SEQUENCE(,4,0)),LAMBDA(x,y,MIN(x,y))))

Thanks,
David
 
Hi Xlambda,

My solution, not desirable, with map, concat, mid,
=MMULT(MAP(MID(BYROW(A1:D23,LAMBDA(x,CONCAT(COUNTIF(x,SEQUENCE(,4,0,))))),{1,2,3,4},1)*1,MID(BYROW(F1:I23,LAMBDA(y,CONCAT(COUNTIF(y,SEQUENCE(,4,0,))))),{1,2,3,4},1)*1,LAMBDA(x,y,MIN(x,y))),{1;1;1;1})

Thanks,
David
 
You must log in or register to reply here.

Forum statistics

Threads
1,216,499
Messages
6,131,012
Members
449,613
Latest member
MedDash99

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