Create the ticket numbers for each State. For a state, ticket number will start with 1 and will run sequentially and will be 6 digits long and will be preceded by first 2 alphabets of state name. The numbers will be a range Start – End. Ex. for state California, 2 cities have been issued 10 and 12 tickets. So first city will have tickets from 1 to 10 and second will have tickets from 11 to 22. Hence numbers will be CA000001 – CA000010 and CA000011 – CA000022.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 620
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Generate Sequential Ticket Numbers with Power Query
Power Query solution 1 for Generate Sequential Ticket Numbers, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.ToColumns(A),
C = List.Generate(
() => [a = 0, b = B{0}{0}, c = B{2}{0}, d = 1],
each [a] < List.Count(B{0}),
each [
a = [a] + 1,
b = if B{0}{a} = null then [b] else B{0}{a},
c = if b = [b] then [c] + B{2}{a} else B{2}{a},
d = if b = [b] then [c] + 1 else 1
],
each [
p = List.Transform(
{[c], [d]},
(x) => Text.Upper(Text.Start([b], 2)) & Text.PadStart(Text.From(x), 6, "0")
),
q = p{1} & " - " & p{0}
][q]
)
in
Table.FromColumns({C}, {"Answer Expected"})Power Query solution 2 for Generate Sequential Ticket Numbers, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Group = Table.Group(
Source,
"State",
{
"A",
each [
FV = Text.Upper(Text.Start(Table.FirstValue(_), 2)),
G = List.Generate(
() => [a = - 1, b = 0],
(f) => f[a] < Table.RowCount(_),
(f) => [
a = f[a] + 1,
b = f[b] + _{a}[#"No. of Tickets"],
c = FV & Number.ToText(f[b] + 1, "000000 - ") & FV & Number.ToText(b, "000000")
],
(f) => f[c]
),
R = Table.FromColumns(
Table.ToColumns(_) & {List.Skip(G)},
Table.ColumnNames(_) & {"Answer"}
)
][R]
},
0,
(x, y) => Number.From(y <> null)
),
Return = Table.Combine(Group[A])
in
ReturnPower Query solution 3 for Generate Sequential Ticket Numbers, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(
Source,
"State",
{
{
"Answer",
each
let
a = _,
b = Text.Upper(Text.Start([State]{0}, 2)),
c = List.Accumulate([No. of Tickets], {0}, (s, c) => s & {List.Last(s) + c}),
d = List.Transform(List.RemoveLastN(c), each _ + 1),
e = List.Skip(c),
f = List.Transform(
{0 .. List.Count(d) - 1},
each b
& Text.PadStart(Text.From(d{_}), 6, "0")
& " - "
& b
& Text.PadStart(Text.From(e{_}), 6, "0")
)
in
f
}
},
0,
(a, b) => Number.From(b <> null)
)[[Answer]],
Sol = Table.ExpandListColumn(Group, "Answer")
in
SolPower Query solution 4 for Generate Sequential Ticket Numbers, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
f = (txt) => Text.Upper(Text.Start(txt, 2)),
g = (num) => Text.PadStart(Text.From(num), 6, "0"),
h = (tbl) => Table.ColumnNames(tbl),
Accum = List.Accumulate(
{1 .. Table.RowCount(Source) - 1},
{{f(Source[State]{0}), 1, Source[No. of Tickets]{0}}},
(s, c) =>
s
& (
if Source[State]{c} = null then
[a = List.Last(s), b = {{a{0}, a{2} + 1, a{2} + Source[No. of Tickets]{c}}}][b]
else
{{f(Source[State]{c}), 1, Source[No. of Tickets]{c}}}
)
),
Transform = List.Transform(Accum, each _{0} & g(_{1}) & " - " & _{0} & g(_{2})),
FromCols = Table.FromColumns(Table.ToColumns(Source) & {Transform}, h(Source) & {"My Answer"}),
Result = Table.AddColumn(FromCols, "Check", each [My Answer] = [Answer Expected])
in
ResultPower Query solution 5 for Generate Sequential Ticket Numbers, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
TAC = Table.AddColumn,
a = Table.FillDown(S, {"State"}),
b = Table.Group(a, "State", {"G", each _})[G],
Fx = (x) =>
let
c = Table.AddIndexColumn(x, "I", 1),
d = TAC(c, "R", each List.Sum(List.Range(c[No. of Tickets], 0, [I]))),
e = Table.AddIndexColumn(d, "J"),
f = TAC(e, "P", each try e{[J] - 1}[R] + 1 otherwise 1),
g = Table.TransformColumnTypes(f, {{"R", type text}, {"P", type text}}),
h = Table.TransformColumns(
g,
{{"R", each Text.PadStart(_, 6, "0")}, {"P", each Text.PadStart(_, 6, "0")}}
),
i = TAC(
h,
"Answer Expected",
each Text.Start(Text.Upper([State]), 2)
& [P]
& " - "
& Text.Start(Text.Upper([State]), 2)
& [R]
)[[Answer Expected]]
in
i,
Sol = Table.Combine(List.Transform(b, each Fx(_)))
in
SolPower Query solution 6 for Generate Sequential Ticket Numbers, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(
Source,
"State",
{
"Temp",
each List.Accumulate(
List.Skip([No. of Tickets]),
{[S = 1, E = [No. of Tickets]{0}]},
(a, v) => a & {[S = List.Last(a)[E] + 1, E = List.Last(a)[E] + v]}
)
},
0,
(x, y) => Number.From(y <> null)
),
Expand = Table.ExpandRecordColumn(Table.ExpandListColumn(Group, "Temp"), "Temp", {"S", "E"}),
Res = Table.AddColumn(
Expand,
"Answer Expected",
each [
T = List.Transform(
{[S], [E]},
(x) => Text.Upper(Text.Start([State], 2)) & Number.ToText(x, "000000")
),
R = Text.Combine(T, " - ")
][R]
)[[Answer Expected]]
in
ResPower Query solution 7 for Generate Sequential Ticket Numbers, proposed by Meganathan Elumalai:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
FillDown = Table.FillDown(Source, {"State"}),
Group = Table.Group(
FillDown,
{"State"},
{
{
"Ans",
each [
lt = List.Transform,
ul = List.Skip(List.Accumulate(_[No. of Tickets], {0}, (s, c) => s & {List.Last(s) + c})),
ll = {1} & lt(List.RemoveLastN(ul, 1), (f) => f + 1),
zip = lt(
List.Zip({ll, ul}),
(x) =>
Text.Combine(
lt(x, (z) => Text.Start(Text.Upper(_[State]{0}), 2) & Number.ToText(z, "000000")),
" - "
)
)
][zip]
}
}
)[[Ans]],
Expand = Table.ExpandListColumn(Group, "Ans")
in
ExpandPower Query solution 8 for Generate Sequential Ticket Numbers, proposed by Rafael González B.:
let
Source = Question_Table,
Fx_Sequence = (Tbl) =>
let
St = Text.Upper(Text.Start(Tbl{0}[State],2)),
LV = Tbl[No. of Tickets],
LS = List.Sum(LV),
LP = List.Transform({1..LS}, each St & Text.PadStart(Text.From(_), Text.Length(Text.From(LS)), "0")),
LG = List.Skip(List.Generate(
() => [i = 0, L = LP],
each [i] <= List.Count(LV),
each [
i = [i] + 1,
LB = LV,
LR = List.Range([L], 0, LB{i - 1}),
L = List.RemoveFirstN([L], LB{i - 1})
],
each [LR])),
LT = List.Transform(LG, each List.First(_) & " - " & List.Last(_))
in
LT,
Group = Table.FromList(
List.Combine(
Table.Group(Source,
"State", {{"Details", each Fx_Sequence(_)}}, 1,
(x,y) => Number.From(y is text))[Details]
),
null, {"Answer Expected"}
)
in
Group
🧙🏻♂️🧙🏻♂️🧙🏻♂️
Power Query solution 9 for Generate Sequential Ticket Numbers, proposed by CA Raghunath Gundi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
FillDown = Table.FillDown(Source, {"State"}),
Code = Table.AddColumn(FillDown, "Code", each Text.Upper(Text.Start([State], 2)), type text),
Grp = Table.Group(
Code,
{"State"},
{{"Grp", each _, type table [State = text, City = text, No. of Tickets = number, Code = text]}}
),
FxA = (t) =>
let
Index = Table.AddIndexColumn(t, "Index", 0, 1, Int64.Type),
Start = Table.AddColumn(
Index,
"start",
each [Code]
& Text.PadStart(
Text.From((List.Sum(List.FirstN(Index[No. of Tickets], [Index])) + 1) ?? 1),
6,
"0"
)
),
End = Table.AddColumn(
Start,
"end",
each [Code]
& Text.PadStart(
Text.From((List.Sum(List.FirstN(Index[No. of Tickets], [Index] + 1))) ?? 1),
6,
"0"
)
),
Ans = Table.CombineColumns(
End,
{"start", "end"},
Combiner.CombineTextByDelimiter(" - ", QuoteStyle.None),
"Answer"
)[Answer]
in
Ans,
Func = Table.ExpandListColumn(Table.TransformColumns(Grp, {"Grp", each FxA(_)}), "Grp")[[Grp]]
in
FuncPower Query solution 10 for Generate Sequential Ticket Numbers, proposed by Mihai Radu O:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
prefF = (l as list) => List.Transform(l, (n) => Text.PadStart(Text.From(n), 6, "0")),
s = [
lt = List.Transform,
lz = List.Zip,
nt = Source[No. of Tickets],
st = Source[State],
s = lt(st, (x) => Byte.From(x is null)),
a = List.Generate(
() => [x = 0, y = nt{0}],
each [x] < List.Count(nt),
each [x = [x] + 1, y = [y] * s{x} + nt{x}],
each [y]
),
b = lt(lz({nt, a}), (x) => x{1} - x{0} + 1),
c = List.Generate(
() => [x = 0, y = Text.Upper(Text.Start(st{0}, 2))],
each [x] < List.Count(st),
each [x = [x] + 1, y = if st{x} <> null then Text.Upper(Text.Start(st{x}, 2)) else [y]],
each [y]
),
d = lt(lz({c, prefF(b), prefF(a)}), (x) => x{0} & x{1} & " - " & x{0} & x{2})
][d]
in
sPower Query solution 11 for Generate Sequential Ticket Numbers, proposed by Krzysztof Kominiak:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Result = Table.Combine(
Table.Group(
Source,
{"State"},
{
{
"NT",
each [
a = _,
a1 = Text.Start(Text.Upper(_[State]{0}), 2),
b = a[No. of Tickets],
c = List.RemoveLastN(
List.Accumulate(
b,
{1},
(s, c) => if List.IsEmpty(s) then {c} else s & {List.Last(s) + c}
),
1
),
d = List.Accumulate(
b,
{},
(s, c) => if List.IsEmpty(s) then {c} else s & {List.Last(s) + c}
),
e = List.Zip({c, d}),
f = List.Transform(
e,
(x) =>
a1
& Text.PadStart(Text.From(x{0}), 6, "0")
& " - "
& a1
& Text.PadStart(Text.From(x{1}), 6, "0")
),
g = Table.ToColumns(a) & {f},
h = Table.FromColumns(g, Table.ColumnNames(a) & {"Answer"})
][h]
}
},
0,
(x, y) => Number.From(y[State] <> null)
)[NT]
)
in
ResultSolving the challenge of Generate Sequential Ticket Numbers with Excel
Excel solution 1 for Generate Sequential Ticket Numbers, proposed by Bo Rydobon 🇹🇭:
=SCAN(0,C2:C13,LAMBDA(a,v,LET(l,@+A13:v,TEXTJOIN(" - ",,UPPER(LEFT(l&a,2))&TEXT(IF(l>0,0,RIGHT(a,6))+v^{0,1},"000000")))))Excel solution 2 for Generate Sequential Ticket Numbers, proposed by John V.:
=SCAN(0,
C2:C13,
LAMBDA(a,
v,
LET(i,
@+A13:v,
TEXTJOIN(" - ",
,
UPPER(
LEFT(
IF(
i>0,
i,
a
),
2
)
)&TEXT((i=0)*RIGHT(
a,
6
)+v^{0,
1},
"000000")))))Excel solution 3 for Generate Sequential Ticket Numbers, proposed by Kris Jaganah:
=LET(
p,
SCAN(
,
UPPER(
LEFT(
A2:A13,
2
)
),
LAMBDA(
x,
y,
IF(
y="",
x,
y
)
)
),
REDUCE(
"Answer Expected",
UNIQUE(
p
),
LAMBDA(
v,
w,
VSTACK(
v,
LET(
a,
TEXT(
SCAN(
,
FILTER(
C2:C13,
p=w
),
SUM
),
"000000"
),
b,
TEXT(
VSTACK(
1,
DROP(
a,
-1
)+1
),
"000000"
),
w&b&" - "&w&a
)
)
)
)
)Excel solution 4 for Generate Sequential Ticket Numbers, proposed by Julian Poeltl:
=DROP(REDUCE(0,UNIQUE(TOCOL(A2:A13,3)),LAMBDA(A,B,VSTACK(A,LET(L,UPPER(LEFT(B,2)),C,SCAN(0,FILTER(C2:C13,SCAN(0,A2:A13,LAMBDA(A,B,IF(B="",A,B)))=B),LAMBDA(A,B,A+B)),L&TEXT(DROP(VSTACK(1,C+1),-1),"000000")&" - "&L&TEXT(C,"000000"))))),1)Excel solution 5 for Generate Sequential Ticket Numbers, proposed by Timothée BLIOT:
=LET(A,A2:A13,B,C2:C13,C,SEQUENCE(ROWS(A)),D,SCAN(0,A,LAMBDA(w,v,IF(v=0,w,UPPER(LEFT(v,2))))),E,SCAN(0,C,LAMBDA(w,v,IF(INDEX(A,v)=0,INDEX(B,v)+w,INDEX(B,v)))),MAP(C,LAMBDA(x,INDEX(D,x)&BASE(IF(INDEX(A,x)<>0,1,INDEX(E,x-1)+1),10,6)&" - "&INDEX(D,x)&BASE(INDEX(E,x),10,6))))Excel solution 6 for Generate Sequential Ticket Numbers, proposed by Oscar Mendez Roca Farell:
=LET(
d,
A2:A13,
s,
SCAN(
0,
d,
LAMBDA(
i,
x,
LET(
n,
MAX(
TAKE(
x:C13,
1
)
),
IF(
x>"",
n,
n+i
)
)
)
),
BYROW(
SCAN(
0,
d,
LAMBDA(
i,
x,
IF(
x>"",
UPPER(
MID(
x,
1,
2
)
),
& i
)
)
)&TEXT(
HSTACK(
VSTACK(
0,
DROP(
s,
-1
)
)+1,
s
),
"000000"
)&{" - ",
""},
CONCAT
)
)Excel solution 7 for Generate Sequential Ticket Numbers, proposed by Sunny Baggu:
=LET(
_f, SCAN("", A2:A13, LAMBDA(a, v, IF(v = "", a, v))),
_u, UNIQUE(_f),
DROP(
REDUCE(
"😊🆕year",
_u,
LAMBDA(x, y,
VSTACK(
x,
LET(
_t, FILTER(C2:C13, _f = y),
_rt, SCAN(0, _t, LAMBDA(a, v, a + v)),
BYROW(
WRAPROWS(
UPPER(LEFT(y, 2)) &
VSTACK("000001", DROP(TOCOL(TEXT(HSTACK(_rt, _rt + 1), "000000")), -1)),
2
),
LAMBDA(a, TEXTJOIN(" - ", , a))
)
)
)
)
),
1
)
)Excel solution 8 for Generate Sequential Ticket Numbers, proposed by LEONARD OCHEA 🇷🇴:
=LET(n,
C2:C13,
i,
UPPER(
LEFT(
SCAN(
,
A2:A13,
LAMBDA(
a,
b,
IF(
b>0,
b,
a
)
)
),
2
)
),
s,
SEQUENCE(
ROWS(
n
)
),
z,
s-TOROW(
s
),
F,
LAMBDA([x],
MMULT((i=TOROW(
i
))*(IF(
x,
z>=0,
z>0
)),
n)),
G,
LAMBDA(
x,
i&TEXT(
x,
"000000"
)
),
G(
F()+1
)&" - "&G(
F(
1
)
))Excel solution 9 for Generate Sequential Ticket Numbers, proposed by Md. Zohurul Islam:
=LET(
a,
A2:A13,
b,
C2:C13,
d,
LEFT(
UPPER(
SCAN(
,
a,
LAMBDA(
x,
y,
IF(
y="",
x,
y
)
)
)
),
2
),
unq,
UNIQUE(
d
),
e,
DROP(
REDUCE(
"",
unq,
LAMBDA(
x,
y,
LET(
p,
FILTER(
b,
d=y
),
q,
SCAN(
,
p,
SUM
),
s,
TEXT(
q,
"000000"
),
u,
TEXT(
VSTACK(
1,
DROP(
q,
-1
)+1
),
"000000"
),
v,
y&u&" - "&y&s,
w,
VSTACK(
x,
v
),
w
)
)
),
1
),
e
)Excel solution 10 for Generate Sequential Ticket Numbers, proposed by Asheesh Pahwa:
=LET(
s,
SCAN(
"",
A2:A13,
LAMBDA(
x,
y,
IF(
y<>"",
y,
x
)
)
),
u,
UNIQUE(
s
),
REDUCE(
C1,
u,
LAMBDA(
a,
v,
VSTACK(
a,
LET(
f,
FILTER(
C2:C13,
s=v
),
_s,
SCAN(
0,
f,
LAMBDA(
x,
y,
x+y
)
),
l,
UPPER(
LEFT(
v,
2
)
),
t,
TEXT(
_s,
"000000"
),
b,
TEXT(
VSTACK(
1,
DROP(
t,
-1
)+1
),
"000000"
),
l&b&" - "&l&t
)
)
)
)
)Excel solution 11 for Generate Sequential Ticket Numbers, proposed by Philippe Brillault:
=LET(PUSH,LAMBDA(x,y,DROP(VSTACK(y,x),-1)),FT,LAMBDA(x,TEXT(x,"000000")),partcum,LAMBDA(x,y,IF(y=0,0,x+y)),fill,LAMBDA(x,y,IF(y="",x,y)),tk,_T[No.ofTickets],st,_T[State],start,SCAN(,(st="")*PUSH(tk,0),partcum)+1,end,start+tk-1,state,UPPER(LEFT(SCAN(,st,fill),2)),state&FT(start)&"-"&state&end)Excel solution 12 for Generate Sequential Ticket Numbers, proposed by Ricardo Romero Garcia:
=LET(
i;
TEXTO(
SCAN(
0;
C2:C13;
LAMBDA(
a;
v;
SI(
DESREF(
v;
;
-2;
)>0;
1;
a+DESREF(
v;
-1;
0
)
)
)
);
"000000"
);
f;
SCAN(
0;
C2:C13;
LAMBDA(
a;
v;
TEXTO(
SI(
DESREF(
v;
;
-2;
)>0;
v;
a+v
);
"000000"
)
)
);
s;
MAYUSC(
IZQUIERDA(
SCAN(
"";
A2:A13;
LAMBDA(
a;
v;
SI(
v="";
a;
v
)
)
);
2
)
);
s&i&" - "&s&f
)Excel solution 13 for Generate Sequential Ticket Numbers, proposed by Craig Hatmaker:
=TicketRange(DROP(A:.A,1), DROP(C:.C,1))
TicketRange = LAMBDA( States, Tickets,
LET(
State, UPPER( LEFT( SCAN( "", States,
LAMBDA(Acc, Val, IF( Val=0, Acc, Val))), 2)),
TicketEnd, SCAN(0, SEQUENCE( ROWS( States)),
LAMBDA(Acc, n,
IF( INDEX( States, n) = 0,
Acc + INDEX( Tickets, n),
INDEX( Tickets, n)))),
TicketBeg, TicketEnd - Tickets + 1,
Result, State & TEXT(TicketBeg,"000000") & " - " &
State & TEXT(TicketEnd,"000000"),
Result
)
)Excel solution 14 for Generate Sequential Ticket Numbers, proposed by Scott Miller:
=LET(
Prefix,
IF(
A2="",
LEFT(
I1,
2
),
LEFT(
A2,
2
)
),
Qty,
C2,
Start,
IF(
OR(
A1="State",
A2<>""
),
1,
RIGHT(
TEXTAFTER(
I1,
" - "
),
6
)+1
),
Prefix&TEXT(
Start,
"000000"
)&" - "&Prefix&TEXT((Start+Qty-1),
"000000"))Solving the challenge of Generate Sequential Ticket Numbers with Python
Python solution 1 for Generate Sequential Ticket Numbers, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "620 Ticket Numbers.xlsx"
input = pd.read_excel(path, sheet_name=0, usecols="A:C", nrows=13)
test = pd.read_excel(path, sheet_name=0, usecols="D", nrows=13)
input['State'] = input['State'].ffill()
input['abbr'] = input['State'].str[:2].str.upper()
input['cum_num'] = input['No. of Tickets'].cumsum()
input['max_cums'] = input['cum_num'].max()
input['first_per_city'] = (input['cum_num'] - input['No. of Tickets'] + 1).astype(str).str.zfill(6)
input['max_per_city'] = input['cum_num'].astype(str).str.zfill(6)
input['Answer Expected'] = input.apply(lambda row: f"{row['abbr']}{row['first_per_city']} - {row['abbr']}{row['max_per_city']}", axis=1)
result = input[['Answer Expected']]
print(result.equals(result)) # True
Solving the challenge of Generate Sequential Ticket Numbers with Python in Excel
Python in Excel solution 1 for Generate Sequential Ticket Numbers, proposed by Alejandro Campos:
df = xl("A1:D13", headers=True).ffill()
def generate_ticket_numbers(df):
return pd.DataFrame([{
"Ticket Range": f"{state[:2].upper()}{start:06d}-{state[:2].upper()}{start + count - 1:06d}"}
for state in df['State'].unique()
for start, count in zip(
[1] + list(df[df['State'] == state]['No. of Tickets'].cumsum()[:-1] + 1),
df[df['State'] == state]['No. of Tickets'])])
ticket_df = generate_ticket_numbers(df)
Solving the challenge of Generate Sequential Ticket Numbers with R
R solution 1 for Generate Sequential Ticket Numbers, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/620 Ticket Numbers.xlsx"
input = read_excel(path, range = "A1:C13")
test = read_excel(path, range = "D1:D13")
result = input %>%
fill(State, .direction = "down") %>%
mutate(abbr = str_to_upper(str_sub(State, 1, 2)),
cum_num = cumsum(`No. of Tickets`),
max_cums = max(cum_num),
first_per_city = str_pad(as.character(cum_num - `No. of Tickets` + 1), 6, pad = "0", side = "left"),
max_per_city = str_pad(as.character(cum_num), 6, pad = "0", side = "left"),
.by = State) %>%
mutate(`Answer Expected` = pmap_chr(list(first_per_city, max_per_city, abbr), ~paste0(..3, ..1, " - ",..3, ..2))) %>%
select(`Answer Expected`)
all.equal(result, test, check.attributes = FALSE)
# [1] TRUE
&&