This challenge is contributed by Duy Tùng The task is to assign sequential numbers to the Result column based on the Number column. Each ID has corresponding numbers. If the numbers for an ID are not consecutive, they should be marked as 1. However, if the numbers are consecutive, they should be marked according to their count. For example, ID 1000008 has numbers 5, 7, 15, 16; since 5 and 7 are not consecutive, they would each be marked as 1. But since 15 and 16 are consecutive, they would be marked as 2, which is the correct result.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 689
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Sequential Counter For IDs with Power Query
_x000D_Power Query solution 1 for Sequential Counter For IDs, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.AddIndexColumn(A, "Ix"),
C = Table.AddColumn(B, "Calc", each [ID] + ([Ix] / 100) - ([Number] / 100)),
D = Table.AddColumn(
C,
"Answer Expected",
each List.Count(List.Select(C[Calc], (x) => x = [Calc]))
)[[Answer Expected]]
in
DPower Query solution 2 for Sequential Counter For IDs, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Grp = Table.Combine(
Table.Group(
Source,
{"ID"},
{
{
"A",
each
let
a = _,
b = Table.AddIndexColumn(a, "Idx", 0),
c = Table.Group(
b,
"Idx",
{{"B", each [[ID]]}, {"Answer", each List.Count([Number])}},
0,
(x, y) =>
let
d = b[Number]{y} - b[Number]{y - 1},
e = if d <> 1 then 1 else 0
in
e
)[[B], [Answer]]
in
c
}
}
)[A]
),
Sol = Table.ExpandTableColumn(Grp, "B", Table.ColumnNames(Grp[B]{0}))[[Answer]]
in
SolPower Query solution 3 for Sequential Counter For IDs, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(
Table.AddIndexColumn(Source, "Idx"),
{"ID", "Number", "Idx"},
{"Answer Expected", each List.Repeat({Table.RowCount(_)}, Table.RowCount(_))},
0,
(x, y) => Number.From(x[ID] <> y[ID] or x[Number] - x[Idx] <> y[Number] - y[Idx])
)[[Answer Expected]],
Res = Table.ExpandListColumn(Group, "Answer Expected")
in
ResPower Query solution 4 for Sequential Counter For IDs, proposed by Meganathan Elumalai:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
AddCol = Table.ReplaceValue(
Table.AddIndexColumn(Source, "Result"),
each [Number],
each [Result],
(x, y, z) => y - z,
{"Result"}
),
Result = Table.Combine(
Table.Group(
AddCol,
{"ID", "Result"},
{
"New",
each Table.ReplaceValue(
_,
(x) => x[Result],
(y) => Table.RowCount(_),
Replacer.ReplaceValue,
{"Result"}
)
},
0
)[New]
)[[Result]]
in
ResultPower Query solution 5 for Sequential Counter For IDs, proposed by Antriksh Sharma:
let
Source = Table,
Transform = List.TransformMany(
List.Distinct(Source[ID]),
(x) => {
let
Data = Table.SelectRows(Source, each [ID] = x),
Result = Table.Group(
Data,
"Number",
{
"T",
each
let
a = [Number],
b = List.Count(a),
c = List.Repeat({b}, b),
d = Table.FromColumns(Table.ToColumns(_) & {c}, {"ID", "Number", "Answer"})
in
d
},
GroupKind.Local,
(x, y) =>
let
a = {null} & Data[Number],
b = List.PositionOf(a, y),
c = (a{b} - a{b - 1}) <> 1,
d = Byte.From(c)
in
d
)[T]
in
Result
},
(x, y) => Table.Combine(y)
),
Result = Table.Combine(Transform)
in
ResultPower Query solution 6 for Sequential Counter For IDs, proposed by Peter Krkos:
PowerQuery solution:
= Table.Combine(Table.Group(Table.AddIndexColumn(Source, "i"), {"ID", "Number", "i"}, {{"T", each
Table.AddColumn(Table.RemoveColumns(_, "i"), "Answer", (x)=> Table.RowCount(_), Int64.Type)}},
0, (x,y)=> Byte.From( y[ID] <> x[ID] or Number.Abs(y[Number]-x[Number]) <> y[i]-x[i]))[T])
Power Query solution 7 for Sequential Counter For IDs, proposed by Aleksandar Kovacevic:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Lst = List.Transform(
{0 .. List.Count(Source[ID]) - 1},
each Source[ID]{_} * 100 - Source[Number]{_} + _
),
Res = Table.AddColumn(
Table.FromColumns({Lst}, {"C"}),
"Answer Expected",
each List.Count(List.Select(Lst, (x) => x = [C]))
)[[Answer Expected]]
in
ResSolving the challenge of Sequential Counter For IDs with Excel
_x000D_Excel solution 1 for Sequential Counter For IDs, proposed by Bo Rydobon 🇹🇭:
=LET(
n,
A2:A29*100+B2:B29-ROW(
B2:B29
),
MAP(
n,
LAMBDA(
x,
SUM(
N(
x=n
)
)
)
)
)Excel solution 2 for Sequential Counter For IDs, proposed by John V.:
=LET(n,B2:B29,i,A2:A29+n%-ROW(n)%,MAP(i,LAMBDA(x,SUM(N(x=i)))))Excel solution 3 for Sequential Counter For IDs, proposed by Kris Jaganah:
=LET(b,B2:B29,c,SEQUENCE(ROWS(b)),d,A2:A29+c/100-(b/100),MAP(d,LAMBDA(x,SUM(N(x=d)))))Excel solution 4 for Sequential Counter For IDs, proposed by Kris Jaganah:
=REDUCE(
"Answer Expected",
UNIQUE(
A2:A29
),
LAMBDA(
m,
n,
VSTACK(
m,
LET(
a,
FILTER(
B2:B29,
A2:A29=n
),
b,
SEQUENCE(
ROWS(
a
)
),
c,
a-XMATCH(
a+b,
a+b
),
MAP(
c,
LAMBDA(
v,
SUM(
N(
v=c
)
)
)
)
)
)
)
)Excel solution 5 for Sequential Counter For IDs, proposed by Timothée BLIOT:
=LET(
A,
A2:A29+B2:B29/100-ROW(
1:28
)/100,
MAP(
A,
LAMBDA(
x,
SUM(
N(
x=A
)
)
)
)
)Excel solution 6 for Sequential Counter For IDs, proposed by Oscar Mendez Roca Farell:
=LET(d,B2:B29,n,A2:A29%+d-ROW(d),MAP(n,LAMBDA(a,SUM(N(n=a)))))Excel solution 7 for Sequential Counter For IDs, proposed by Duy Tùng:
=LET(b,B2:B29,a,A2:A29&b-ROW(b),BYROW(N(a=TOROW(a)),SUM))Excel solution 8 for Sequential Counter For IDs, proposed by Md. Zohurul Islam:
=LET(u,A2:A29,v,B2:B29,n,v-SEQUENCE(ROWS(u)),w,MAP(u,n,LAMBDA(x,y,SUM(ABS(u=x)*ABS(n=y)))),w)Excel solution 9 for Sequential Counter For IDs, proposed by Hamidi Hamid:
=LET(
x,
SCAN(
0,
A1:A28<>A2:A29,
SUM
),
y,
SCAN(
0,
B1:B28<>B2:B29-1,
SUM
),
w,
x+y,
v,
MAP(
w,
LAMBDA(
a,
SUM(
N(
w=a
)
)
)
),
v
)Excel solution 10 for Sequential Counter For IDs, proposed by Asheesh Pahwa:
=REDUCE(
D1,
UNIQUE(
A2:A29
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
f,
FILTER(
B2:B29,
A2:A29=y
),
s,
SEQUENCE(
ROWS(
f
)
),
d,
f-s,
MAP(
d,
LAMBDA(
z,
SUM(
N(
d=z
)
)
)
)
)
)
)
)Excel solution 11 for Sequential Counter For IDs, proposed by Dhaval Patel:
=SUMPRODUCT(($A$2:$A$29=A2)*((B$2:B$29-ROW(
B$2:B$29
))=(B2-ROW())))Excel solution 12 for Sequential Counter For IDs, proposed by Fredson Alves Pinho:
=LET(a,
A2:A29,
b,
B2:B29-SEQUENCE(
28
),
MAP(a,
b,
LAMBDA(x,
y,
SUMPRODUCT(--(a=x),
--(b=y)))))Solving the challenge of Sequential Counter For IDs with Python
_x000D_Python solution 1 for Sequential Counter For IDs, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "689 Consecutive Numbers Marking.xlsx"
input = pd.read_excel(path, usecols="A:B", nrows=29)
test = pd.read_excel(path, usecols="D", nrows=29)
input['rn'] = range(1, len(input) + 1)
input['group_id'] = (
(input['ID'] != input['ID'].shift(1)) |
((input['Number'] - input['rn']) != (input['Number'] - input['rn']).shift(1))
).cumsum()
input['answer_expected'] = input.groupby('group_id')['group_id'].transform('size')
result = input[['answer_expected']]
print(result['answer_expected'].equals(test['Answer Expected']))
Solving the challenge of Sequential Counter For IDs with Python in Excel
_x000D_Python in Excel solution 1 for Sequential Counter For IDs, proposed by Alejandro Campos:
df = xl("A1:B29", headers=True); df['orig_index'] = df.index
def f(g):
g = g.sort_values('Number'); n = g['Number'].tolist(); r = [1]*len(n); i = 0
while i < len(n):
j = i
while j+1 < len(n) and n[j+1] == n[j]+1: j += 1
for k in range(i, j+1): r[k] = j - i + 1
i = j + 1
g['Result'] = r; return g
df = df.groupby('ID', group_keys=False).apply(f).sort_values('orig_index').drop(columns='orig_index').reset_index(drop=True)
Solving the challenge of Sequential Counter For IDs with R
_x000D_R solution 1 for Sequential Counter For IDs, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/689 Consecutive Numbers Marking.xlsx"
input = read_excel(path, range = "A1:B29")
test = read_excel(path, range = "D1:D29")
result = input %>%
mutate(rn = row_number()) %>%
group_by(
group_id = cumsum(ID != lag(ID, default = first(ID)) |
(Number - rn) != lag(Number - rn, default = first(Number - rn)))
) %>%
mutate(answer_expected = n()) %>%
ungroup() %>%
select(answer_expected)
all.equal(result$answer_expected, test$`Answer Expected`)
# TRUE