Generate the integer intervals corresponding to given numbers. If continuous numbers are given, then starting and ending numbers constitute a range. So 11, 12, 13, 14 is range 11-14.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 486
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Convert List to Integer Intervals with Power Query
Power Query solution 1 for Convert List to Integer Intervals, proposed by Omid Motamedisedeh:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
Source,
"Custom",
each
let
a = Text.Split([Problem], ","),
b = Table.FromColumns({List.Transform(a, Number.From)}, {"A"}),
c = Table.Group(
b,
{"A"},
{"B", each [A]},
0,
(s, t) => 1 - Number.From(List.ContainsAll(b[A], {s[A] .. t[A]}))
)[B],
d = List.Transform(
c,
each Text.Combine(List.Transform(List.Distinct({_{0}, List.Last(_)}), Text.From), "-")
)
in
Text.Combine(d, ", ")
)
in
SolPower Query solution 2 for Convert List to Integer Intervals, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[ Name = "data" ]}[Content],
Return = Table.AddColumn (
Source,
"Answer",
each [
S = Text.Split ( [Problem], ", " ),
T = { - hashtag#infinity } & List.Transform ( S, Number.From ) & { hashtag#infinity },
G = List.Generate (
() => [ a = 0, b = null, c = null ],
( f ) => f[a] < List.Count ( T ) - 1,
( f ) => [
a = f[a] + 1,
i = T{a},
chk1 = Number.From ( T{a - 1} + 1 = i ),
chk2 = Number.From ( T{a + 1} - 1 = i ),
s = chk1 + chk2 * 2,
t = Text.From ( i ),
b = { null, null, t, f[b] }{s},
c = { t, f[b] & "-" & t, null, null }{s}
],
( f ) => f[c]
),
R = Text.Combine ( G, ", " )
][R]
)
in
Return
Power Query solution 3 for Convert List to Integer Intervals, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
Source,
"Custom",
each
let
a = Text.Split([Problem], ","),
b = Table.FromColumns({List.Transform(a, Number.From)}, {"A"}),
c = Table.Group(
b,
{"A"},
{"B", each [A]},
0,
(s, t) =>
Number.From(t[A] - s[A] > List.PositionOf(b[A], t[A]) - List.PositionOf(b[A], s[A]))
)[B],
d = List.Transform(
c,
each Text.Combine(List.Transform(List.Distinct({_{0}, List.Last(_)}), Text.From), "-")
)
in
Text.Combine(d, ", ")
)
in
SolPower Query solution 4 for Convert List to Integer Intervals, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Sol = Table.AddColumn(Source, "Custom", each
let
a = Text.Split([Problem],","),
b = Table.FromColumns({List.Transform(a, Number.From)}, {"A"}),
c = Table.AddIndexColumn(b, "Idx", 0,1),
d = Table.Group(c, {"Idx"}, {"B", each [A]}, 0, (s,t)=> Number.From(c[A]{t[Idx]}-c[A]{s[Idx]}>t[Idx]-s[Idx]))[B],
e = List.Transform(d, each Text.Combine(List.Transform(List.Distinct({_{0}, List.Last(_)}), Text.From), "-"))
in Text.Combine(e, ", "))
in
Sol
Show translation
Show translation of this commentPower Query solution 5 for Convert List to Integer Intervals, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
Source,
"A",
each Text.Combine(
List.Transform(
Table.Group(
Table.Combine(
List.RemoveLastN(
let
a = Text.Split([Problem], ","),
b = List.Transform(a, Number.From),
c = List.Generate(
() => [x = 0, y = {b{0}}],
each [x] <= List.Count(b),
each [x = [x] + 1, y = if b{x} - List.Last([y]) = 1 then [y] & {b{x}} else {b{x}}],
each [y]
),
d = List.Transform(c, each List.Count(_)),
e = List.Transform(List.Zip({d, b}), (z) => Table.FromRows({z}, {"B", "C"}))
in
e
)
),
"B",
{"D", each [C]},
0,
(a, b) => Number.From(b = 1)
)[D],
each Text.Combine(List.Distinct({Text.From(_{0}), Text.From(List.Last(_))}), "-")
),
", "
)
)
in
SolPower Query solution 6 for Convert List to Integer Intervals, proposed by Mihai Radu O:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
sol = Table.AddColumn(
Source,
"Answer Expected",
each
let
a = List.Transform(Text.Split([Problem], ", "), Number.From),
b = Text.Combine(
List.Transform(
{0 .. List.Count(a) - 1},
(x) =>
if x = 0 then
Text.From(a{0})
else if a{x} = a{x - 1} + 1 then
"-" & Text.From(a{x})
else
"," & Text.From(a{x})
)
),
c = Text.Split(b, ","),
d = List.Transform(
c,
(x) =>
let
_a = Text.BeforeDelimiter(x, "-"),
nr = List.Count(Text.PositionOf(x, "-", Occurrence.All)),
_b = Text.AfterDelimiter(x, "-", nr - 1),
_c = if nr = 0 then x else _a & "-" & _b
in
_c
)
in
Text.Combine(d, ", ")
)[[Answer Expected]]
in
solPower Query solution 7 for Convert List to Integer Intervals, proposed by Venkata Rajesh:
let
Source = Data,
Split = Table.AddColumn(Source, "List", each Text.Split([Problem], ", ")),
Expand = Table.ExpandListColumn(Split, "List"),
CType = Table.TransformColumnTypes(Expand, {{"List", Int64.Type}}),
Index = Table.AddIndexColumn(CType, "Index", 0, 1, Int64.Type),
Max = Table.AddColumn(
Index,
"Max",
each try
if [List] + 1 = Index{[Index] + 1}[List] and [Problem] = Index{[Index] + 1}[Problem] then
null
else
[List]
otherwise
[List]
),
FilledUp = Table.FillUp(Max, {"Max"}),
Grouped1 = Table.Group(
FilledUp,
{"Problem", "Max"},
{{"Min", each List.Min([List]), type number}}
),
Check = Table.AddColumn(
Grouped1,
"Range",
each if [Max] = [Min] then Text.From([Min]) else Text.From([Min]) & "-" & Text.From([Max]),
type text
),
Grouped2 = Table.Group(Check, {"Problem"}, {{"Range", each Text.Combine([Range], ", ")}})
in
Grouped2Power Query solution 8 for Convert List to Integer Intervals, proposed by Arnaud Duvernois:
let
Source = Excel.CurrentWorkbook(){[Name = "Tableau1"]}[Content],
Split = Table.AddColumn(
Source,
"Split",
each List.Transform(Text.Split([Problem], ", "), each Number.From(_))
),
GroupLocal = Table.TransformColumns(
Split,
{
"Split",
each Text.Combine(
Table.Group(
Table.FromColumns({_}),
{"Column1"},
{
{
"tbl",
each
let
a = Text.From(List.Min(_[Column1])),
b = Text.From(List.Max(_[Column1]))
in
if a = b then a else a & "-" & b
}
},
GroupKind.Local,
(s, c) =>
Number.From(
List.PositionOf({s[Column1] .. c[Column1]}, c[Column1])
<> List.PositionOf(List.Skip(_, (x) => x <> s[Column1]), c[Column1])
)
)[tbl],
", "
)
}
)
in
GroupLocalSolving the challenge of Convert List to Integer Intervals with Excel
Excel solution 1 for Convert List to Integer Intervals, proposed by Bo Rydobon 🇹🇭:
=MAP(
A2:A8,
LAMBDA(
a,
LET(
b,
-TEXTSPLIT(
a,
","
),
MID(
CONCAT(
CHOOSE(
MMULT(
{1,
3},
-ISNA(
XMATCH(
b+{-1;1},
b
)
)
)/2+3,
", "&-b,
b,
""
)
),
3,
99
)
)
)
)Excel solution 2 for Convert List to Integer Intervals, proposed by John V.:
=MAP(
A2:A8,
LAMBDA(
x,
LET(
n,
--TEXTSPLIT(
x,
,
","
),
c,
--ISNA(
XMATCH(
n-1,
n
)
),
MID(
CONCAT(
REPT(
", "&n,
c
)&REPT(
-n,
cExcel solution 3 for Convert List to Integer Intervals, proposed by محمد حلمي:
=MAP(A2:A8,
LAMBDA(a,
LET(
w,
--TEXTSPLIT(
a,
,
","
),
r,
SCAN(,
-VSTACK(
0,
DROP(
w,
1
)=DROP(
w,
-1
)+1
),
LAMBDA(a,
v,
(v=0)+a)),
e,
UNIQUE(
r
),
i,
XLOOKUP(
e,
r,
w
),
j,
XLOOKUP(
e,
r,
w,
,
,
-1
),
ARRAYTOTEXT(
IF(
i=j,
i,
i&-j
)
))))Excel solution 4 for Convert List to Integer Intervals, proposed by Kris Jaganah:
=MAP(A2:A8,
LAMBDA(v,
LET(a,
--TEXTSPLIT(
v,
,
", "
),
b,
MIN(
a
),
c,
MAX(
a
),
d,
SEQUENCE(
c-b+1,
,
b
),
e,
XLOOKUP(
d,
a,
a,
0
),
f,
SCAN(0,
e,
LAMBDA(x,
y,
IF((y>0)*(x<>0),
x,
y))),
g,
UNIQUE(TOCOL(f/(f>0),
3)),
h,
XLOOKUP(
g,
f,
e,
,
,
-1
),
ARRAYTOTEXT(
IF(
g<>h,
g&"-"&h,
g
)
))))Excel solution 5 for Convert List to Integer Intervals, proposed by Julian Poeltl:
=MAP(A2:A8,LAMBDA(P,LET(SP,TEXTSPLIT(P,", "),T,IFERROR(SP+1=DROP(SP,,1)*1,FALSE),C,TEXTJOIN(IF(T,"-",", "),,SP),M,TEXTSPLIT(C,"-",", "),Ma,BYROW(M,LAMBDA(A,MAX(IFERROR(--A,0)))),Mi,BYROW(M,LAMBDA(A,MIN(IFERROR(--A,MAX(--SP))))),TEXTJOIN(", ",,IF(Ma=Mi,Ma,Mi&"-"&Ma)))))Excel solution 6 for Convert List to Integer Intervals, proposed by Timothée BLIOT:
=MAP(
A2:A8,
LAMBDA(
z,
LET(
A,
--TEXTSPLIT(
z,
,
", "
),
F,
LAMBDA(
n,
m,
--ISNUMBER(
XMATCH(
n,
m
)
)
),
B,
MAP(
A,
LAMBDA(
x,
F(
x-1,
A
)+F(
x+1,
A
)
)
),
C,
VSTACK(
FILTER(
A,
B=0,
""
),
BYROW(
WRAPROWS(
FILTER(
A,
B=1,
""
),
2
),
LAMBDA(
x,
TEXTJOIN(
"-",
,
x
)
)
)
),
TEXTJOIN(
", ",
,
SORTBY(
C,
--REGEXEXTRACT(
C,
"d+",
0
)
)
)
)
)
)Excel solution 7 for Convert List to Integer Intervals, proposed by Oscar Mendez Roca Farell:
=MAP(A2:A8,
LAMBDA(a,
LET(F,
LAMBDA(
j,
k,
TEXTSPLIT(
j ,
,
k
)
),
t,
F(
a,
", "
),
ARRAYTOTEXT(MAP(F(REDUCE(a,
IFERROR(TOCOL(t/(DROP(
t,
1
)-DROP(
t,
-1
)>1),
2),
"*"),
LAMBDA(
i,
x,
SUBSTITUTE(
i,
x&", ",
x&"|"
)
)),
"|"),
LAMBDA(
b,
LET(
m,
F(
b,
", "
),
IF(
COUNT(
-m
)>1,
@m&"-"&TAKE(
m,
-1
),
m
)
)
))))))Excel solution 8 for Convert List to Integer Intervals, proposed by Duy Tùng:
=MAP(A2:A8,LAMBDA(x,LET(a,TEXTSPLIT(x,,", ")*1,ARRAYTOTEXT(DROP(GROUPBY(SCAN(0,a-VSTACK(0,DROP(a,-1))<>1,SUM),a,LAMBDA(x,IF(MAX(x)=@x,@x,MIN(x)&-MAX(x))),,0),,1)))))Excel solution 9 for Convert List to Integer Intervals, proposed by Sunny Baggu:
=MAP(
A2:A8,
LAMBDA(p,
LET(
_ts, TEXTSPLIT(p, , ", ") + 0,
_s, SCAN(
1,
VSTACK(0, --NOT(DROP(_ts, 1) - DROP(_ts, -1) = 1)),
LAMBDA(a, v, IF(v = 0, a, a + 1))
),
ARRAYTOTEXT(
MAP(
UNIQUE(_s),
LAMBDA(x, TEXTJOIN("-", , UNIQUE(TAKE(FILTER(_ts, _s = x), {1; -1}))))
)
)
)
)
)Excel solution 10 for Convert List to Integer Intervals, proposed by LEONARD OCHEA 🇷🇴:
=MAP(A2:A8,LAMBDA(a,LET(F,TEXTSPLIT,G,TEXTJOIN,E,IFERROR,d,F(a,", "),p,IF(E(DROP(d,,1)-d,1)=1,",","-"),G(", ",,BYROW(E(--F(CONCAT(d&p),",","-"),""),LAMBDA(x,G("-",,UNIQUE(VSTACK(MIN(x),MAX(x))))))))))Excel solution 11 for Convert List to Integer Intervals, proposed by Anshu Bantra:
=MAP(
A2:A8,
LAMBDA(
data_,
LET(
split_,
--TEXTSPLIT(
data_,
,
","
),
min_,
MIN(
split_
),
max_,
MAX(
split_
),
seq_,
SEQUENCE(
max_+1-min_,
,
min_
),
match_,
XMATCH(
seq_,
split_
),
vals_,
IFERROR(
INDEX(
split_,
match_
),
"~"
),
sets_,
IFERROR(
--TEXTSPLIT(
TEXTJOIN(
",",
,
vals_
),
",",
"~"
),
""
),
final_,
TEXTJOIN(
", ",
,
BYROW(
sets_,
LAMBDA(
r,
IF(
MIN(
r
)=MAX(
r
),
MIN(
r
),
CONCAT(
MIN(
r
),
"-",
MAX(
r
)
)
)
)
)
),
SUBSTITUTE(
final_,
"0, ",
""
)
)
)
)Excel solution 12 for Convert List to Integer Intervals, proposed by Bilal Mahmoud kh.:
=MAP(A2:A8,
LAMBDA(r,
TEXTJOIN(",",
,
LET(a,
TEXTSPLIT(TEXTJOIN(" ",
,
REDUCE(,
--TEXTSPLIT(
r,
","
),
LAMBDA(x,
y,
IF((y-MAX(
ABS(
x
)
))>1,
VSTACK(
x,
-1*y
),
VSTACK(
x,
y
))))),
,
"-"),
b,
MAP(
a,
LAMBDA(
v,
LET(
n,
--TEXTSPLIT(
v,
" ",
,
TRUE
),
IF(
MAX(
n
)=MIN(
n
),
n,
MIN(
n
)&"-"&MAX(
n
)
)
)
)
),
b))))Excel solution 13 for Convert List to Integer Intervals, proposed by JvdV -:
=REDUCE(
A2:A8,
SEQUENCE(
9999
),
LAMBDA(
x,
y,
IFNA(
REGEXREPLACE(
x,
"(d+-)?("&y&"), ("&y+1&")b",
"${1:-$2-}$3"
),
x
)
)
)
Preemptively added the IFNA()Excel solution 14 for Convert List to Integer Intervals, proposed by JvdV -:
=MAP(
A2:A8,
LAMBDA(
s,
LET(
x,
TEXTSPLIT(
s,
,
", "
),
y,
TEXTJOIN(
", ",
,
IFNA(
XLOOKUP(
x-1,
x,
-x-1
),
x
)
),
IFNA(
REGEXREPLACE(
y,
"(, (-d+))+",
"$2"
),
s
)
)
)
)Excel solution 15 for Convert List to Integer Intervals, proposed by Sandeep Marwal:
=MAP(
A1:A7,
LAMBDA(
a,
LET(
b,
VSTACK(
--TRIM(
TEXTSPLIT(
a,
,
","
)
),
"-"
),
c,
VSTACK(
"-",
DROP(
b+1,
-1
)
),
d,
SEQUENCE(
ROWS(
b
)
),
e,
b=c,
f,
FILTER(
HSTACK(
d,
b
),
e=FALSE
),
ff,
TAKE(
f,
,
1
),
g,
BYROW(
f,
LAMBDA(
a,
TAKE(
a,
,
-1
)+IFERROR(
INDEX(
ff,
1+MATCH(
TAKE(
a,
,
1
),
ff
)
),
1
)-TAKE(
a,
,
1
)-1
)
),
h,
DROP(
f,
-1,
1
),
i,
DROP(
g,
-1
),
j,
TEXTJOIN(
",",
,
IF(
h=i,
h,
h&"-"&i
)
),
j
)
)
)Excel solution 16 for Convert List to Integer Intervals, proposed by El Badlis Mohd Marzudin:
=MAP(
A2:A8,
LAMBDA(
y,
LET(
b,
TEXTSPLIT(
y,
,
", "
)+0,
c,
SEQUENCE(
MAX(
b
)-MIN(
b
)+1,
,
MIN(
b
)
),
d,
IFNA(
INDEX(
b,
XMATCH(
c,
b
)
),
"x"
),
e,
IFERROR(
TEXTSPLIT(
TEXTJOIN(
",",
,
d
),
",",
"x"
)+0,
""
),
f,
BYROW(
e,
LAMBDA(
x,
IF(
COUNT(
x
)=1,
CONCAT(
x
),
MIN(
x
)&-MAX(
x
)
)
)
),
TEXTJOIN(
", ",
,
FILTER(
f,
f<>"00"
)
)
)
)
)Excel solution 17 for Convert List to Integer Intervals, proposed by Diarmuid Early:
=MAP(
A2:A8,
LAMBDA(
input,
@REDUCE(
{"",
0,
-1},
TAKE(
TEXTSPLIT(
input,
", "
)*1,
1
),
LAMBDA(
a,
v,
LET(
prevStr,
@a,
run,
INDEX(
a,
2
),
currMax,
INDEX(
a,
3
),
IF(
v>currMax+1,
HSTACK(
TEXTJOIN(
", ",
,
prevStr,
v
),
0,
v
),
HSTACK(
IF(
run,
TEXTBEFORE(
prevStr,
"-",
-1
)&"-"&v,
prevStr&"-"&v
),
1,
v
)
)
)
)
)
)
)
The logic:
* For simplicity,
I work with a triple: the first value is the current string,
the second is 1 if the end of the current string is a run (e.g. 3-5) and 0 if it's a singleton,
and the third is the current max. Initial values are "",
0,
and -1. Then I update the triple based on the latest number.
* Given a string and a new number,
first I check if that number is > previous max + 1.
--> If it is,
I just add it to the end of the current string,
run = 0,
max = new number
--> if it's not,
and we're on a run,
drop the previous end of the run and replace with current number (e.g. 3-5 --> 3-6); otherwise,
append "-" and the new number (e.g. 3 --> 3-4)Excel solution 18 for Convert List to Integer Intervals, proposed by Burhan Cesur:
=MAP(
A2:A8,
LAMBDA(
x,
LET(
y,
x,
a,
XMATCH(
-TEXTSPLIT(
y,
","
)+{1,
-1},
-TEXTSPLIT(
y,
","
)
),
c,
BYCOL(
--ISNA(
a
),
SUM
),
SUBSTITUTE(
MID(
CONCAT(
UNIQUE(
IF(
c>0,
","&TEXTSPLIT(
y,
","
),
"-"
),
1
)
),
2,
99
),
"-,",
" -"
)
)
)
)Excel solution 19 for Convert List to Integer Intervals, proposed by Peter Mulholland, CFA:
=MAP(A2:A8, LAMBDA(input,
LET(orig, TEXTSPLIT(input,", "),
shifted, HSTACK(TAKE(orig,,1),DROP(orig,,-1)),
diff, orig-shifted,
diff_bck, HSTACK(DROP(diff,,1),"last"),
list_map, MAP(orig, diff, diff_bck, LAMBDA(a,b,c,
SWITCH(c,
"last", TAKE(orig,,-1),
1, IF(b=1,"drop",a&"-"),
a&", "))),
output, CONCAT(FILTER(list_map, list_map<>"drop")),
output)))Solving the challenge of Convert List to Integer Intervals with Python
Python solution 1 for Convert List to Integer Intervals, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "486 Create Integer Intervals.xlsx"
input = pd.read_excel(path, usecols="A")
test = pd.read_excel(path, usecols="B")
def group_consecutive(number_string):
numbers = [int(num) for num in number_string.split(",")]
numbers.sort()
ranges = []
start = end = numbers[0]
for i in range(1, len(numbers)):
if numbers[i] - numbers[i-1] == 1:
end = numbers[i]
else:
if start == end:
ranges.append(str(start))
else:
ranges.append(f"{start}-{end}")
start = end = numbers[i]
if start == end:
ranges.append(str(start))
else:
ranges.append(f"{start}-{end}")
return ", ".join(ranges)
result = input.copy()
result["Answer Expected"] = result["Problem"].map(group_consecutive)
print(result["Answer Expected"].equals(test["Answer Expected"])) # True
Solving the challenge of Convert List to Integer Intervals with Python in Excel
Python in Excel solution 1 for Convert List to Integer Intervals, proposed by Abdallah Ally:
import pandas as pd
def integer_interval(text):
numbers = list(map(int, text.split(', ')))
values = []
start = numbers[0]
for i in range(1, len(numbers)):
if numbers[i] != numbers[i - 1] + 1:
if start == numbers[i - 1]:
values.append(f'{start}')
else:
values.append(f'{start}-{numbers[i - 1]}')
start = numbers[i]
if start == numbers[-1]:
values.append(f'{start}')
else:
values.append(f'{start}-{numbers[-1]}')
return ', '.join(values)
file_path = 'Excel_Challenge_486 - Create Integer Intervals.xlsx'
df = pd.read_excel(file_path)
# Perform data wrangling
df['My Answer'] = df['Problem'].map(integer_interval)
df['Check'] = df['Answer Expected'] == df['My Answer']
df
Solving the challenge of Convert List to Integer Intervals with R
R solution 1 for Convert List to Integer Intervals, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/486 Create Integer Intervals.xlsx"
input = read_excel(path, range = "A1:A8")
test = read_excel(path, range = "B1:B8")
group_consecutive = function(number_string) {
numbers <- str_split(number_string, ",") %>%
unlist() %>%
as.numeric()
tibble(numbers = sort(numbers)) %>%
mutate(group = cumsum(c(TRUE, diff(numbers) != 1))) %>%
summarise(range = if_else(n() > 1,
paste0(min(numbers), "-", max(numbers)),
as.character(numbers[1])), .by = group) %>%
pull(range) %>%
paste(collapse = ", ")
}
result = input %>%
mutate(`Answer Expected` = map_chr(Problem, group_consecutive))
identical(result$`Answer Expected`, test$`Answer Expected`)
#> [1] TRUE
&&