If there are more than one consecutive streaks of H or T, then list the Indexes of those. Index 2 has 4 consecutive streaks including itself. Index 3 has 3 consecutive streaks including itself. Index 4 has 2 consecutive streaks including itself. Index 5 has no H following it, hence not counted.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 590
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of List Consecutive Streak Indexes with Power Query
Power Query solution 1 for List Consecutive Streak Indexes, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Combine(
Table.Group(
Source,
{"Result"},
{
{
"A",
each
let
a = _,
b = List.Reverse({1 .. Table.RowCount(a)}),
c = Table.FromColumns(Table.ToColumns(a) & {b}, {"A", "B", "Consecutives"})
in
c
}
},
0
)[A]
),
Tbl = Table.SelectRows(Group, each [Consecutives] > 1),
Pivot = Table.Pivot(
Tbl,
List.Distinct(Tbl[B]),
"B",
"A",
each Text.Combine(List.Transform(List.Sort(_), Text.From), ", ")
),
Sol = Table.Sort(Pivot, {{"Consecutives", 1}})
in
SolPower Query solution 2 for List Consecutive Streak Indexes, proposed by Glyn Willis:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Sorted Rows" = Table.Sort(Source, {{"Index", Order.Descending}}),
Custom1 =
let
l = List.Buffer(#"Sorted Rows"[Result]),
c = List.Count(l),
lg = List.Generate(
() => [i = 0, Consecutive No = 1, item = l{i}],
each [i] < c,
each [
i = [i] + 1,
Consecutive No = if item = [item] then [Consecutive No] + 1 else 1,
item = l{i}
],
each [Consecutive No]
),
comb = Table.FromColumns(Table.ToColumns(#"Sorted Rows") & {lg})
in
comb,
#"Filtered Rows" = Table.SelectRows(Custom1, each List.Contains({4, 3, 2}, [Column3])),
#"Pivoted Column" = Table.Pivot(
#"Filtered Rows",
List.Distinct(#"Filtered Rows"[Column2]),
"Column2",
"Column1",
each Text.Combine(List.Transform(List.Sort(_), (x) => Text.From(x)), ", ")
),
#"Sorted Rows1" = Table.Sort(#"Pivoted Column", {{"Column3", Order.Descending}})
in
#"Sorted Rows1"Power Query solution 3 for List Consecutive Streak Indexes, proposed by Joevan Bedico:
let
Source = Excel.CurrentWorkbook(){[Name = "Table4"]}[Content],
AddCons =
let
r = Source[Result],
t = List.Transform
in
Table.FromRows(
List.Select(
t(
Table.ToRows(Source),
each t(_, Text.From)
& {List.Count(List.FirstN(List.Skip(r, _{0} - 1), (x) => x = _{1}))}
),
each _{2} > 1
),
{"I", "R", "Consecutives"}
),
Answer = Table.Sort(
Table.Pivot(AddCons, List.Distinct(AddCons[R]), "R", "I", each Text.Combine(_, ", ")),
{"Consecutives", Order.Descending}
)
in
AnswerSolving the challenge of List Consecutive Streak Indexes with Excel
Excel solution 1 for List Consecutive Streak Indexes, proposed by Bo Rydobon 🇹🇭:
=LET(r,B3:B22,n,MAP(r,LAMBDA(a,XMATCH(TRUE,a<>a:B23)-1)),PIVOTBY(n,r,A3:A22,ARRAYTOTEXT,,0,-1,0,,n>1))Excel solution 2 for List Consecutive Streak Indexes, proposed by 🇰🇷 Taeyong Shin:
=LET(
r,
B3:B22,
i,
SEQUENCE(
ROWS(
r
)+1
),
n,
REDUCE(
0,
E2:F2,
LAMBDA(
a,
v,
a+DROP(
XMATCH(
i,
IF(
VSTACK(
r,
v
)=v,
i
),
1
)-i,
-1
)
)
),
PIVOTBY(
n,
r,
DROP(
i,
-1
),
ARRAYTOTEXT,
,
0,
-1,
0,
,
n>1
)
)Excel solution 3 for List Consecutive Streak Indexes, proposed by Julian Poeltl:
=LET(
M,
CONCAT(
B3:B22
),
L,
LAMBDA(
C,
N,
UNIQUE(
TOCOL(
SEARCH(
REPT(
C,
N
),
M,
SEQUENCE(
20
)
),
3
)
)
),
REDUCE(
HSTACK(
"Consecutives",
"H",
"T"
),
SEQUENCE(
3,
,
4,
-1
),
LAMBDA(
A,
B,
VSTACK(
A,
HSTACK(
B,
TEXTJOIN(
", ",
,
LET(
R,
L(
"H",
B
),
FILTER(
R,
NOT(
ISNUMBER(
XMATCH(
"*"&R&"*",
CHOOSECOLS(
A,
2
),
2
)
)
)
)
)
),
TEXTJOIN(
", ",
,
LET(
R,
L(
"T",
B
),
FILTER(
R,
NOT(
ISNUMBER(
XMATCH(
"*"&R&"*",
DROP(
A,
,
2
),
2
)
)
)
)
)
)
)
)
)
)
)Excel solution 4 for List Consecutive Streak Indexes, proposed by Aditya Kumar Darak 🇮🇳:
=LET(
_index, A3:A22,
_ht, B3:B22,
_check, VSTACK(TRUE, DROP(_ht, 1) = DROP(_ht, -1)),
_cons, SCAN(0, _check, LAMBDA(a,b, IF(b, a + 1, 1))),
_brk, SCAN(1, 1 - _check, SUM),
_sort, SORTBY(_cons, _brk, 1, _cons, -1),
_return, PIVOTBY(_sort, _ht, _index, ARRAYTOTEXT, 0, 0, -1, 0, , _sort > 1),
_return
)Excel solution 5 for List Consecutive Streak Indexes, proposed by Timothée BLIOT:
=SORT(
DROP(
REDUCE(
0,
ROW(
2:4
),
LAMBDA(
w,
v,
LET(
A,
LAMBDA(
n,
TEXTSPLIT(
REPT(
n,
v
),
,
":",
1
)
),
B,
A(
"T:"
),
C,
A(
"H:"
),
D,
LAMBDA(
m,
LET(
E,
MAP(
SEQUENCE(
21-v
),
LAMBDA(
x,
IF(
PRODUCT(
--IFNA(
DROP(
B3:B22,
x-1
)=m,
1
)
),
x,
""
)
)
),
TEXTJOIN(
", ",
,
FILTER(
E,
NOT(
MAP(
E,
LAMBDA(
x,
ISNUMBER(
XMATCH(
x+1,
E
)
)
)
)
)
)
)
)
),
VSTACK(
w,
HSTACK(
v,
D(
C
),
D(
B
)
)
)
)
)
),
1
),
,
-1
)Excel solution 6 for List Consecutive Streak Indexes, proposed by Oscar Mendez Roca Farell:
=DROP(PIVOTBY(MAP(B3:B22,LAMBDA(b,XMATCH(0,N(b:B23=b))-1)),B3:B22,A3:A22,ARRAYTOTEXT,,0,-1,0),-1)Excel solution 7 for List Consecutive Streak Indexes, proposed by Sunny Baggu:
=LET(
_e1, LAMBDA(arr,
LET(
_k, CONCAT(B3:B22),
_n, SEQUENCE(5, , 6, -1),
_r, MAP(_n, LAMBDA(a, REPT(arr, a))),
_s, SEQUENCE(LEN(_k)),
_h, MAP(
SEQUENCE(5),
LAMBDA(n,
LET(
_a, _s * (MID(_k, _s, INDEX(_n, n, 1)) = INDEX(_r, n, 1)),
_b, (DROP(_a, 1) = 0) * DROP(_a, -1),
_c, ARRAYTOTEXT(FILTER(_b, _b > 0)),
_c
)
)
),
FILTER(HSTACK(_n, _h), NOT(ISERR(_h)))
)
),
HSTACK(_e1("H"), TAKE(_e1("T"), , -1))
)Excel solution 8 for List Consecutive Streak Indexes, proposed by Md. Zohurul Islam:
=LET(
a,A3:A22,
p,B4:B22,q,B3:B21,r,B3:B22,
b,ABS(p=q),
f,VSTACK(1,b),
g,SCAN(0,f,LAMBDA(x,y,IF(y,x+1,1))),
h,SCAN(1,1-f,LAMBDA(x,y,x+y)),
j,SORTBY(g,h,1,g,-1),
k,PIVOTBY(j,r,a,ARRAYTOTEXT, 0, 0, -1, 0, , j > 1),
hdr,VSTACK("Consecutives",DROP(TAKE(k,,1),1)),
result,HSTACK(hdr,DROP(k,,1)),
result)Excel solution 9 for List Consecutive Streak Indexes, proposed by Hamidi Hamid:
=LET(
x,
SORTBY(
A3:B22,
A3:A22,
-1
),
f,
LAMBDA(
m,
LEN(
SCAN(
,
IF(
TAKE(
x,
,
-1
)=m,
TAKE(
x,
,
-1
),
""
),
LAMBDA(
a,
v,
IF(
v=m,
a & v,
v
)
)
)
)
),
y,
f(
"H"
),
s,
SORT(
UNIQUE(
FILTER(
y,
y>1
)
),
,
-1
),
ww,
LAMBDA(
n,
MAP(
s,
LAMBDA(
a,
ARRAYTOTEXT(
SORT(
FILTER(
TAKE(
x,
,
1
),
n=a
)
)
)
)
)
),
z,
ww(
y
),
g,
f(
"T"
),
zz,
ww(
g
),
HSTACK(
s,
z,
zz
)
)Excel solution 10 for List Consecutive Streak Indexes, proposed by ferhat CK:
=REDUCE(SEQUENCE(3,,4,-1),{"H","T"},LAMBDA(f,g,HSTACK(f,LET(j,IF(B3:B22=g,B3:B22,""),a,CONCAT(SCAN(0,j,LAMBDA(a,v,IFS((v=g),a+1,(a=1)*(v=g),a+1,v="",0)))),c,ARRAYTOTEXT,lm,LAMBDA(x,UNIQUE(TOCOL(FIND(x,a,SEQUENCE(19)),3))),rrr,lm(1234),rr,IF(lm(123)=rrr,rrr+1,lm(123)),r,IFS(lm(12)=rrr,rrr+2,lm(12)=rr,rr+1,TRUE,lm(12)),VSTACK(c(rrr),c(rr),c(r))))))Excel solution 11 for List Consecutive Streak Indexes, proposed by Imam Hambali:
=LET(
r,--( B3:B22={"H","T"}),
cc, CHOOSECOLS,
l, LAMBDA(x, SORTBY(x, A3:A22,-1)),
s, LAMBDA(a, l(SCAN(, l(a), LAMBDA(x,y, IF(y=0,0, y+x))))),
gb, LAMBDA(x, GROUPBY(x, A3:A22,ARRAYTOTEXT,0,0,,x>1)),
hs, HSTACK(gb(s(cc(r,1))), cc(gb(s(cc(r,2))),2) ),
VSTACK(D2:F2, SORT(hs,1,-1))
)Excel solution 12 for List Consecutive Streak Indexes, proposed by Julien Lacaze:
=LET(
i,
A3:A22,
l,
B3:B22,
rl,
SORTBY(
l,
i,
-1
),
s,
MAP(
i,
LAMBDA(
m,
INDEX(
REDUCE(
,
i,
LAMBDA(
a,
v,
VSTACK(
a,
IF(
INDEX(
rl,
v,
1
)=INDEX(
rl,
m,
1
),
TAKE(
a,
-1
)+1,
0
)
)
)
),
m
)
)
),
DROP(
PIVOTBY(
SORTBY(
s,
i,
& -1
),
l,
i,
ARRAYTOTEXT,
0,
0,
-1,
0
),
-1
)
)
What it does :
The REDUCE is here to calculate consecutive streaks
of a given list : rl
of a given paramater : INDEX(
rl,
m
)
It is encapsulated in a MAP,
to retrieve the current streak of each row.
The given list is the reverted initial list : SORTBY(
l,
i,
-1
)
The PIVOTBY handle the output format (list of streaks is reverted back)Solving the challenge of List Consecutive Streak Indexes with Python
Python solution 1 for List Consecutive Streak Indexes, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "590 Consecutive Streaks.xlsx"
input = pd.read_excel(path, usecols="A:B", skiprows=1, nrows=21)
test = pd.read_excel(path, usecols="D:F", skiprows=1, nrows=3)
test['H'] = test['H'].astype(str)
test['T'] = test['T'].astype(str)
def consecutive_id(series):
return (series != series.shift()).cumsum()
def process(df, starting_index):
df = df.iloc[starting_index-1:]
df = df.assign(cons=consecutive_id(df['Result']))
df = df[df['cons'] == 1]
result = {
'Index': df['Index'].min(),
'Result': df['Result'].min(),
'Streak': len(df)
}
return pd.DataFrame([result])
result = pd.concat([process(input, i) for i in range(1, 21)])
result = result[result['Streak'] != 1]
result = result.pivot_table(index='Streak', columns='Result', values='Index', aggfunc=lambda x: ', '.join(map(str, x))).sort_values('Streak', ascending=False)
result.reset_index(inplace=True)
result.columns.name = None
result.rename(columns={'Streak': 'Consecutives'}, inplace=True)
print(result.equals(test)) # True
Solving the challenge of List Consecutive Streak Indexes with Python in Excel
Python in Excel solution 1 for List Consecutive Streak Indexes, proposed by Alejandro Campos:
df = xl("A2:B22", headers=True)
def find_streaks(df):
streaks = {k: {'H': [], 'T': []} for k in ['4', '3', '2']}
for i in range(len(df) - 1):
for k in [4, 3, 2]:
if i <= len(df) - k and len(set(df.loc[i:i+k-1, 'Result'])) == 1:
streaks[str(k)][df.loc[i, 'Result']].append(df.loc[i, 'Index'])
break
return streaks
streaks = find_streaks(df)
result_df = pd.DataFrame({
'Consecutives': ['4', '3', '2'],
'H': [', '.join(map(str, streaks[k]['H'])) for k in ['4', '3', '2']],
'T': [', '.join(map(str, streaks[k]['T'])) for k in ['4', '3', '2']]
})
result_df
Python in Excel solution 2 for List Consecutive Streak Indexes, proposed by Anshu Bantra:
df = xl("B2:B22", headers=True)
string = ''.join(df.values.flatten())
reps, result, position, lst = [], [], [], []
for char in set(string):
for consec in range(4, 1, -1):
vals = []
for idx in range(len(string)):
if char*consec in string[idx:idx+consec] and idx+1 not in lst:
vals.append(idx+1); lst.append(idx+1)
reps.append(consec)
result.append(char)
position.append(','.join(map(str,vals)))
ans = pd.DataFrame([reps, result, position]).T
ans = ans.pivot(index=0, columns=1, values=2)
ans = ans.sort_index(ascending=False).reset_index()
ans.columns = ['Consecutives', 'H', 'T']
ans
Solving the challenge of List Consecutive Streak Indexes with R
R solution 1 for List Consecutive Streak Indexes, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/590 Consecutive Streaks.xlsx"
input = read_excel(path, range = "A2:B22")
test = read_excel(path, range = "D2:F5")
process = function(df, starting_index) {
df = df %>%
filter(row_number() >= starting_index) %>%
mutate(cons = consecutive_id(Result)) %>%
filter(cons == 1) %>%
summarise(Index = min(Index),
Result = min(Result),
Streak = n())
return(df)
}
result = map_dfr(1:20, ~process(input, .x)) %>%
filter(Streak != 1) %>%
pivot_wider(names_from = Result, values_from = Index, values_fn = ~ paste(.x, collapse = ", ")) %>%
select(Consecutives = Streak, everything())
all.equal(result, test, check.attributes = FALSE)
# [1] TRUE
&&