List the largest group of consecutive numbers in each column. Consecutive can be both increasing like 4, 5, 6 or decreasing 6, 5, 4. In case of more than one group of consecutive numbers, consider the first group. Ex. 3, 4, 2, 9, 8, 7, 6, 5, 1 There are 3 groups of consecutive numbers – (3, 4), (9, 8, 7), (7, 6, 5). Last two are the largest groups and we will consider 9, 8, 7 as the answer as it appears first.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 347
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Find Longest Consecutive Sequence with Power Query
Power Query solution 1 for Find Longest Consecutive Sequence, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
G = each List.Max(
List.Accumulate(
_,
{},
(s, c) =>
let
l = List.Last(s),
n = List.Last(l)
in
if s = {} then
s & {{c}}
else if Number.Abs(c - n) = 1 and (List.Count(l) = 1 or n + l{1} - l{0} = c) then
List.RemoveLastN(s) & {l & {c}}
else
s & {{c}}
),
0,
List.Count
),
S = Table.FromColumns(List.Transform(Table.ToColumns(Source), G), Table.ColumnNames(Source))
in
SPower Query solution 2 for Find Longest Consecutive Sequence, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
G = each List.Accumulate(
List.Accumulate(
_,
{},
(s, c) =>
let
l = List.Last(s),
n = List.Last(l)
in
if s = {} then
s & {{c}}
else if Number.Abs(c - n) = 1 and (List.Count(l) = 1 or n + l{1} - l{0} = c) then
List.RemoveLastN(s) & {l & {c}}
else
s & {{c}}
),
{},
(s, c) => if List.Count(c) > List.Count(s) then c else s
),
S = Table.FromColumns(List.Transform(Table.ToColumns(Source), G), Table.ColumnNames(Source))
in
SPower Query solution 3 for Find Longest Consecutive Sequence, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Fx = (f) =>
let
a = List.Accumulate(
List.Skip(f),
{f{0}},
(s, c) => if Number.Abs(c - List.Last(s)) = 1 then s & {c} else s & {null} & {c}
),
b = List.Generate(
() => [x = 0, y = 1],
each [x] <= List.Count(a),
each [y = if a{[x]} = null then [y] + 1 else [y], x = [x] + 1],
each [y]
),
c = Table.Group(
Table.SelectRows(Table.FromColumns({b, a}, {"A", "B"}), each [B] <> null),
"A",
{"B", each [B]}
)[B],
d = List.Max(List.Transform(c, List.Count))
in
List.Select(c, each List.Count(_) = d),
Sol = Table.FromColumns(
List.Transform(Table.ToColumns(Source), each Fx(_){0}),
Table.ColumnNames(Source)
)
in
SolPower Query solution 4 for Find Longest Consecutive Sequence, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
FX = (f) =>
let
A = List.Combine(
List.Transform(
{0 .. List.Count(f) - 1},
each List.Select(
List.Transform({1 .. List.Count(f)}, (x) => List.Skip(List.FirstN(f, x), _)),
each not List.IsEmpty(_)
)
)
),
B = List.Transform(
{0 .. List.Count(A) - 1},
each List.Distinct(
List.RemoveLastN(
List.Transform({0 .. List.Count(A{_}) - 1}, (x) => Number.Abs(A{_}{x + 1} - A{_}{x}))
)
)
= {1}
),
Listas = List.Transform(List.PositionOf(B, true, Occurrence.All), each A{_}),
Max = List.Max(List.Transform(Listas, each List.Count(_)))
in
List.Select(Listas, each List.Count(_) = Max){0},
Sol = Table.FromColumns(
List.Transform(Table.ToColumns(Source), each FX(_)),
Table.ColumnNames(Source)
)
in
SolSolving the challenge of Find Longest Consecutive Sequence with Excel
Excel solution 1 for Find Longest Consecutive Sequence, proposed by Bo Rydobon 🇹🇭:
=LET(
L,
LAMBDA(
a,
x,
LET(
d,
SCAN(
1,
DROP(
a,
1
)-DROP(
a,
-1
)=x,
LAMBDA(
c,
y,
y*c+1
)
),
m,
MAX(
d
),
n,
XMATCH(
m,
d
)+1,
VSTACK(
m-n%,
SEQUENCE(
m,
,
INDEX(
a,
n
)-x*m+x,
x
)
)
)
),
z,
A2:D20,
DROP(
REDUCE(
0,
SEQUENCE(
COLUMNS(
z
)
),
LAMBDA(
h,
n,
LET(
f,
INDEX(
z,
,
n
),
IFNA(
HSTACK(
h,
L(
f,
IF(
@L(
f,
1
)>@L(
f,
-1
),
1,
-1
)
)
),
""
)
)
)
),
1,
1
)
)Excel solution 2 for Find Longest Consecutive Sequence, proposed by Rick Rothstein:
=TRANSPOSE(
IFERROR(
TEXTSPLIT(
TEXTJOIN(
" ",
,
BYCOL(
A2:E20,
LAMBDA(
a,
LET(
x,
IF(
IFERROR(
ABS(
OFFSET(
a,
1,
)-a
)=1,
)+IFERROR(
ABS(
a-OFFSET(
a,
-1,
)
)=1,
),
a,
" "
),
t,
TEXTSPLIT(
TRIM(
TEXTJOIN(
"/",
0,
x
)
),
" "
),
l,
LEN(
t
)-LEN(
SUBSTITUTE(
t,
"/",
""
)
),
INDEX(
t,
XMATCH(
MAX(
l
),
l
)
)
)
)
)
),
"/",
" ",
1
),
""
)
)Excel solution 3 for Find Longest Consecutive Sequence, proposed by John V.:
=IFNA(
DROP(
REDUCE(
0,
ROW(
1:4
),
LAMBDA(
a,
v,
LET(
r,
LAMBDA(
r,
c,
i,
a,
d,
IF(
OFFSET(
c,
i-1,
)+d=OFFSET(
c,
i,
),
r(
r,
c,
i+1,
a+1,
d
),
a+1
)
),
b,
MAP(
INDEX(
A2:D20,
,
v
),
LAMBDA(
x,
LET(
a,
r(
r,
x,
1,
,
1
),
b,
r(
r,
x,
1,
,
-1
),
IF(
a>b,
a,
b
)
)
)
),
m,
MAX(
b
),
HSTACK(
a,
OFFSET(
A1,
XMATCH(
m,
b
),
v-1,
m
)
)
)
)
),
,
1
),
""
)Excel solution 4 for Find Longest Consecutive Sequence, proposed by محمد حلمي:
=DROP(
REDUCE(
0,
SEQUENCE(
4
),
LAMBDA(
acc,
s,
LET(
b,
INDEX(
A1:D20,
,
s
),
j,
LAMBDA(
q,
w,
LET(
rr,
LAMBDA(
h,
LET(
e,
SCAN(
1,
IF(
q+h=N(
+w
),
q,
""
),
LAMBDA(
a,
d,
IF(
ISNUMBER(
d
),
a,
a+1
)
)
),
REDUCE(
0,
UNIQUE(
e
),
LAMBDA(
a,
d,
HSTACK(
a,
FILTER(
q,
e=d
)
)
)
)
)
),
x,
HSTACK(
rr(
1
),
rr(
-1
)
),
i,
BYCOL(
x,
LAMBDA(
a,
COUNT(
a
)
)
),
TAKE(
FILTER(
x,
i=MAX(
i
)
),
,
1
)
)
),
IFNA(
HSTACK(
acc,
j(
DROP(
b,
1
),
DROP(
b,
-1
)
)
),
""
)
)
)
),
,
1
)Excel solution 5 for Find Longest Consecutive Sequence, proposed by Kris Jaganah:
=VSTACK("Value"&{1,
2,
3,
4},
IFNA(DROP(--REDUCE("",
BYCOL(A2:D20,
LAMBDA(a,
LET(b,
VSTACK(
DROP(
a,
1
),
99
),
c,
ABS(
a-b
),
d,
SEQUENCE(
ROWS(
c
)
),
e,
FILTER(
HSTACK(
a,
b,
d
),
c=1
),
f,
TAKE(
e,
,
-1
),
g,
VSTACK(
@f,
DROP(
f,
-1
)
),
h,
SCAN(
1,
f-g,
LAMBDA(
v,
w,
IF(
w>1,
v+1,
v
)
)
),
i,
MAP(h,
LAMBDA(z,
SUM(--(h=z))))-h/10,
TEXTJOIN(
",",
,
UNIQUE(
TOCOL(
FILTER(
TAKE(
e,
,
2
),
MAX(
i
)=i
)
)
)
)))),
LAMBDA(
y,
z,
HSTACK(
y,
TEXTSPLIT(
z,
,
","
)
)
)),
,
1),
"")&)Excel solution 6 for Find Longest Consecutive Sequence, proposed by Julian Poeltl:
=TRANSPOSE(
TEXTSPLIT(
TEXTJOIN(
"|",
,
BYCOL(
A2:D20,
LAMBDA(
A,
LET(
S,
SCAN(
1,
DROP(
VSTACK(
1,
DROP(
A,
1
)-A
),
-1
),
LAMBDA(
A,
B,
IF(
ABS(
B
)=1,
A,
A+1
)
)
),
TEXTJOIN(
",",
,
FILTER(
A,
S=XMATCH(
100,
MAP(
UNIQUE(
S
),
LAMBDA(
A,
ROWS(
FILTER(
S,
S=A
)
)
)
),
-1
)
)
)
)
)
)
),
",",
"|",
,
,
""
)
)Excel solution 7 for Find Longest Consecutive Sequence, proposed by Timothée BLIOT:
=DROP(
IFNA(
REDUCE(
"",
SEQUENCE(
4
),
LAMBDA(
y,
x,
LET(
C,
Index(
A2:D20,
,
x
),
A,
--SCAN(
0,
SEQUENCE(
18
),
LAMBDA(
w,
v,
IF(
ABS(
INDEX(
C,
v
)-INDEX(
C,
v+ 1
)
)=1,
1+w ,
0
)
)
),
HSTACK(
y,
TAKE(
TAKE(
C,
XMATCH(
MAX(
A
),
A
)+1
),
-MAX(
A
)-1
)
)
)
)
),
""
) ,
,
1
)Excel solution 8 for Find Longest Consecutive Sequence, proposed by Hussein SATOUR:
=TRANSPOSE(TEXTSPLIT(CONCAT(BYCOL(A2:D20, LAMBDA(x, CONCAT(LET(a, ABS(DROP(x, -1) - DROP(x, 1)),
b, LEN(SUBSTITUTE(SCAN(,a,LAMBDA(x,y, x&y)), "1", "")),
c, MODE(b), d, XMATCH(c, b)+1, e, COUNT(FILTER(b, b = c)),
INDEX(x, IF(e=18, SEQUENCE(19), SEQUENCE(e,, d))))&"/")))&"|"), "/", "|",1,,""))Excel solution 9 for Find Longest Consecutive Sequence, proposed by Duy Tùng:
=DROP(
REDUCE(
0,
A2:D2,
LAMBDA(
x,
y,
LET(
a,
TAKE(
D20:y,
,
1
),
b,
SCAN(
0,
ABS(
a-VSTACK(
0,
DROP(
a,
-1
)
)
)<>1,
SUM
),
c,
DROP(
FREQUENCY(
b,
b
),
-1
),
IFNA(
HSTACK(
x,
FILTER(
a,
XLOOKUP(
MAX(
c
),
c,
b
)=b
)
),
""
)
)
)
),
,
1
)Excel solution 10 for Find Longest Consecutive Sequence, proposed by Sunny Baggu:
=IFNA(
DROP(
REDUCE(
"",
SEQUENCE(
COLUMNS(
A1:D1
)
),
LAMBDA(
a,
v,
HSTACK(
a,
LET(
_c,
INDEX(
A2:D20,
,
v
),
_c1,
ABS(
VSTACK(
0,
DROP(
_c,
1
) - DROP(
_c,
-1
)
)
),
_seq,
SEQUENCE(
ROWS(
_c1
)
),
_t,
CONCAT(
IF(
_c1 = 1,
_seq & ",",
"x"
)
),
_c3,
TEXTSPLIT(
_t,
,
"x",
1
),
_tb,
TEXTBEFORE(
_c3,
","
) - 1,
_ta,
TEXTAFTER(
TEXTBEFORE(
_c3,
",",
-1
),
",",
-1,
,
,
_tb
) + 0,
_diff,
_ta - _tb + 1,
_max,
MAX(
_diff
),
_r1,
XLOOKUP(
_max,
_diff,
_tb
),
INDEX(
_c,
SEQUENCE(
_max,
,
_r1
)
)
)
)
)
),
,
1
),
""
)Excel solution 11 for Find Longest Consecutive Sequence, proposed by LEONARD OCHEA 🇷🇴:
=IFNA(DROP(REDUCE("",SEQUENCE(4),LAMBDA(x,y, LET(v,INDEX(A2:D20,,y),s,SCAN(0,--(ABS(DROP(v,-1)-DROP(v,1))=1),LAMBDA(a,b,IF(b,a+b,0))),m,MAX(s),HSTACK(x,INDEX(v,SEQUENCE(m+1,,XMATCH(1,--(s=m))-m+1)))))),,1),"")Excel solution 12 for Find Longest Consecutive Sequence, proposed by JvdV –:
=DROP(
IFNA(
REDUCE(
0,
ROW(
1:4
),
LAMBDA(
a,
b,
HSTACK(
a,
LET(
c,
CHOOSECOLS(
A2:D20,
b
),
r,
21-ROW(
1:20
),
x,
TOCOL(
FIND(
REPT(
1,
r
),
BYCOL(
N(
DROP(
c,
-1
)+{1,
-1}=DROP(
c,
1
)
),
CONCAT
)
)&-r,
3
),
q,
@SORTBY(
x,
-TEXTAFTER(
x,
"-"
),
,
--TEXTBEFORE(
x,
"-"
),
),
INDEX(
c,
SEQUENCE(
TEXTAFTER(
q,
"-"
)+1,
,
--TEXTBEFORE(
q,
"-"
)
)
)
)
)
)
),
""
),
,
1
)Excel solution 13 for Find Longest Consecutive Sequence, proposed by Pieter de Bruijn:
=LET(f,LAMBDA(g,REDUCE("",g,LAMBDA(a,b,LET(c,REDUCE("",b:A20,LAMBDA(x,y,LET(s,SEQUENCE(ROWS(b:y),,b),IF(OR(AND(b:y=SEQUENCE(ROWS(b:y),,b)),AND(b:y=SEQUENCE(ROWS(b:y),,b,-1)))*(ROWS(b:y)>COLUMNS(x)),TOROW(b:y),x)))),IF(ROWS(a)>ROWS(TOCOL(c)),a,TOCOL(c)))))),IFNA(HSTACK(f(A2:A20),f(B2:B20),f(C2:C20),f(D2:D20)),""))Excel solution 14 for Find Longest Consecutive Sequence, proposed by Pieter de Bruijn:
=LET(n,A2:D20,DROP(REDUCE("",SEQUENCE(COLUMNS(n)),LAMBDA(v,w,LET(a,INDEX(n,,w),b,SCAN(0,ROW(n)-1,LAMBDA(x,y,LET(z,INDEX(a,y+{0,1},1),IF(IFERROR(SUM(z*{-1,1})=1,0),((x>-1)*x)+1,IF(IFERROR(SUM(z*{1,-1})=1,0),((x<1)*x)-1,0))))),c,ABS(b),d,MAX(c),e,XMATCH(d,c)+1,i,INDEX(a,SEQUENCE(d+1,,e-d)),IFNA(HSTACK(v,i),"")))),,1))Excel solution 15 for Find Longest Consecutive Sequence, proposed by Giorgi Goderdzishvili:
=LET(
_oc,
BYCOL(A2:D20,
LAMBDA(c,
LET(
_arr,
c,
_gr,
DROP(
_arr,
1
) - DROP(
_arr,
-1
),
_sc,
SCAN(0,
--(_gr=1),
LAMBDA(
x,
y,
IF(
y=1,
1+x,
0
)
)),
_st,
XMATCH(
MAX(
_sc
),
_sc,
0
),
_rng,
FILTER(_arr,
(ROW(
_arr
)>=(_st-MAX(
_sc
)+2))*(ROW(
_arr
)<=(_st+2))),
_sc2,
SCAN(0,
--(_gr=-1),
LAMBDA(
x,
y,
IF(
y=1,
1+x,
0
)
)),
_st2,
XMATCH(
MAX(
_sc2
),
_sc2,
0
),
_rng2,
FILTER(_arr,
(ROW(
_arr
)>=(_st2-MAX(
_sc2
)+2))*(ROW(
_arr
)<=(_st2+2))),
_fin,
IF(
AND(
ROWS(
_rng
)=ROWS(
_rng2
),
_st<_st2
),
_rng,
IF(
ROWS(
_rng
)>ROWS(
_rng2
),
_rng,
_rng2
)
),
ARRAYTOTEXT(
_fin
)))),
_fn,
TRANSPOSE(
TEXTSPLIT(
CONCAT(
_oc&"!"
),
", ",
"!",
TRUE,
,
""
)
),
_fn)Excel solution 16 for Find Longest Consecutive Sequence, proposed by Luis Couto:
=REDUCE(
0,
{1;2;3;4},
LAMBDA(
a,
j,
LET(
v,
INDEX(
A2:D20,
,
j
),
d,
SCAN(
0,
VSTACK(
DROP(
v,
1
),
0
)-v,
LAMBDA(
a,
x,
IF(
ABS(
x
)=1,
a+1,
0
)
)
),
m,
MAX(
d
),
r,
INDEX(
v,
SEQUENCE(
m+1,
,
MATCH(
m,
d,
0
)-m+1,
1
)
),
IFNA(
IF(
OR(
a
),
HSTACK(
a,
r
),
r
),
""
)
)
)
)Solving the challenge of Find Longest Consecutive Sequence w&ith R
R solution 1 for Find Longest Consecutive Sequence, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("Max Consecutive Numbers.xlsx", range = "A1:D20")
test = read_excel("Max Consecutive Numbers.xlsx", range = "F2:I21")
find_cons_series = function(column) {
series_df = tibble(
number = column,
lagged = lag(column),
diff = abs(column - lag(column))
) %>%
filter(diff == 1) %>%
mutate(series_diff = abs(number - lag(number, default = 1)),
series = cumsum(ifelse(series_diff != 1, 1, 0))
) %>%
group_by(series) %>%
summarise(start = first(lagged),
end = last(number),
length = n()) %>%
ungroup() %>%
arrange(desc(length))
longest_series = first(series_df)
sequence = seq(longest_series$start, longest_series$end)
return(sequence)
}
columns = colnames(input)
result = map(columns, ~find_cons_series(input[[.x]])) %>%
map(~c(., rep(NA, 19 - length(.)))) %>%
bind_cols() %>%
set_names(columns) %>%
mutate_all(as.numeric)
&