Generate the ticket numbers as given. All first numbers from Sequence column will be appended to corresponding Booklet cell one by one, then second number and so on. If no number in Sequence, no ticket number will be generated.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 652
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Generate Sequential Tickets with Power Query
Power Query solution 1 for Generate Sequential Tickets, proposed by Kris Jaganah:
let]} ,(x,y)=> y & x))[Ans] )))} , {"Answer Expected"})Power Query solution 2 for Generate Sequential Tickets, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Table.SelectRows(
Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
each [Sequence] <> null
),
Cols = Table.Combine(
Table.AddColumn(
Source,
"A",
each
let
a = _,
b = Record.ToList(a),
c = Text.Split(b{1}, ", "),
d = List.Transform(c, each b{0} & _),
e = Table.FromRows({d})
in
e
)[A]
),
Sol = List.RemoveNulls(List.Combine(Table.ToColumns(Cols)))
in
SolPower Query solution 3 for Generate Sequential Tickets, proposed by Luan Rodrigues:
let
Fonte = Table.AddColumn(
Tabela1,
"lista",
each
let
b = Text.Split([Sequence], ", "),
c = try
List.Transform({0 .. List.Count(b) - 1}, (x) => Text.Combine({[Booklet], b{x}}, ", "))
otherwise
{null}
in
c
)[lista],
res = List.TransformMany(List.Zip(Fonte), (x) => List.RemoveNulls(x), (a, b) => b)
in
resPower Query solution 4 for Generate Sequential Tickets, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
LT = List.Transform,
a = LT(S[Sequence], each try Text.Split(Text.From(_), ", ") otherwise {_}),
b = LT(S[Booklet], each {_}),
c = LT(List.Positions(b), each b{_} & a{_}),
Fx = (x) =>
let
d = x,
e = List.Skip(LT(d, each d{0} & _))
in
e,
f = List.Combine(List.Zip(LT(c, each Fx(_)))),
Sol = Table.FromColumns({List.RemoveNulls(f)}, {"Answer Expected"})
in
SolPower Query solution 5 for Generate Sequential Tickets, proposed by Meganathan Elumalai:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Result = [
cnt = Table.RowCount(Source),
fin = Table.FromList(
List.RemoveNulls(
List.Combine(
List.Zip(
List.Transform(
{0 .. cnt - 1},
(f) =>
List.Transform(
try Text.Split(Source[Sequence]{f}, ", ") otherwise {},
(x) => Source[Booklet]{f} & x
)
)
)
)
),
null,
{"Result"}
)
][fin]
in
ResultPower Query solution 6 for Generate Sequential Tickets, proposed by Rafael González B.:
let
Source = Question_Table,
RemoveNull = Table.SelectRows(Source, each ([Sequence] <> null)),
Tickets = Table.AddColumn(RemoveNull, "Union", each
let
a = Text.Split([Sequence], ", "),
b = {[Booklet]},
c = List.TransformMany(b, each a, (x,_) => x & _)
in
c)[Union],
Result = Table.FromList(
List.RemoveNulls(List.Combine(List.Zip(Tickets))),
Splitter.SplitByNothing(),
{"Answer Expected"})
in
Result
🧙🏻♂️🧙🏻♂️🧙🏻♂️
Power Query solution 7 for Generate Sequential Tickets, proposed by Peter Krkos:
let
FilteredRows = Table.SelectRows(Source, each ([Sequence] <> null)),
Ad_L = Table.AddColumn(FilteredRows, "L", each Text.Split([Sequence], ", ")),
Rows = List.Buffer(Table.ToRows(Ad_L[[Booklet], [L]])),
Gen = List.Accumulate(
{0 .. List.Max(List.Transform(Ad_L[L], List.Count)) - 1},
{},
(s, c) => s & {List.Transform(Rows, each _{0} & _{1}{c}?)}
),
ToTbl = Table.FromList(List.RemoveNulls(List.Combine(Gen)), null, type table [Answer = text])
in
ToTblPower Query solution 8 for Generate Sequential Tickets, proposed by CA Raghunath Gundi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Split = Table.SplitColumn(
Source,
"Sequence",
Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv),
{"1" .. "4"}
),
Unpivot = Table.UnpivotOtherColumns(Split, {"Booklet"}, "Attribute", "Value"),
Sort = Table.Sort(Unpivot, {{"Attribute", Order.Ascending}, {"Booklet", Order.Ascending}}),
Merge = Table.CombineColumns(
Sort,
{"Booklet", "Value"},
Combiner.CombineTextByDelimiter("", QuoteStyle.None),
"Answer Expected"
)[[Answer Expected]]
in
MergePower Query solution 9 for Generate Sequential Tickets, proposed by Alexandre Garcia:
let
H = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
P = List.Transform,
L = Table.ToList(
H,
(x) =>
let
a = List.RemoveItems(Text.Split(x{1} ?? "", ", "), {""})
in
List.Zip({List.Positions(a), P(a, each x{0} & _)})
),
C = Table.FromColumns({P(List.Sort(List.Combine(L), each _{0}), each _{1})}, {"Answer"})
in
CPower Query solution 10 for Generate Sequential Tickets, proposed by Krzysztof Kominiak:
let
Source = Table.SelectRows(
Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"i45WclTSUbLQUTA0UorViVZyAvJMwCxnIMvIUEfByNASzHcB8sEMVyADJK6jYKyjAFHrBhQyBZphoRQbCwA=",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [Booklet = _t, Sequence = _t]
),
each [Sequence] <> null and [Sequence] <> ""
),
TabTr = Table.TransformColumns(Source, {}, each Text.Split(_, ", ")),
Ls = Table.AddColumn(
TabTr,
"Ls",
each List.TransformMany([Sequence], (x) => [Booklet], (x, y) => y & x)
)[Ls],
Result = Table.FromColumns(
{
List.RemoveNulls(
List.Combine(Table.ToColumns(Table.Combine(List.Transform(Ls, each Table.FromRows({_})))))
)
},
{"Answer Exp."}
)
in
ResultPower Query solution 11 for Generate Sequential Tickets, proposed by Tyler N.:
let
a = YourTable,
k = List.Transform,
o = {0 .. 20},
f = List.RemoveNulls(
List.Combine(
k(
o,
each k(
o,
(x) =>
try
a[Booklet]{x} & Text.Split(Text.From(Table.ToRows(a[[Sequence]]){x}{0}), ", "){_}
otherwise
null
)
)
)
)
in
fPower Query solution 12 for Generate Sequential Tickets, proposed by Oleksandr Mynka:
let
DELIM = ", ",
src = Excel.CurrentWorkbook(){[Name = "SourceTable"]}[Content],
cnt = List.Accumulate(
List.Transform(src[Sequence], Splitter.SplitTextByDelimiter(DELIM)),
0,
(s, c) => List.Max({s, List.Count(c)})
),
split = Table.SplitColumn(src, "Sequence", Splitter.SplitTextByDelimiter(DELIM), cnt),
unpiv = Table.UnpivotOtherColumns(split, {"Booklet"}, "Order", "Num"),
sort = Table.Sort(unpiv, {{"Order", 0}, {"Booklet", 0}}),
del = Table.RemoveColumns(sort, {"Order"}),
res = Table.CombineColumns(del, {"Booklet", "Num"}, (x) => Text.Combine(x), "Answer Expected")
in
resSolving the challenge of Generate Sequential Tickets with Excel
Excel solution 1 for Generate Sequential Tickets, proposed by Bo Rydobon 🇹🇭:
=TOCOL(
A2:A7&TEXTSPLIT(
TEXTAFTER(
" "&B2:B7,
" ",
{1,
2,
3,
4}
),
","
),
3,
1
)Excel solution 2 for Generate Sequential Tickets, proposed by Rick Rothstein:
=TOCOL(
A2:A7&TEXTSPLIT(
TEXTAFTER(
", "&B2:B7,
", ",
SEQUENCE(
,
99
)
),
", "
),
3,
1
)Excel solution 3 for Generate Sequential Tickets, proposed by John V.:
=TOCOL(A2:A7&TEXTSPLIT(TEXTAFTER(" "&B2:B7," ",COLUMN(A:Z)),","),2,1)Excel solution 4 for Generate Sequential Tickets, proposed by Kris Jaganah:
=LET(a,A2:A7,b,B2:B7,TOCOL(TEXTSPLIT(CONCAT(IF(b="","",a&SUBSTITUTE(b," ",a))&"-"),",","-",1),3,1))Excel solution 5 for Generate Sequential Tickets, proposed by Julian Poeltl:
=LET(
T,
TOCOL(
A2:A7&DROP(
REDUCE(
1,
B2:B7,
LAMBDA(
A,
B,
VSTACK(
A,
IFERROR(
TEXTSPLIT(
B,
", "
),
""
)
)
)
),
1
),
3,
1
),
FILTER(
T,
LEN(
T
)>1
)
)Excel solution 6 for Generate Sequential Tickets, proposed by Timothée BLIOT:
=LET(
A,
TOCOL(
REDUCE(
0,
ROW(
1:6
),
LAMBDA(
w,
v,
VSTACK(
w,
IFERROR(
INDEX(
A2:A7,
v,
)&TEXTSPLIT(
INDEX(
B2:B7,
v,
),
", "
),
0
)
)
)
),
3,
1
),
FILTER(
A,
A<>0
)
)Excel solution 7 for Generate Sequential Tickets, proposed by Hussein SATOUR:
=LET(A,TEXTJOIN,B,TEXTSPLIT,TOCOL(B(A("",,MAP(A2:A7,B2:B7,LAMBDA(x,y,IFERROR(A("/",,x&B(y,", "))&",","")))),"/",",",1),3,1))Excel solution 8 for Generate Sequential Tickets, proposed by Oscar Mendez Roca Farell:
=TOCOL(
A2:A7&--TEXTSPLIT(
CONCAT(
B2:B7&"|"
),
", ",
"|"
),
2,
1
)Excel solution 9 for Generate Sequential Tickets, proposed by Duy Tùng:
=LET(a,TEXTSPLIT(TEXTJOIN("/",,IF(B2:B7>0,B2:B7,", ")),", ","/"),TOCOL(IFS(a>"",A2:A7&a),3,1))Excel solution 10 for Generate Sequential Tickets, proposed by Sunny Baggu:
=TOCOL(
FILTER(
A2:A7,
B2:B7 <> ""
) &
TEXTSPLIT(
TEXTJOIN(
"/",
1,
B2:B7
),
", ",
"/"
),
3,
1
)Excel solution 11 for Generate Sequential Tickets, proposed by Sunny Baggu:
=LET(
_a,
IFNA(
DROP(
REDUCE(
"",
B2:B7,
LAMBDA(
a,
v,
VSTACK(
a,
IFERROR(
TEXTSPLIT(
v,
", "
),
""
)
)
)
),
1
),
""
),
TOCOL(
IF(
_a = "",
1 / x,
A2:A7 & _a
),
3,
1
)
)Excel solution 12 for Generate Sequential Tickets, proposed by Anshu Bantra:
= to_df(REF("A1:B7"))
df['Sequence'] = df['Sequence'].str.split(', ')
df_1 = df.explode('Sequence').reset_index(drop=True)
index_lst =[]
df_index = list(df_1.index)
while not sorted(index_lst) == df_index:
last_element = ''
for idx, element in enumerate(df_1['Booklet']):
if element != last_element and idx not in index_lst:
index_lst.append(idx)
last_element = element
df_1 = df_1.reindex(index_lst).dropna()
df_1['Booklet'].values+df_1['Sequence'].apply(str)Excel solution 13 for Generate Sequential Tickets, proposed by Md. Zohurul Islam:
=LET(u,A2:A7,v,B2:B7,c,u&"-"&v,
d,DROP(REDUCE("",FILTER(c,v<>""),LAMBDA(x,y,LET(e,TEXTSPLIT(y,{"-",", "}),f,TAKE(e,,1),g,DROP(e,,1),VSTACK(x,f&g)))),1),
h,TOCOL(d,3,1),
h)Excel solution 14 for Generate Sequential Tickets, proposed by Pieter de B.:
=TOCOL(A2:A7&TEXTSPLIT(TEXTAFTER(", "&B2:B7,", ",SEQUENCE(,85)),", "),2,1)
86 being 255/3 (comma, space, character)Excel solution 15 for Generate Sequential Tickets&, proposed by Hamidi Hamid:
=LET(x,IFERROR(DROP(TEXTSPLIT(CONCAT("/"&A2:A7&","&B2:B7),",","/"),1),""),w,SUBSTITUTE(TOCOL(TRANSPOSE(IF(DROP(x,,1)<>"",TAKE(x,,1)&"-"&DROP(x,,1),1/0)),3),"-",),w)Excel solution 16 for Generate Sequential Tickets, proposed by Asheesh Pahwa:
=LET(
t,
TEXTAFTER(
", "&B2:B7,
", ",
SEQUENCE(
,
MAX(
MAP(
B2:B7,
LAMBDA(
x,
COUNTA(
TEXTSPLIT(
x,
", "
)
)
)
)
)
)
),
TOCOL(
A2:A7&TEXTSPLIT(
t,
", "
),
2,
1
)
)Excel solution 17 for Generate Sequential Tickets, proposed by Asheesh Pahwa:
=LET(
d,
DROP(
REDUCE(
"",
B2:B7,
LAMBDA(
x,
y,
VSTACK(
x,
IFERROR(
TEXTSPLIT(
y,
", "
),
""
)
)
)
),
1
),
TOCOL(
A2:A7&IF(
d="",
NA(),
d
),
2,
1
)
)Excel solution 18 for Generate Sequential Tickets, proposed by ferhat CK:
=TOCOL(TRANSPOSE(LET(a,A2:B7,f,FILTER(a,TAKE(a,,-1)>0),REDUCE("Answer Experted",BYROW(f,ARRAYTOTEXT),LAMBDA(x,y,VSTACK(x,REGEXEXTRACT(y,"w")®EXEXTRACT(y,"d+",1)))))),2)Excel solution 19 for Generate Sequential Tickets, proposed by Jaroslaw Kujawa:
=LET(c;REDUCE("";SEQUENCE(MAX(LEN(B2:B7)-LEN(SUBSTITUTE(B2:B7;",";""))+1));LAMBDA(b;kol;VSTACK(b;REDUCE("";B2:B7;LAMBDA(a;x;LET(f;TEXTSPLIT(x;;", ");nr;IFERROR(CHOOSEROWS(f;kol);"");VSTACK(a;IF(LEN(nr);OFFSET(x;;-1)&nr;""))))))));FILTER(c;LEN(c)))Excel solution 20 for Generate Sequential Tickets, proposed by Meganathan Elumalai:
=LET(x,TOCOL(REDUCE("",B2:B7,LAMBDA(a,v,VSTACK(a,IFERROR(OFFSET(v,,-1)&TEXTSPLIT(v,", "),"")))),3,1),FILTER(x,x>""))Excel solution 21 for Generate Sequential Tickets, proposed by Imam Hambali:
=LET(
a, FILTER(A2:B7,B2:B7<>""),
cc, CHOOSECOLS,
TOCOL(cc(a,1)&TEXTSPLIT(TEXTJOIN(";",1, cc(a,2)),", ",";"),3,1)
)Excel solution 22 for Generate Sequential Tickets, proposed by Ahmed Ariem:
=TEXTSPLIT(
TEXTJOIN(
",",
1,
MAP(
SUBSTITUTE(
B2:B7,
" ",
""
),
A2:A7,
LAMBDA(
a,
b,
REGEXREPLACE(
a,
"b(d{1,3})b",
b&"1"
)
)
)
),
,
","
)Excel solution 23 for Generate Sequential Tickets, proposed by El Badlis Mohd Marzudin:
=LET(
a,
TEXTSPLIT(
TEXTJOIN(
"|",
0,
B2:B7
),
", ",
"|",
,
,
""
),
b,
A2:A7&a,
TOCOL(
IF(
LEN(
b
)>1,
b,
NA()
),
3,
1
)
)Excel solution 24 for Generate Sequential Tickets, proposed by Erdit Qendro:
=LET(
rng,
A2:B7,
rngfiltered,
FILTER(
rng,
TAKE(
rng,
,
-1
)<>""
),
rowscount,
SEQUENCE(
ROWS(
rngfiltered
)
),
colLetter,
INDEX(
rngfiltered,
rowscount,
1
),
rowsN,
INDEX(
rngfiltered,
rowscount,
2
),
rowsNSplit,
DROP(
REDUCE(
"",
rowscount,
LAMBDA(
a,
I,
LET(
txToSplit,
INDEX(
rowsN,
I,
1
),
IF(
I>MAX(
rowscount
),
a,
VSTACK(
a,
TEXTSPLIT(
txToSplit,
", "
)
)
)
)
)
),
1
),
TOCOL(
colLetter&rowsNSplit,
3,
TRUE
)
)Excel solution 25 for Generate Sequential Tickets, proposed by Erdit Qendro:
=LET(rng,A2:B7,
rngfiltered,FILTER(rng,TAKE(rng,,-1)<>""),
colLetter,INDEX(rngfiltered,,1),
rowNumb,INDEX(rngfiltered,,2),
rowNumbSplit,TEXTSPLIT(TEXTJOIN("|",,rowNumb),", ","|"),
TOCOL(colLetter&rowNumbSplit,3,TRUE))Excel solution 26 for Generate Sequential Tickets, proposed by Gabriel Pugliese:
=LET(
z,
A2:B7,
_a,
FILTER(
z,
TAKE(
z,
,
-1
)>""
),
_b,
DROP(
REDUCE(
"",
DROP(
_a,
,
1
),
LAMBDA(
a,
v,
VSTACK(
a,
TEXTSPLIT(
v,
", "
)
)
)
),
1
),
TOCOL(
TAKE(
_a,
,
1
)&_b,
2,
1
)
)Solving the challenge of Generate Sequential Tickets with Python
Python solution 1 for Generate Sequential Tickets, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "652 Generate Ticket Numbers.xlsx"
input = pd.read_excel(path, usecols="A:B", nrows=7)
test = pd.read_excel(path, usecols="C", nrows=12)
input["Sequence"] = input["Sequence"].str.split(", ")
result = (
input.explode("Sequence")
.dropna()
.assign(rn=lambda d: d.groupby("Booklet").cumcount() + 1)
.sort_values(["rn", "Booklet"])
.drop(columns="rn")
.assign(**{"Answer Expected": lambda d: d["Booklet"].astype(str) + d["Sequence"].astype(str)})[["Answer Expected"]]
.reset_index(drop=True)
)
print(result.equals(test)) # True
Python solution 2 for Generate Sequential Tickets, proposed by Luan Rodrigues:
import pandas as pd
from itertools import zip_longest
file = r"Excel_Challenge_652 - Generate Ticket Numbers.xlsx"
df = pd.read_excel(file, usecols='A:B').dropna()
df['Result'] = df.apply(lambda row: [f"{row['Booklet']}{item}" for item in str(row['Sequence']).split(', ')], axis=1)
df = pd.DataFrame([x for grupo in zip_longest(*df['Result']) for x in grupo if x], columns=['Result'])
print(df)
Solving the challenge of Generate Sequential Tickets with Python in Excel
Python in Excel solution 1 for Generate Sequential Tickets, proposed by Alejandro Campos:
data = xl("A1:B7", headers=True)
data["Sequence"] = [list(map(int, seq.split(", "))) if seq else [] for seq in data["Sequence"]]
[f"{b}{seq[i]}" for i in range(max(map(len, data["Sequence"]))) for b, seq in zip("ABCDEF", data["Sequence"]) if i < len(seq)]
Python in Excel solution 2 for Generate Sequential Tickets, proposed by Aditya Kumar Darak 🇮🇳:
df = xl("A1:B7", True)
df["Sequence"] = df["Sequence"].str.split(", ")
df = df.explode("Sequence").dropna()
df["Answer"] = df["Booklet"] + df["Sequence"]
result = df["Answer"].reset_index(drop=True)
result
Solving the challenge of Generate Sequential Tickets with R
R solution 1 for Generate Sequential Tickets, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/652 Generate Ticket Numbers.xlsx"
input = read_excel(path, range = "A1:B7")
test = read_excel(path, range = "C1:C12")
result = input %>%
separate_rows(Sequence, sep = ", ") %>%
na.omit() %>%
mutate(rn = row_number(), .by = Booklet) %>%
arrange(rn, Booklet) %>%
select(-rn) %>%
unite(`Answer Expected`, Booklet, Sequence, sep = "")
all.equal(result, test)
#> [1] TRUE
&&