STREAK

=STREAK(a,v,o,x)

a
array
v
value
[o]
orientation: if omitted or 0, byrow (clm vector result), if 1 , bycol (row vector result)
[x]
max argument: if omitted => entire vector, if 1 max(vector)

Largest consecutive distribution of a given value, BYROW or BYCOL

Xlambda

Well-known Member
Joined
Mar 8, 2021
Messages
860
Office Version
  1. 365
Platform
  1. Windows
STREAK Largest consecutive distribution of a given value, byrow or bycol
This is my take on a cool function: STREAK
Added new functionality, max argument [x]
Main calculation core of the function: IF(i, v + i, 0)
Excel Formula:
=LAMBDA(a, v, [o], [x],
    LET(
        b, IF(a = v, 1, 0),
        s, LAMBDA(x, MAX(SCAN(0, x, LAMBDA(v, i, IF(i, v + i, 0))))),
        i, IF(o, BYROW(b, LAMBDA(x, s(x))), BYCOL(b, LAMBDA(x, s(x)))),
        IF(x, MAX(i), i)
    )
)
Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
1
2=CHAR(RANDARRAY(20,20,65,67,1))=STREAK(B3#,"a")
3CCBABABCBCBCABCABCBA1
4AAABBAAABCBCABABACBA3
5ACCBCBBBBABBCBABCBCC1
6BABCABABCAAABACAAABA3
7ACAAACCCCBAABCBBABCB3
8CACCABACBBBBBCCCABCB1
9CCAACBAAAABCAAABBACC4
10BAACBACAABCCBAAABCCC3
11BBABBCCABBBABABABACA1
12ABCAAACACACBCAABABCA3
13CCCBCCAAABCABCAACABA3
14BCCBAAAACABCAABBAAAB4
15BCBABBAAABCABBCAAACB3
16BBACBACABBAABACBABBB2
17BBBABABABABBCCCBBAAA3
18BBBABBACBBCCCACBCABC1
19AAABABBABCCCCABCBACC3
20AACAABABBABCBAACACAC2
21CBAABAAABACBBCAABBCB3=STREAK(B3#,"a",,1)
22BBABCAACACACCCAABAAC24
23
2422323239222224323313
25=STREAK(B3#,"a",1)
269
27=STREAK(B3#,"a",1,1)
28
Sheet1
Cell Formulas
RangeFormula
B2,Y21,W2B2=FORMULATEXT(B3)
B3:U22B3=CHAR(RANDARRAY(20,20,65,67,1))
W3:W22W3=STREAK(B3#,"a")
Y22Y22=STREAK(B3#,"a",,1)
B24:U24B24=STREAK(B3#,"a",1)
B25,U27B25=FORMULATEXT(B24)
U26U26=STREAK(B3#,"a",1,1)
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B3:U22Expression=B3="a"textNO
 
Upvote 0
I know, can be modified with ease, that's why I have mentioned above "min corespondent length". Next break will do, since I'm at work.✌️🙏
 
Revised ROWSTREAK(a,[v],[n],[lf]) Calls STREAKS
a
: array
[v]: single value or array of values, if omitted, all unique values of "ar" are considered
[n]: numeric value length/frequency, if omitted max(length)/max(frequency) triggered by "lf" argument
[lf]: length/frequency argument, if omitted=> frequencies, if 1=> lengths, if n=0 and lf=2=>lengths of max frequencies
Excel Formula:
=LAMBDA(a, [v], [n], [lf],
    LET(
        b, STREAKS(a, v),
        m, MAP(
            DROP(b, 1),
            LAMBDA(x,
                LET(
                    f, --TEXTSPLIT(x, ","),
                    y, MAX(f),
                    ml, COLUMNS(f),
                    l, SEQUENCE(, ml),
                    IF(n, IF(lf, INDEX(f, n), TEXTJOIN(",", , IF(f = n, l, ""))), IF(lf, IF(lf = 1, ml, y & ";" & TEXTJOIN(",", , IF(f = y, l, ""))), y))
                )
            )
        ),
        e, IFERROR(VSTACK(TAKE(b, 1), m), 0),
        IFERROR(--e, e)
    )
)
STREAK.xlsx
ABCDEFGHIJKLMNOPQRST
1all values, max frequenciesall values, max lengthsall values, max frequencies and their lengths
2v, omitted=>all valuesv, omitted=>all valuesv, omitted=>all values
3n, omitted,maxn, omitted,maxn, omitted,max
4lf, omitted=> frequencieslf,1=> lengthslf,2=> max frequencies; corespondent lengths
5=STREAKS(ar)=ROWSTREAK(ar)=ROWSTREAK(ar,,,1)=ROWSTREAK(ar,,,2)
6ABCABCABCABC
77,0,2,17,0,111,177114327;17;111;1
84,46,45,1,1,14652244;1,26;15;1
97,3,28,0,14,0,27843337;18;14;1
109,17,1,0,17,1,1,19772449;17;17;1
116,1,25,2,1,12,46543426;15;14;2
12
13lengths for frequencies=1frequencies for length=1
14v, omitted=>all valuesv, omitted=>all values
15n, 1n, 1
16lf, omitted=> frequencieslf,1=> lengths
17=ROWSTREAK(ar,,1)=ROWSTREAK(ar,,1,1)
18ABCABC
194327711
202,3,4465
213784
2222,42,3,4977
2323,4652
24
25lengths for frequencies=2frequencies for length=2
26v, omitted=>all valuesv, omitted=>all values
27n, 2n, 2
28lf, omitted=> frequencieslf,1=> lengths
29=ROWSTREAK(ar,,2)=ROWSTREAK(ar,,2,1)
30ABCABC
313001
32441
3333300
34111
35321124
36
37lengths for frequencies=4frequencies for length=4
38v, omitted=>all valuesv, omitted=>all values
39n, 4n, 4
40lf, omitted=> frequencieslf,1=> lengths
41=ROWSTREAK(ar,,4)=ROWSTREAK(ar,,4,1)
42ABCABC
43100
441,22001
451000
46011
472010
48
ROWSTREAK 1
Cell Formulas
RangeFormula
B5,K41,F41,K29,F29,K17,F17,P5,K5,F5B5=FORMULATEXT(B6)
B6:D11B6=STREAKS(ar)
F6:H11F6=ROWSTREAK(ar)
K6:M11K6=ROWSTREAK(ar,,,1)
P6:R11P6=ROWSTREAK(ar,,,2)
F18:H23F18=ROWSTREAK(ar,,1)
K18:M23K18=ROWSTREAK(ar,,1,1)
F30:H35F30=ROWSTREAK(ar,,2)
K30:M35K30=ROWSTREAK(ar,,2,1)
F42:H47F42=ROWSTREAK(ar,,4)
K42:M47K42=ROWSTREAK(ar,,4,1)
Dynamic array formulas.
Named Ranges
NameRefers ToCells
ar=ROWSTREAK!$B$4:$AO$8B6, F6, F18, F30, F42, K6, K18, K30, K42, P6
 
Amazing! How you think of these challenges, its quite amazing!

first IF statement should be, me thinks... but totally cool function!

Excel Formula:
    IF(
          lf,
            TEXTJOIN(",", , IF(f = n, l, "")),
            INDEX(f, n)
   ),
 
Amazing! How you think of these challenges, its quite amazing!

first IF statement should be, me thinks... but totally cool function!

Excel Formula:
    IF(
          lf,
            TEXTJOIN(",", , IF(f = n, l, "")),
            INDEX(f, n)
   ),
Long time no see😊 Back on track!!
The IF "flow" is like this (nested IF) and works fine:
IF( n, IF( lf , INDEX , TJ1 ) , IF( lf , IF( lf=1, ml, y&TJ2 ) ,y ) )
 
Thanks!

I'm started using a modified version of your excellent work here:

I can't remember what I changed, I think
- I swapped the arguments in the first IF, so if the last argument of the Lamdba was 0 or 1, it always returned a length or frequency, regardless of whether n was blank or not.
- Then I redefined ml, in case there were any '0' cases, as I was getting iffy results if a character appeared on one row, but not others.

But, as I say, its a superb function, and thanks for sharing. There so much you can do with Lamdbas, it's truly quite amazing.
I'm trying to find the hi-fi emoji, is this it? 🙏🤚


Excel Formula:
AROWSTREAK = LAMBDA(a, [v], [n], [lf],
    LET(
        b, ASTREAKS(a, v),
        m, MAP(
            DROP(b, 1),
            LAMBDA(x,
                LET(
                    f, --TEXTSPLIT(x, ","),
                    y, MAX(f),
                    ml, IF(y = 0, 0, COLUMNS(f)),
                    l, SEQUENCE(, ml),
                    IF(
                        n,
                        IF(
                            lf,
                            TEXTJOIN(",", , IF(f = n, l, "")),
                            INDEX(f, n)
                        ),
                        IF(
                            lf,
                            IF(
                                lf = 1,
                                ml,
                                y & ";" & TEXTJOIN(",", , IF(f = y, l, ""))
                            ),
                            y
                        )
                    )
                )
            )
        ),
        e, IFERROR(VSTACK(TAKE(b, 1), m), 0),
        IFERROR(--e, e)
    )
);
 
Thanks, and very good point !✌️🙏 If we have to overcome the cases when a value is not found on a row, if I leave the IF "flow" as it is, modifying only "ml" var to IF(y, COLUMNS(f), 0) will do the trick.
Maybe with this we can get out from beta version. 😉.
Regarding the complexity of lambdas, I think we are only scratching the surface. I have so many functions and concepts that are waiting to be posted. Editing the mini-sheets to a minimal standard quality is so time consuming that I do not know if I will ever catch up. The only way is to work on the go.
ROWSTREAK(a,[v],[n],[lf]) Calls STREAKS
a
: array
[v]: single value or array of values, if omitted, all unique values of "ar" are considered
[n]: numeric value length/frequency, if omitted max(length)/max(frequency) triggered by "lf" argument
[lf]: length/frequency argument, if omitted=> frequencies, if 1=> lengths, if n=0 and lf=2=>max frequencies and their lengths.
Excel Formula:
=LAMBDA(a, [v], [n], [lf],
    LET(
        b, STREAKS(a, v),
        m, MAP(
            DROP(b, 1),
            LAMBDA(x,
                LET(
                    f, --TEXTSPLIT(x, ","),
                    y, MAX(f),
                    ml, IF(y, COLUMNS(f), 0),
                    l, SEQUENCE(, ml),
                    IF(n, IF(lf, INDEX(f, n), TEXTJOIN(",", , IF(f = n, l, ""))), IF(lf, IF(lf = 1, ml, y & ";" & TEXTJOIN(",", , IF(f = y, l, ""))), y))
                )
            )
        ),
        e, IFERROR(VSTACK(TAKE(b, 1), m), 0),
        IFERROR(--e, e)
    )
)
With this sample data, every row is missing a value, so whatever arguments we are triggering, will always have 0's or ""'s diagonal.
STREAK.xlsx
ABCDEFGHIJKLMNOPQRSTU
1
2defined name "ay" = B4:AO8
3
4AAABBAABBAAABBAABAB
5CAACCAACCAACCAAAACC
6BBCCBBBBCCBBBBCBBBB
7
ROWSTREAK 2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B6:U6Expression=B6="c"textNO
B6:U6Expression=B6="b"textNO
B6:U6Expression=B6="a"textNO
B4:U5Expression=B4="c"textNO
B4:U5Expression=B4="b"textNO
B4:U5Expression=B4="a"textNO
 
STREAK.xlsx
VWXYZAAABACADAEAFAGAHAIAJAKALAMAN
1max frequencieslengths for frequencies=1frequencies for lengths=1
2=STREAKS(ay)n,lf, omittedn,1,lf,omittedn,1,lf,1
3ABC=ROWSTREAK(ay)=ROWSTREAK(ay,,1)=ROWSTREAK(ay,,1,1)
41,2,22,30ABCABCABC
50,3,0,101,42301120
600,1,0,31,230441001
703221001
8
9max lengthslengths for frequencies=2frequencies for lengths=2
10n,omitted,lf,1n,2,lf,omittedn,2,lf,1
11=ROWSTREAK(ay,,,1)=ROWSTREAK(ay,,2)=ROWSTREAK(ay,,2,1)
12ABCABCABC
133202,31230
14402304
150422012
16
17max frq; coresp. lengthslengths for frequencies=3frequencies for lengths=3
18n,omitted,lf,2n,3,lf,omittedn,3,lf,1
19=ROWSTREAK(ay,,,2)=ROWSTREAK(ay,,3)=ROWSTREAK(ay,,3,1)
20ABCABCABC
212;2,33;202200
223;204;22000
2303;42;24000
24
25lengths for frequencies=4frequencies for lengths=4
26n,4,lf,omittedn,4,lf,1
27=ROWSTREAK(ay,,4)=ROWSTREAK(ay,,4,1)
28ABCABC
29000
302100
31030
32
ROWSTREAK 2
Cell Formulas
RangeFormula
W2,AE27,AI27,AA19,AE19,AI19,AA11,AE11,AI11,AE3,AI3,AA3W2=FORMULATEXT(W3)
W3:Y6W3=STREAKS(ay)
AA4:AC7AA4=ROWSTREAK(ay)
AE4:AG7AE4=ROWSTREAK(ay,,1)
AI4:AK7AI4=ROWSTREAK(ay,,1,1)
AA12:AC15AA12=ROWSTREAK(ay,,,1)
AE12:AG15AE12=ROWSTREAK(ay,,2)
AI12:AK15AI12=ROWSTREAK(ay,,2,1)
AA20:AC23AA20=ROWSTREAK(ay,,,2)
AE20:AG23AE20=ROWSTREAK(ay,,3)
AI20:AK23AI20=ROWSTREAK(ay,,3,1)
AE28:AG31AE28=ROWSTREAK(ay,,4)
AI28:AK31AI28=ROWSTREAK(ay,,4,1)
Dynamic array formulas.
Named Ranges
NameRefers ToCells
ay='ROWSTREAK 2'!$B$4:$T$6AI28, AI20, AI12, AI4, AE28, AE20, AE12, AE4, AA20, AA12, AA4, W3
 
Extracting data from STREAKS 4D array(values, bycol, lengths, frequencies)
Chose 2 separate functions, and not one that embeds both, because it would have added an extra argument and with 3 arguments is easier to perceive, plus byrow/bycol approach became common in Excel for new functions, safer to use.
The main core part of the function (lambda helper function in MAP) will be identical for both functions, so only a few minor orientation changes to get to COLSTREAK.
COLSTREAK(a,[v],[n],[lf]) Calls STREAKS
a
: array
[v]: single value or array of values, if omitted, all unique values of "ar" are considered
[n]: numeric value length/frequency, if omitted max(length)/max(frequency) triggered by "lf" argument
[lf]: length/frequency argument, if omitted=> frequencies, if 1=> lengths, if n=0 and lf=2=>max frequencies and their lengths.
Excel Formula:
=LAMBDA(a, [v], [n], [lf],
    LET(
        b, STREAKS(a, v, 1),
        m, MAP(
            DROP(b, , 1),
            LAMBDA(x,
                LET(
                    f, --TEXTSPLIT(x, ","),
                    y, MAX(f),
                    ml, IF(y, COLUMNS(f), 0),
                    l, SEQUENCE(, ml),
                    IF(n, IF(lf, INDEX(f, n), TEXTJOIN(",", , IF(f = n, l, ""))), IF(lf, IF(lf = 1, ml, y & ";" & TEXTJOIN(",", , IF(f = y, l, ""))), y))
                )
            )
        ),
        e, IFERROR(HSTACK(TAKE(b, , 1), m), 0),
        IFERROR(--e, e)
    )
)
STREAK.xlsx
ABCDEFGH
1
2defined name "br" = B4:F43
3
4brCACCA
5CBCAB
6BCBBA
7ACBAC
8CCBAB
9ACACA
10AAABB
11AAACA
12CBCAC
13CBBBC
14BCACC
15AAABC
16BABCC
17ABBCC
18ABBBA
19CBBAB
20AABCA
21ABBBB
22CABAC
23ACBAA
24ACAAA
25ACACA
26ABAAA
27BABAC
28AACBA
29BCACC
30ACABA
31CABBA
32ACAAB
33CCABC
34CCBBB
35CCBAA
36BBBCB
37BABAC
38AAACC
39BBCBA
40BBBAB
41BBACA
42CBBAC
43ABCAA
44
COLSTREAK
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B4:F43Expression=B4="c"textNO
B4:F43Expression=B4="b"textNO
B4:F43Expression=B4="a"textNO
 
STREAK.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACAD
1all values, max frequenciesall values, max lengthsall values, lengths of max frequencies
2=STREAKS(br,,1)v, omitted=>all valuesv, omitted=>all valuesv, omitted=>all values
3A7,2,1,14,42,3,27,3,111,1,0,1n, omitted,maxn, omitted,maxn, omitted,max
4B5,1,14,1,1,0,15,0,1,1,0,0,0,18,29lf, omitted=> frequencieslf,1=> lengthslf,2=> max frequencies; corespondent lengths
5C5,2,11,1,1,24,110,16,1,0,0,0,1=COLSTREAK(br)=COLSTREAK(br,,,1)=COLSTREAK(br,,,2)
6A743711A42334A7;14;1,23;27;111;1
7B54589B35821B5;14;15;18;19;1
8C524106C34226C5;12;44;110;16;1
9
10lengths for frequencies=1frequencies for length=1
11v, omitted=>all valuesv, omitted=>all values
12n, 1n, 1
13lf, omitted=> frequencieslf,1=> lengths
14=COLSTREAK(br,,1)=COLSTREAK(br,,1,1)
15A3,432,4A742711
16B2,32,3,53,4,8B54589
17C31,2,3222,6C514106
18
19lengths for frequencies=2frequencies for length=2
20v, omitted=>all valuesv, omitted=>all values
21n, 2n, 2
22lf, omitted=> frequencieslf,1=> lengths
23=COLSTREAK(br,,2)=COLSTREAK(br,,2,1)
24A21,3A24331
25B2B11020
26C24C21111
27
28lengths for frequencies=4frequencies for length=4
29v, omitted=>all valuesv, omitted=>all values
30n, 4n, 4
31lf, omitted=> frequencieslf,1=> lengths
32=COLSTREAK(br,,4)=COLSTREAK(br,,4,1)
33A1,2A10001
34B1B00100
35C1C02000
36
COLSTREAK 1
Cell Formulas
RangeFormula
B2,P32,I32,P23,I23,P14,I14,W5,P5,I5B2=FORMULATEXT(B3)
B3:G5B3=STREAKS(br,,1)
I6:N8I6=COLSTREAK(br)
P6:U8P6=COLSTREAK(br,,,1)
W6:AB8W6=COLSTREAK(br,,,2)
I15:N17I15=COLSTREAK(br,,1)
P15:U17P15=COLSTREAK(br,,1,1)
I24:N26I24=COLSTREAK(br,,2)
P24:U26P24=COLSTREAK(br,,2,1)
I33:N35I33=COLSTREAK(br,,4)
P33:U35P33=COLSTREAK(br,,4,1)
Dynamic array formulas.
Named Ranges
NameRefers ToCells
br=COLSTREAK!$B$4:$F$43B3, I6, I15, I24, I33, P6, P15, P24, P33, W6
 
With this sample data, every clm is missing a value, so whatever arguments we are triggering, will always have 0's or ""'s diagonal.
STREAK.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAAB
1
2defined name "by" = B4:D22max frequencieslengths for frequencies=1frequencies for lengths=1
3n,lf, omittedn,1,lf,omittedn,1,lf,1
4byACB=STREAKS(by,,1)=COLSTREAK(by)=COLSTREAK(by,,1)=COLSTREAK(by,,1,1)
5AABA1,2,20,3,0,10A230A14A100
6AACB2,300,1,0,3B303B2B200
7BCCC01,41,2C042C11C011
8BCB
9AABmax lengthslengths for frequencies=2frequencies for lengths=2
10AABn,omitted,lf,1n,2,lf,omittedn,2,lf,1
11BCB=COLSTREAK(by,,,1)=COLSTREAK(by,,2)=COLSTREAK(by,,2,1)
12BCCA340A2,3A230
13AACB204B1B301
14AABC022C2C042
15ACB
16BCBmax frq; coresp. lengthslengths for frequencies=3frequencies for lengths=3
17BABn,omitted,lf,2n,3,lf,omittedn,3,lf,1
18AAC=COLSTREAK(by,,,2)=COLSTREAK(by,,3)=COLSTREAK(by,,3,1)
19AABA2;2,33;20A2A200
20BABB3;203;4B24B000
21ACBC04;22;2CC000
22BCB
23lengths for frequencies=4frequencies for lengths=4
24n,4,lf,omittedn,4,lf,1
25=COLSTREAK(by,,4)=COLSTREAK(by,,4,1)
26AA010
27BB003
28C2C000
29
COLSTREAK 2
Cell Formulas
RangeFormula
G4,V25,Q25,V18,Q18,L18,V11,Q11,L11,V4,Q4,L4G4=FORMULATEXT(G5)
G5:J7G5=STREAKS(by,,1)
L5:O7L5=COLSTREAK(by)
Q5:T7Q5=COLSTREAK(by,,1)
V5:Y7V5=COLSTREAK(by,,1,1)
L12:O14L12=COLSTREAK(by,,,1)
Q12:T14Q12=COLSTREAK(by,,2)
V12:Y14V12=COLSTREAK(by,,2,1)
L19:O21L19=COLSTREAK(by,,,2)
Q19:T21Q19=COLSTREAK(by,,3)
V19:Y21V19=COLSTREAK(by,,3,1)
Q26:T28Q26=COLSTREAK(by,,4)
V26:Y28V26=COLSTREAK(by,,4,1)
Dynamic array formulas.
Named Ranges
NameRefers ToCells
by='COLSTREAK 2'!$B$4:$D$22V26, V19, V12, V5, Q26, Q19, Q12, Q5, L19, L12, L5, G5
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B4:D22Expression=B4="c"textNO
B4:D22Expression=B4="b"textNO
B4:D22Expression=B4="a"textNO
 

Forum statistics

Threads
1,223,575
Messages
6,173,148
Members
452,502
Latest member
PQCurious

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