Generate the integers corresponding to intervals. If range is x-y, then all integers between x & y (both included) need to be generated. if a single number appears, it will appear as it is. The answer should have unique numbers and sorted in ascending order. Ex. 5-8, 12 = 5, 6, 7, 8, 12
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 441
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Generate Integers From Ranges with Power Query
Power Query solution 1 for Generate Integers From Ranges, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Return = Table.AddColumn(
Source,
"Answer",
each [
R = Text.Replace([Problem], "-", ".."),
E = Expression.Evaluate("{" & R & "}"),
L = List.Sort(List.Distinct(E)),
T = List.Transform(L, Text.From),
C = Text.Combine(T, ", ")
][C]
)
in
ReturnPower Query solution 2 for Generate Integers From Ranges, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
Source,
"Answer",
each
let
a = Text.Split([Problem], ", "),
b = List.Transform(
a,
each
if Text.Contains(_, "-") then
{
Number.From(Text.BeforeDelimiter(_, "-")) .. Number.From(
Text.AfterDelimiter(_, "-")
)
}
else
{Number.From(_)}
),
c = List.Distinct(List.Sort(List.Combine(b)))
in
Text.Combine(List.Transform(c, each Text.From(_)), ", ")
)[[Answer]]
in
SolPower Query solution 3 for Generate Integers From Ranges, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
res = List.Transform(
Fonte[Problem],
each Text.Combine(
List.Transform(
List.Sort(
List.Distinct(
List.TransformMany(
Text.Split(Text.Replace(_, "-", ".."), ", "),
(x) => Expression.Evaluate("{" & x & "}"),
(a, b) => b
)
),
0
),
Text.From
),
","
)
)
in
resPower Query solution 4 for Generate Integers From Ranges, proposed by Alexis Olson:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Result = Table.AddColumn(
Source,
"Answer",
each [
CommaSplit = Text.Split([Problem], ", "),
HypenSplit = List.Transform(CommaSplit, each Text.Split(_, "-")),
ToRanges = List.Transform(
HypenSplit,
each {Number.From(List.First(_)) .. Number.From(List.Last(_))}
),
Combine = Text.Combine(List.Transform(List.Sort(List.Union(ToRanges)), Number.ToText), ", ")
][Combine]
)
in
ResultPower Query solution 5 for Generate Integers From Ranges, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Fx = (x) =>
let
L = List.Transform,
a = Text.Split(x, ", "),
b = L(a, each if Text.Contains(_, "-") then Text.Split(_, "-") else _),
c = List.Select(List.Combine(L(b, each {_})), each _ is text),
d = List.Difference(b, c),
e = L(L(d, each _{0}), Number.From),
f = L(L(d, each _{1}), Number.From),
g = List.RemoveNulls(List.Combine(L({0, 1}, each try {e{_} .. f{_}} otherwise {null}))),
h = Text.Combine(L(List.Sort(List.Distinct(L(c & g, Number.From))), Text.From), ", ")
in
h,
Sol = Table.AddColumn(S, "Answer Expected", each Fx([Problem]))
in
SolPower Query solution 6 for Generate Integers From Ranges, proposed by Rafael González B.:
let
Source = Excel.CurrentWorkbook(){0}[Content],
TTC = Table.TransformColumns(Source, {"Problem", each
let
Tx = Text.Split(_, ", "),
NF = Number.From,
LT = List.Transform(Tx, each if Text.Contains(_, "-")
then {NF(Text.BeforeDelimiter(_,"-"))..NF(Text.AfterDelimiter(_,"-"))}
else {NF(_)}),
F = List.Sort(List.Distinct(List.Combine(LT))),
TF = Text.Combine(List.Transform(F, each Text.From(_)), ", ")
in
TF})
in
TTC
🧙🏻♂️🧙🏻♂️🧙🏻♂️
Power Query solution 7 for Generate Integers From Ranges, proposed by Glyn Willis:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Added Custom" = Table.AddColumn(
Source,
"Custom",
each Text.Combine(
List.Transform(
List.Sort(
List.Union(
List.Transform(
Text.Split([Problem], ", "),
(ol) =>
let
li = Text.Split(ol, "-")
in
{Number.From(li{0}) .. Number.From(li{1}? ?? li{0})}
)
)
),
(ol2) => Text.From(ol2)
),
", "
)
)
in
#"Added Custom"Solving the challenge of Generate Integers From Ranges with Excel
Excel solution 1 for Generate Integers From Ranges, proposed by Bo Rydobon 🇹🇭:
=MAP(A2:A7,
LAMBDA(a,
LET(l,
TRANSPOSE(
--TEXTSPLIT(
a,
"-",
","
)
),
f,
TAKE(
l,
1
),
s,
SEQUENCE(
5^5
),
ARRAYTOTEXT(UNIQUE(TOCOL(s/(s>=f)/(s<=IFNA(
DROP(
l,
1
),
f
)),
3))))))
=MAP(A2:A7,
LAMBDA(a,
LET(l,
--TEXTSPLIT(
a,
"-",
","
),
f,
TAKE(
l,
,
1
),
s,
SEQUENCE(
,
5^5
),
ARRAYTOTEXT(FILTER(s,
BYCOL((s>=f)*(s<=IFNA(
DROP(
l,
,
1
),
f
)),
OR))))))Excel solution 2 for Generate Integers From Ranges, proposed by Rick Rothstein:
=MAP(
A2:A7,
LAMBDA(
a,
TEXTJOIN(
", ",
,
SORT(
DROP(
UNIQUE(
REDUCE(
"",
TEXTSPLIT(
a,
", "
),
LAMBDA(
a,
x,
LET(
t,
TEXTBEFORE(
x&"-",
"-"
),
VSTACK(
a,
SEQUENCE(
TEXTAFTER(
"-"&x,
"-",
-1
)-t+1,
,
t
)
)
)
)
)
),
1
)
)
)
)
)Excel solution 3 for Generate Integers From Ranges, proposed by John V.:
=MAP(
A2:A7,
LAMBDA(
x,
TEXTJOIN(
", ",
,
SORT(
UNIQUE(
REDUCE(
"",
TEXTSPLIT(
x,
","
),
LAMBDA(
a,
v,
LET(
i,
--TEXTSPLIT(
v,
"-"
),
VSTACK(
a,
SEQUENCE(
1+MAX(
i
)-@i,
,
@i
)
)
)
)
)
)
)
)
)
)Excel solution 4 for Generate Integers From Ranges, proposed by محمد حلمي:
=MAP(
A2:A7,
LAMBDA(
a,
TEXTJOIN(
", ",
,
SORT(
UNIQUE(
REDUCE(
"",
TEXTSPLIT(
a,
","
),
LAMBDA(
A,
v,
LET(
d,
--TEXTSPLIT(
v,
"-"
),
VSTACK(
A,
SEQUENCE(
MAX(
d
)-@d+1,
,
@d
)
)
)
)
)
)
)
)
)
)Excel solution 5 for Generate Integers From Ranges, proposed by محمد حلمي:
=MAP(A2:A7,
LAMBDA(a,
LET(l,
--TEXTSPLIT(
a,
"-",
","
),
f,
TAKE(
l,
,
1
),
s,
SEQUENCE(
,
5^5
),
ARRAYTOTEXT(UNIQUE(TOCOL(s/(s>=f)/(s<=IFNA(
DROP(
l,
,
1
),
f
)),
3,
1))))))Excel solution 6 for Generate Integers From Ranges, proposed by 🇰🇷 Taeyong Shin:
=MAP(
SUBSTITUTE(
A2:A7,
"-",
":"
),
LAMBDA(
x,
LET(
s,
TEXTSPLIT(
x,
", "
),
TEXTJOIN(
", ",
,
SORT(
UNIQUE(
REDUCE(
"",
TEXTBEFORE(
s&":"&s,
":",
2,
,
1
),
LAMBDA(
a,
v,
VSTACK(
a,
ROW(
INDIRECT(
v
)
)
)
)
)
)
)
)
)
)
)Excel solution 7 for Generate Integers From Ranges, proposed by Kris Jaganah:
=MAP(
A2:A7,
LAMBDA(
x,
LET(
a,
TEXTSPLIT(
x,
,
", "
),
ARRAYTOTEXT(
SORT(
UNIQUE(
TOCOL(
--REDUCE(
FILTER(
a,
ISERR(
FIND(
"-",
a
)
),
""
),
FILTER(
a,
IFERROR(
FIND(
"-",
a
),
0
)
),
LAMBDA(
x,
y,
VSTACK(
x,
SEQUENCE(
TEXTAFTER(
y,
"-"
)-TEXTBEFORE(
y,
"-"
)+1,
,
TEXTBEFORE(
y,
"-"
)
)
)
)
),
3
)
)
)
)
)
)
)Excel solution 8 for Generate Integers From Ranges, proposed by Julian Poeltl:
=MAP(
A2:A7,
LAMBDA(
T,
LET(
SP,
TEXTSPLIT(
T,
", "
),
CA,
IF(
ISNUMBER(
SEARCH(
"-",
SP
)
),
MAP(
SP,
LAMBDA(
A,
TEXTJOIN(
",",
,
SEQUENCE(
TEXTAFTER(
A,
"-"
)-TEXTBEFORE(
A,
"-"
)+1,
,
TEXTBEFORE(
A,
"-"
)
)
)
)
),
SP
),
TEXTJOIN(
", ",
,
SORT(
UNIQUE(
TEXTSPLIT(
TEXTJOIN(
",",
,
CA
),
","
),
TRUE
)*1,
,
,
& TRUE
)
)
)
)
)Excel solution 9 for Generate Integers From Ranges, proposed by Timothée BLIOT:
=LET(
A,
TEXTSPLIT(
A2,
","
),
ARRAYTOTEXT(
SORT(
UNIQUE(
--TRIM(
TEXTSPLIT(
CONCAT(
MAP(
A,
LAMBDA(
x,
ARRAYTOTEXT(
IF(
ISNUMBER(
FIND(
"-",
x
)
),
LET(
B,
--TEXTBEFORE(
x,
"-"
),
C,
--TEXTAFTER(
x,
"-"
),
SEQUENCE(
C-B+1,
,
B
)
),
x
)
)
)
)&","
),
,
",",
1
)
)
)
)
)
)Excel solution 10 for Generate Integers From Ranges, proposed by Hussein SATOUR:
=MAP(
A2:A7,
LAMBDA(
z,
ARRAYTOTEXT(
UNIQUE(
TOCOL(
SORT(
--REDUCE(
"",
TEXTSPLIT(
z,
","
),
LAMBDA(
x,
y,
LET(
a,
TEXTBEFORE(
y,
"-"
),
VSTACK(
x,
IFNA(
SEQUENCE(
TEXTAFTER(
y,
"-"
)-a+1,
,
a
),
y
)
)
)
)
)
),
2
)
)
)
)
)Excel solution 11 for Generate Integers From Ranges, proposed by Oscar Mendez Roca Farell:
=MAP(A2:A7,
LAMBDA(a,
LET(t,
--TEXTSPLIT(
a,
"-",
","
),
F,
LAMBDA(
i,
j,
DROP(
i,
,
-j
)
),
m,
IFNA(
t,
F(
t,
1
)
),
s,
SEQUENCE(
,
9^4
),
ARRAYTOTEXT(SORT(UNIQUE(TOCOL(s/(s<=F(
m,
-1
)/(s>=F(
m,
1
))),
2)))))))Excel solution 12 for Generate Integers From Ranges, proposed by Duy Tùng:
=MAP(
A2:A7,
LAMBDA(
v,
LET(
a,
TEXTSPLIT(
v,
,
", "
),
ARRAYTOTEXT(
SORT(
UNIQUE(
DROP(
REDUCE(
0,
SUBSTITUTE(
a,
"-",
":"
),
LAMBDA(
x,
y,
VSTACK(
x,
IFERROR(
ROW(
INDIRECT(
y
)
),
--y
)
)
)
),
1
)
)
)
)
)
)
)Excel solution 13 for Generate Integers From Ranges, proposed by Sunny Baggu:
=MAP(
A2:A7,
LAMBDA(
x,
LET(
_ts,
TEXTSPLIT(
x,
,
", "
),
_ta,
IFNA(
TEXTAFTER(
_ts,
"-"
),
0
),
_tb,
IFNA(
TEXTBEFORE(
_ts,
"-"
),
_ts
),
ARRAYTOTEXT(
SORT(
UNIQUE(
--TOCOL(
DROP(
REDUCE(
"",
SEQUENCE(
ROWS(
_ts
)
),
LAMBDA(
a,
v,
VSTACK(
a,
IFERROR(
INDEX(
_tb,
v,
1
) + SEQUENCE(
,
1 + INDEX(
_ta,
v,
1
) - INDEX(
_tb,
v,
1
),
) - 1,
INDEX(
_tb,
v,
1
)
)
)
)
),
1
),
3
)
)
)
)
)
)
)Excel solution 14 for Generate Integers From Ranges, proposed by Sunny Baggu:
=MAP(
A2:A7,
LAMBDA(
x,
LET(
_ts,
TEXTSPLIT(
x,
"-",
", ",
,
,
0
),
s,
BYROW(
_ts,
LAMBDA(
x,
MAX(
0,
SUM(
x * {-1,
1}
) + 1
)
)
),
ARRAYTOTEXT(
SORT(
UNIQUE(
--TOCOL(
MAKEARRAY(
ROWS(
_ts
),
MAX(
s
),
LAMBDA(
r,
c,
INDEX(
IFERROR(
INDEX(
_ts,
r,
1
) + SEQUENCE(
,
INDEX(
s,
r,
1
),
0
),
INDEX(
_ts,
r,
1
)
),
,
c
)
)
),
3
)
)
)
)
)
)
)Excel solution 15 for Generate Integers From Ranges, proposed by Abdallah Ally:
=MAP(
A2:A7,
LAMBDA(
s,
LET(
a,
TEXTSPLIT(
s,
", "
),
b,
DROP(
REDUCE(
"",
a,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
c,
--TEXTSPLIT(
y,
"-"
),
SEQUENCE(
SUM(
TAKE(
c,
1,
-1
)-TAKE(
c,
,
1
)+1
),
,
SUM(
TAKE(
c,
,
1
)
)
)
)
)
)
),
1
),
TEXTJOIN(
", ",
,
UNIQUE(
SORT(
b
)
)
)
)
)
)Excel solution 16 for Generate Integers From Ranges, proposed by 🇵🇪 Ned Navarrete C.:
=MAP(
A2:A7,
LAMBDA(
r,
TEXTJOIN(
", ",
1,
SORT(
UNIQUE(
REDUCE(
"",
TEXTSPLIT(
r,
,
", "
),
LAMBDA(
c,
v,
LET(
n,
TEXTSPLIT(
v,
"-"
),
VSTACK(
c,
@n+SEQUENCE(
SUM(
n*{-1,
1}
)+1
)-1
)
)
)
)
)
)
)
)
)Excel solution 17 for Generate Integers From Ranges, proposed by Andy Heybruch:
=MAP(
A2:A7,
LAMBDA(
_p,
TEXTJOIN(
", ",
,
UNIQUE(
SORT(
--TEXTSPLIT(
&
REDUCE(
"",
TEXTSPLIT(
_p,
,
","
),
LAMBDA(
a,
v,
TEXTJOIN(
",",
,
a,
TEXTJOIN(
",",
,
LET(
_a,
--TEXTBEFORE(
v,
"-",
,
,
,
--v
),
_b,
--TEXTAFTER(
v,
"-",
,
,
,
--v
),
SEQUENCE(
_b-_a+1,
,
_a
)
)
)
)
)
),
,
","
)
)
)
)
)
)Excel solution 18 for Generate Integers From Ranges, proposed by Anup Kumar:
=BYROW(
A2:A7,
LAMBDA(
p,
LET(
txt,
p,
ds,
"-",
spt,
TEXTSPLIT(
txt,
,
", "
),
rng,
FILTER(
spt,
ISNUMBER(
SEARCH(
ds,
spt
)
)
),
nos,
FILTER(
spt,
NOT(
ISNUMBER(
SEARCH(
ds,
spt
)
)
)
),
exp,
DROP(
REDUCE(
"",
rng,
LAMBDA(
x,
y,
VSTACK(
x,
SEQUENCE(
1*TEXTAFTER(
y,
ds
)-1*TEXTBEFORE(
y,
ds
)+1,
,
TEXTBEFORE(
y,
ds
)
)
)
)
),
1
),
ARRAYTOTEXT(
SORT(
UNIQUE(
VSTACK(
IFERROR(
--nos,
exp
),
IFERROR(
exp,
--nos
)
)
)
)
)
)
)
)Excel solution 19 for Generate Integers From Ranges, proposed by LUIS FLORENTINO COUTO CORTEGOSO:
=MAP(
A2:A7,
LAMBDA(
c,
LET(
x,
SUBSTITUTE(
"A"&TEXTSPLIT(
c,
", "
),
"-",
":A"
),
TEXTJOIN(
", ",
,
SORT(
UNIQUE(
REDUCE(
"",
x,
LAMBDA(
a,
y,
VSTACK(
a,
ROW(
INDIRECT(
y
)
)
)
)
)
)
)
)
)
)
)Excel solution 20 for Generate Integers From Ranges, proposed by Anil Kumar Goyal:
= Problem) %>%
separate_rows(
Answer,
sep = ", "
) %>%
reframe(
Answer = unlist(
map(
Answer,
~{
if(
str_detect(
.x,
"-"
)
){
unlist(
str_split(
.x,
"-"
)
) %>%
as.integer() %>%
{.[1]:.[2]}
} else {
as.integer(
.x
)
}
}
)
),
.by = Problem
) %>%
distinct() %>%
summarise(
Answer = str_c(
sort(
Answer
),
collapse = ", "
),
.by = Problem
)Excel solution 21 for Generate Integers From Ranges, proposed by Tyler Cameron:
=MAP(
A2:A7,
LAMBDA(
t,
ARRAYTOTEXT(
SORT(
UNIQUE(
TOCOL(
DROP(
REDUCE(
"",
TEXTSPLIT(
t,
,
", "
),
LAMBDA(
x,
y,
LET(
a,
--TEXTSPLIT(
y,
"-"
),
b,
MAX(
a
),
c,
MIN(
a
),
VSTACK(
x,
IF(
COUNTA(
a
)>1,
TOROW(
SEQUENCE(
b-c+1,
,
c
)
),
a
)
)
)
)
),
1
),
3
)
)
)
)
)
)Excel solution 22 for Generate Integers From Ranges, proposed by Olasunkanmi Babatope:
=MAP(
A2:A7,
LAMBDA(
dText,
LET(
commaSplit,
IFERROR(
SUBSTITUTE(
SUBSTITUTE(
dText,
",",
"-"
),
" ",
""
),
""
),
spaceSplit,
IFERROR(
TEXTSPLIT(
commaSplit,
"-"
),
""
),
list,
BYCOL(
spaceSplit,
LAMBDA(
ss,
VALUE(
ss
)
)
),
myRange,
MAX(
list
)-MIN(
list
),
numSeries,
SEQUENCE(
1,
myRange+1,
MIN(
list
),
1
),
Textjoin(
",",
numSeries,
True
)
)
)
)Solving the challenge of Generate Integers From Ranges with Python
Python solution 1 for Generate Integers From Ranges, proposed by Konrad Gryczan, PhD:
import pandas as pd
import re
input = pd.read_excel("441 Integer Intervals.xlsx", usecols="A", nrows=7)
test = pd.read_excel("441 Integer Intervals.xlsx", usecols="B", nrows=7)
result = input.copy()
result['Problem'] = result['Problem'].str.split(", ")
result['row_number'] = result.index
result = result.explode('Problem')
result['Problem'] = result['Problem'].apply(lambda x: list(range(int(x.split('-')[0]), int(x.split('-')[1])+1)) if '-' in x else [int(x)])
result = result.explode('Problem')
result = result.groupby(result.index).agg({'Problem': lambda x: ', '.join(map(str, sorted(set(x))))}).reset_index(drop=True)
print(result['Problem'].equals(test['Answer Expected'])) # True
Python solution 2 for Generate Integers From Ranges, proposed by Luan Rodrigues:
import pandas as pd
df = pd.read_excel('PY/Excel_Challenge_441 - Integer Intervals/Excel_Challenge_441 - Integer Intervals.xlsx',usecols='A')
df['tab'] = df['Problem'].apply(lambda x: x.split(', ')).copy()
df = df[['Problem','tab']].explode(['tab'])
df['split'] = df['tab'].apply(lambda x: x.split('-') )
def ajuste(lista):
if len(lista) == 2:
return list(range(int(lista[0]), int(lista[1]) + 1))
else:
return lista
df['split'] = df['split'].apply(lambda x: ajuste(x) )
df['indice'] = df.index
df_agrupado = df.groupby(['Problem','indice'])['split'].sum(set([])).reset_index()
df_result = df_agrupado.sort_values(by=['indice'])
print(df_result[['split']])
Solving the challenge of Generate Integers From Ranges with Python in Excel
Python in Excel solution 1 for Generate Integers From Ranges, proposed by Abdallah Ally:
# I have recently been working on regular expressions
import pandas as pd
import re
file_path = 'Excel_Challenge_441 - Integer Intervals.xlsx'
df = pd.read_excel(file_path)
# Perform data transformation and cleansing
def integer_interval(col):
regex = r'(d+-d+)'
replacement = lambda x: ', '.join([str(y) for y in range(int(x[1].split('-')[0]), int(x[1].split('-')[1]) + 1)])
text = re.sub(regex, replacement, col)
numbers = [str(x) for x in sorted({int(y) for y in text.split(', ')})]
return ', '.join(numbers)
df['My Answer'] = df['Problem'].apply(integer_interval)
# Display final results
df
Python in Excel solution 2 for Generate Integers From Ranges, proposed by Abdallah Ally:
import pandas as pd
file_path = 'Excel_Challenge_441 - Integer Intervals.xlsx'
df = pd.read_excel(file_path)
# Perform data transformation and cleansing
def integer_interval(col):
chars = col.split(', ')
numbers = set()
for char in chars:
if char.find('-') > -1:
nums = [int(x) for x in char.split('-')]
numbers.update([x for x in range(nums[0], nums[1] + 1)])
else:
numbers.add(int(char))
return ', '.join([str(x) for x in sorted(numbers)])
df['My Answer'] = df['Problem'].apply(integer_interval)
# Display final results
df
Python in Excel solution 3 for Generate Integers From Ranges, proposed by ferhat CK:
all=xl("A1:A7", headers=True)
ans = []
for j in all.Problem:
cikis=[]
cc=j.strip("").split(",")
for i in cc:
if "-" in i:
q = i.split("-")
mn = int(q[0])
mx = int(q[1])
for n in range(mn, mx + 1):
if int(n) not in cikis:
cikis.append(int(n))
else:
if int(i) not in cikis:
cikis.append(int(i))
cikis.sort()
c2=[str(i) for i in cikis]
ans.append(','.join(c2))
pd.DataFrame({'Answer Expected':ans})
Solving the challenge of Generate Integers From Ranges with R
R solution 1 for Generate Integers From Ranges, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("Excel/441 Integer Intervals.xlsx", range = "A1:A7")
test = read_excel("Excel/441 Integer Intervals.xlsx", range = "B1:B7")
result = input %>%
mutate(rn = row_number()) %>%
separate_rows(Problem, sep = ", ") %>%
mutate(Problem = map(Problem, ~{
if(str_detect(., "-")){
range = str_split(., "-")[[1]]
seq(as.numeric(range[1]), as.numeric(range[2]))
} else {
as.numeric(.)
}
})) %>%
unnest(Problem) %>%
summarise(`Answer Expected` = str_c(sort(unique(Problem)), collapse = ", "), .by = rn) %>%
select(-rn)
&