Pivot the given data with headers suffixed with 1, 2… If you have PivotBy / GroupBy, try using that.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 413
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Pivot Using Suffix Headers with Power Query
Power Query solution 1 for Pivot Using Suffix Headers, proposed by Kris Jaganah:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(Source, {"ID"}, {"All", each _}),
Index = Table.AddColumn(Group, "AA", each Table.AddIndexColumn([All], "Nu", 1, 1)),
Xpand = Table.ExpandTableColumn(Index, "AA", {"Num", "Nu"}, {"Num", "Nu"}),
Remove = Table.RemoveColumns(Xpand, {"All"}),
AddPre = Table.TransformColumns(Remove, {"Nu", each "Num " & Text.From(_)}),
Pivot = Table.Pivot(AddPre, List.Distinct(AddPre[Nu]), "Nu", "Num")
in
PivotPower Query solution 2 for Pivot Using Suffix Headers, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Group = Table.Group(Source, "ID", {{"Count", Table.RowCount}, {"Filter", each [Num]}}),
Sort = Table.Sort(Group, "ID"),
Column = List.Transform({1 .. List.Max(Sort[Count])}, each "Num " & Text.From(_)),
Output = Table.AddColumn(
Sort,
"Output",
each Table.FromRows({[Filter]}, List.FirstN(Column, [Count]))
)[[ID], [Output]],
Return = Table.ExpandTableColumn(Output, "Output", Column, Column)
in
ReturnPower Query solution 3 for Pivot Using Suffix Headers, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.Sort(
Table.Combine(
Table.Group(
Source,
{"ID"},
{
{
"A",
each
let
a = List.Transform({1 .. List.Count([Num])}, each "Num " & Text.From(_)),
b = Table.PromoteHeaders(Table.FromRows({{"ID"} & a, {[ID]{0}} & [Num]}))
in
b
}
}
)[A]
),
"ID"
)
in
SolPower Query solution 4 for Pivot Using Suffix Headers, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Ids = List.Distinct(Source[ID]),
Group = Table.Combine(
Table.Group(
Source,
{"ID"},
{
{
"A",
each
let
a = [Num],
b = List.Transform({1 .. List.Count(a)}, each "Num " & Text.From(_)),
c = Table.FromRows({a}, b)
in
c
}
}
)[A]
),
Sol = Table.Sort(
Table.FromColumns({Ids} & Table.ToColumns(Group), {"ID"} & Table.ColumnNames(Group)),
"ID"
)
in
SolPower Query solution 5 for Pivot Using Suffix Headers, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
gp = Table.Combine(
Table.Group(
Fonte,
{"ID"},
{
{
"tab",
each [
a = {_[ID]{0}} & _[Num],
b = List.Transform(
{0 .. List.Count(a) - 1},
(x) => if x = 0 then "ID" else "Num " & Text.From(x)
),
c = Table.FromRows({a}, b)
][c]
}
}
)[tab]
),
res = Table.Sort(gp, {"ID", 0})
in
resPower Query solution 6 for Pivot Using Suffix Headers, proposed by Brian Julius:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(Source, {"ID"}, {{"All", each _, type table [ID = text, Num = number]}}),
AddIndex = Table.RemoveColumns(
Table.AddColumn(Group, "Custom", each Table.AddIndexColumn([All], "Index", 1, 1)),
"All"
),
Expand = Table.ExpandTableColumn(AddIndex, "Custom", {"Num", "Index"}, {"Num", "Index"}),
AddPrefix = Table.TransformColumns(
Expand,
{{"Index", each "Num " & Text.From(_, "en-US"), type text}}
),
Pivot = Table.Pivot(AddPrefix, List.Distinct(AddPrefix[Index]), "Index", "Num", List.Sum)
in
PivotPower Query solution 7 for Pivot Using Suffix Headers, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
a = Table.Sort(Table.Group(S, {"ID"}, {"G", each _}), {"ID", 0}),
b = Table.TransformColumns(a, {"G", each Table.Transpose([[Num]])}),
c = Table.TransformColumns(
b,
{"G", each Table.TransformColumnNames(_, each Text.Replace(_, "Column", "Num "))}
),
Sol = Table.ExpandTableColumn(c, "G", {"Num 1", "Num 2", "Num 3", "Num 4", "Num 5"})
in
SolPower Query solution 8 for Pivot Using Suffix Headers, proposed by Eric Laforce:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(
Source,
{"ID"},
{
"All",
each
let
CN = {"ID"} & List.Transform({1 .. List.Count(_[Num])}, each "Num " & Text.From(_))
in
Table.FromRows({{_[ID]{0}} & _[Num]}, CN)
}
),
Result = Table.Sort(Table.Combine(Group[All]), "ID")
in
ResultPower Query solution 9 for Pivot Using Suffix Headers, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source, {{"Num", type text}}),
#"Grouped Rows" = Table.Group(
#"Changed Type",
{"ID"},
{{"Num", each Text.Combine([Num], ","), type number}}
),
#"Split Column by Delimiter" = Table.SplitColumn(
Table.TransformColumnTypes(#"Grouped Rows", {{"Num", type text}}, "en-US"),
"Num",
Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv),
{"Num1", "Num2", "Num3", "Num4", "Num5"}
),
#"Sorted Rows" = Table.Sort(#"Split Column by Delimiter", {{"ID", Order.Ascending}})
in
#"Sorted Rows"Power Query solution 10 for Pivot Using Suffix Headers, proposed by Rafael González B.:
let
Source = Excel.CurrentWorkbook(){0}[Content],
LAc = List.Accumulate,
LID = Source[ID],
ListID = List.Sort(List.Distinct(LID)),
LA = LAc(
ListID,
{},
(s, c) =>
let
d = List.PositionOf(LID, c, 2),
e = {{c} & List.Transform(d, each Number.From(Source[Num]{_}))}
in
s & e
),
LZ = Table.FromColumns(List.Zip(LA), {"ID"} & LAc({"1" .. "5"}, {}, (x, y) => x & {"Num " & y}))
in
LZPower Query solution 11 for Pivot Using Suffix Headers, proposed by Rafael González B.:
let
Source = Excel.CurrentWorkbook(){0}[Content],
TTC = Table.TransformColumnTypes,
TCN = Table.ColumnNames,
TC = TTC(Source, {{"ID", type text}, {"Num", type text}}),
Sort = Table.Buffer(Table.Sort(TC, {{"ID", Order.Ascending}})),
GroupBy = Table.Group(Sort, {"ID"}, {{"Grouping", each _[Num]}}),
Values = GroupBy[Grouping],
Combine = Table.FromList(
List.Transform(Values, each Text.Combine(_, "|")),
Splitter.SplitByNothing(),
{"Num"}),
SplitColumn = Table.SplitColumn(
Combine,
"Num",
Splitter.SplitTextByDelimiter("|"),
{"Num1", "Num2", "Num3", "Num4", "Num5"}),
GetTable = Table.FromColumns(
{GroupBy[ID]} & Table.ToColumns(SplitColumn),
{"ID"} & TCN(SplitColumn)),
Result = TTC(GetTable, List.Transform(List.Skip(TCN(GetTable)), each {_, type number}))
in
Result
P.D: The last line of this code: ¡Thanks Bhavya Gupta 👏🏻👏🏻!
Power Query solution 12 for Pivot Using Suffix Headers, proposed by Luke Jarych:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Grouped = Table.Group(Source, {"ID"}, {{"GroupBy", each _}}),
Indexed = Table.TransformColumns(
Grouped,
{
"GroupBy",
each
let
a = Table.AddIndexColumn(_, "Index", 1, 1),
b = Table.AddColumn(a, "NumName", each "Num " & Text.From([Index]))
in
b
}
),
RemoveCol = Table.RemoveColumns(Indexed, "ID"),
ExpandedTable = Table.ExpandTableColumn(
RemoveCol,
"GroupBy",
{"ID", "Num", "NumName"},
{"ID", "Num", "NumName"}
),
Pivoted = Table.Pivot(ExpandedTable, List.Distinct(ExpandedTable[NumName]), "NumName", "Num")
in
PivotedPower Query solution 13 for Pivot Using Suffix Headers, proposed by Glyn Willis:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source, {{"ID", type text}, {"Num", Int64.Type}}),
#"Grouped Rows" = Table.Group(
#"Changed Type",
{"ID"},
{
{
"d",
each
let
n = List.Buffer([Num])
in
Table.FromRows(
{{[ID]{0}} & n},
{"ID"} & List.Transform({1 .. List.Count(n)}, (x) => "Num " & Text.From(x))
),
type table
}
}
),
d = Table.Combine(#"Grouped Rows"[d]),
#"Sorted Rows" = Table.Sort(d, {{"ID", Order.Ascending}})
in
#"Sorted Rows"Power Query solution 14 for Pivot Using Suffix Headers, proposed by Tyler N.:
let
s = YourTable,
a = Table.AddColumn(
Table.Sort(Table.Distinct(s[[ID]], "ID"), "ID"),
"b",
each
let
a = s,
b = [ID],
c = Table.AddColumn(
Table.AddIndexColumn(Table.SelectRows(a, each [ID] = b)[[Num]], "I", 1, 1),
"n",
each "Num " & Text.From([I])
)[[Num], [n]],
z = Table.Pivot(c, List.Distinct(c[n]), "n", "Num")
in
z
),
t = Table.ColumnNames(Table.Combine(a[b])),
x = Table.ExpandTableColumn(a, "b", t, t)
in
xPower Query solution 15 for Pivot Using Suffix Headers, proposed by Alexandra Popoff:
let
Source = Excel.CurrentWorkbook(){[Name = "Table"]}[Content],
// Prep: Gp by Id and add an Index to each line of the each sub group
#"Gp: by ID" = Table.Group(
Source,
{"ID"},
{{"Data", each _, type table [ID = text, Num = number]}}
),
#"Gp: Add Index to Sub Gp" = Table.AddColumn(
#"Gp: by ID",
"Data Index",
each Table.AddIndexColumn([Data], "Idx Line", 1),
type number
),
#"Gp: Keep Treated Data" = Table.SelectColumns(#"Gp: Add Index to Sub Gp", {"Data Index"}),
// Reimport Data
#"Reimport Data" = Table.ExpandTableColumn(
#"Gp: Keep Treated Data",
"Data Index",
{"ID", "Num", "Idx Line"},
{"ID", "Num", "Idx Line"}
),
#"Fix Data Type" = Table.TransformColumnTypes(
#"Reimport Data",
{{"ID", type text}, {"Idx Line", type text}, {"Num", type number}}
),
#"Add Prefix to Row Name" = Table.TransformColumns(
#"Fix Data Type",
{{"Idx Line", each "Num " & Text.From(_, "fr-FR"), type text}}
),
// Output
#"Out: Pivoted Row Name Col" = Table.Pivot(
#"Add Prefix to Row Name",
List.Distinct(#"Add Prefix to Row Name"[#"Idx Line"]),
"Idx Line",
"Num",
List.Sum
),
#"Out: Sort Table" = Table.Sort(#"Out: Pivoted Row Name Col", {{"ID", Order.Ascending}})
in
#"Out: Sort Table"Power Query solution 16 for Pivot Using Suffix Headers, proposed by Karunakaran S K P:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source, {{"ID", type text}, {"Num", Int64.Type}}),
#"Grouped Rows" = Table.Group(
#"Changed Type",
{"ID"},
{{"All Data", each _, type table [ID = text, Num = number]}}
),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Num", each Table.Column([All Data], "Num")),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom", {"All Data"}),
#"Extracted Values" = Table.TransformColumns(
#"Removed Columns",
{"Num", each Text.Combine(List.Transform(_, Text.From), ","), type text}
),
#"Split Column by Delimiter" = Table.SplitColumn(
#"Extracted Values",
"Num",
Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv),
{"Num.1", "Num.2", "Num.3", "Num.4", "Num.5"}
),
#"Changed Type1" = Table.TransformColumnTypes(
#"Split Column by Delimiter",
{
{"Num.1", Int64.Type},
{"Num.2", Int64.Type},
{"Num.3", Int64.Type},
{"Num.4", Int64.Type},
{"Num.5", Int64.Type}
}
)
in
#"Changed Type1"Power Query solution 17 for Pivot Using Suffix Headers, proposed by Cristobal Salcedo Beltran:
let
Source = Excel.CurrentWorkbook(){0}[Content],
#"Grouped Rows" = Table.Group(
Source,
{"ID"},
{"Num", each Text.Combine(List.Transform([Num], Text.From), "|"), type text}
),
#"Sorted Rows" = Table.Sort(#"Grouped Rows", {{"ID", Order.Ascending}}),
#"Split Column by Delimiter" = Table.SplitColumn(
#"Sorted Rows",
"Num",
Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv),
{"Num 1", "Num 2", "Num 3", "Num 4", "Num 5"}
)
in
#"Split Column by Delimiter"Solving the challenge of Pivot Using Suffix Headers with Excel
Excel solution 1 for Pivot Using Suffix Headers, proposed by Rick Rothstein:
=LET(
u,
SORT(
UNIQUE(
A2:A15
)
),
d,
DROP(
HSTACK(
u,
IFNA(
TEXTSPLIT(
REDUCE(
"",
u,
LAMBDA(
a,
x,
a&TEXTJOIN(
"*",
,
FILTER(
B2:B15,
A2:A15=x
)
)&"/"
)
),
"*",
"/"
),
""
)
),
-1
),
VSTACK(
HSTACK(
"ID",
"Num"&SEQUENCE(
,
COLUMNS(
d
)-1
)
),
d
)
)Excel solution 2 for Pivot Using Suffix Headers, proposed by محمد حلمي:
=REDUCE(
D1:I1,
SORT(
UNIQUE(
A2:A15
)
),
LAMBDA(
a,
d,
IFNA(
VSTACK(
a,
HSTACK(
d,
TOROW(
FILTER(
B2:B15,
A2:A15=d
)
)
)
),
""
)
)
)Excel solution 3 for Pivot Using Suffix Headers, proposed by 🇰🇷 Taeyong Shin:
=LET(
id,
A1:A15,
F,
LAMBDA(
i,
LAMBDA(
x,
IFERROR(
INDEX(
x,
i,
1
),
""
)
)
),
DROP(
GROUPBY(
id,
B1:B15,
MAP(
SEQUENCE(
,
MAX(
GROUPBY(
id,
id,
ROWS,
,
0
)
)
),
LAMBDA(
x,
F(
x
)
)
),
3,
0
),
1
)
)Excel solution 4 for Pivot Using Suffix Headers, proposed by 🇰🇷 Taeyong Shin:
=DROP(
PIVOTBY(
A1:A15,
"Num"&MAP(
A1:A15,
LAMBDA(
x,
COUNTIF(
A1:x,
x
)
)
),
B1:B15,
SINGLE,
3,
0,
,
0
),
1
)Excel solution 5 for Pivot Using Suffix Headers, proposed by Kris Jaganah:
=LET(
a,
SORT(
A2:B15
),
b,
DROP(
a,
,
1
),
c,
TAKE(
a,
,
1
),
d,
"Num "&SEQUENCE(
ROWS(
a
)
)-XMATCH(
c,
c
)+1,
PIVOTBY(
c,
d,
b,
SUM,
0,
0,
,
0
)
)Excel solution 6 for Pivot Using Suffix Headers, proposed by Kris Jaganah:
=LET(
a,
A2:A15,
b,
B2:B15,
c,
MAP(
a,
LAMBDA(
x,
COUNTIF(
TAKE(
a,
1
):x,
x
)
)
),
d,
SORT(
UNIQUE(
a
)
),
e,
"Num "&TOROW(
UNIQUE(
c
)
),
VSTACK(
HSTACK(
"ID",
e
),
HSTACK(
d,
XLOOKUP(
d&e,
a&"Num "&c,
b,
""
)
)
)
)Excel solution 7 for Pivot Using Suffix Headers, proposed by Julian Poeltl:
=LET(
I,
A2:A15,
N,
B2:B15,
F,
SORTBY(
N,
I
),
L,
SORT(
I
),
S,
UNIQUE(
L
),
R,
REDUCE(
"",
S,
LAMBDA(
A,
B,
VSTACK(
A,
TOROW(
FILTER(
F,
L=B
)
)
)
)
),
HSTACK(
S,
DROP(
IFERROR(
R,
""
),
1
)
)
)Excel solution 8 for Pivot Using Suffix Headers, proposed by Timothée BLIOT:
=LET(
A,
SORT(
A2:B15
),
B,
TAKE(
A,
,
1
),
PIVOTBY(
B,
"Num "&SCAN(
1,
SEQUENCE(
ROWS(
A
)
),
LAMBDA(
w,
v,
IF(
v>1,
IF(
INDEX(
B,
v
)=INDEX(
B,
v-1
),
w+1,
1
),
1
)
)
),
TAKE(
A,
,
-1
),
SUM,
,
0,
,
0
)
)Excel solution 9 for Pivot Using Suffix Headers, proposed by Hussein SATOUR:
=LET(
a,
A2:A15,
b,
SCAN(
,
a,
CONCAT
),
PIVOTBY(
a,
"Num"&LEN(
b
)-LEN(
SUBSTITUTE(
b,
a,
""
)
),
B2:B15,
SUM,
,
0,
,
0
)
)Excel solution 10 for Pivot Using Suffix Headers, proposed by Oscar Mendez Roca Farell:
=LET(
_d,
A2:A15,
_u,
UNIQUE(
SORT(
_d
)
),
REDUCE(
HSTACK(
A1,
B1&SEQUENCE(
,
MAX(
COUNTIF(
_d,
_u
)
)
)
),
_u,
LAMBDA(
i,
x,
IFNA(
VSTACK(
i,
UNIQUE(
TOROW(
FILTER(
A2:B15,
_d=x
)
),
1
)
),
""
)
)
)
)Excel solution 11 for Pivot Using Suffix Headers, proposed by Oscar Mendez Roca Farell:
=LET(
_d,
A2:A15,
_m,
MAP(
_d,
LAMBDA(
a,
COUNTIF(
A2:a,
a
)
)
),
_n,
TOROW(
UNIQUE(
_m
)
),
_u,
SORT(
UNIQUE(
_d
)
),
VSTACK(
HSTACK(
A1,
B1&_n
),
HSTACK(
_u,
XLOOKUP(
_u&_n,
_d&_m,
B2:B15,
""
)
)
)
)Excel solution 12 for Pivot Using Suffix Headers, proposed by Duy Tùng:
=LET(
a,
PIVOTBY(
A2:A15,
"Num "&MAP(
A2:A15,
LAMBDA(
x,
SUM(
N(
A2:x=x
)
)
)
),
B2:B15,
SINGLE,
,
0,
,
0
),
IF(
TAKE(
a,
1
)&TAKE(
a,
,
1
)="",
A1,
a
)
)Excel solution 13 for Pivot Using Suffix Headers, proposed by Sunny Baggu:
=LET(
_id,
SORT(
UNIQUE(
A2:A15
)
),
_tbl,
IF(
TOROW(
A2:A15
) = _id,
TOROW(
B2:B15
),
"x"
),
_v,
DROP(
IFNA(
REDUCE(
"",
SEQUENCE(
ROWS(
_id
)
),
LAMBDA(
a,
v,
VSTACK(
a,
FILTER(
INDEX(
_tbl,
v,
),
INDEX(
_tbl,
v,
) <> "x"
)
)
)
),
""
),
1
),
VSTACK(
HSTACK(
A1,
B1 & SEQUENCE(
,
COLUMNS(
_v
)
)
),
HSTACK(
_id,
_v
)
)
)Excel solution 14 for Pivot Using Suffix Headers, proposed by Sunny Baggu:
=LET(
_id,
SORT(
UNIQUE(
A2:A15
)
),
_r,
ROWS(
_id
),
_c,
MAX(
MAP(
_id,
LAMBDA(
x,
ROWS(
FILTER(
B2:B15,
A2:A15 = x
)
)
)
)
),
_v,
IFERROR(
MAKEARRAY(
_r,
_c,
LAMBDA(
r,
c,
INDEX(
TOROW(
IF(
A2:A15 = INDEX(
_id,
r
),
B2:B15,
x
),
3
),
c
)
)
),
""
),
VSTACK(
HSTACK(
A1,
B1 & SEQUENCE(
,
_c
)
),
HSTACK(
_id,
_v
)
)
)Excel solution 15 for Pivot Using Suffix Headers, proposed by LEONARD OCHEA 🇷🇴:
=PIVOTBY(
A2:A15,
"Num "&MAP(
A2:A15,
LAMBDA(
a,
COUNTIF(
A2:a,
a
)
)
),
B2:B15,
SUM,
0,
0,
,
0
)Excel solution 16 for Pivot Using Suffix Headers, proposed by Abdallah Ally:
=LET(
a,
A2:A15,
b,
B2:B15,
c,
REDUCE(
"",
SORT(
UNIQUE(
a
)
),
LAMBDA(
x,
y,
VSTACK(
x,
HSTACK(
y,
TOROW(
FILTER(
b,
a=y
)
)
)
)
)
),
IFNA(
VSTACK(
HSTACK(
"ID",
"Num "&SEQUENCE(
,
COLUMNS(
c
)-1
)
),
DROP(
c,
1
)
),
""
)
)Excel solution 17 for Pivot Using Suffix Headers, proposed by Asheesh Pahwa:
=LET(
id,
A2:A15,
nm,
B2:B15,
u,
SORT(
UNIQUE(
id
)
),
r,
IFNA(
DROP(
REDUCE(
"",
u,
LAMBDA(
x,
y,
VSTACK(
x,
TOROW(
FILTER(
nm,
id=y
)
)
)
)
),
1
),
""
),
VSTACK(
HSTACK(
"ID",
"Num "&SEQUENCE(
,
COLUMNS(
r
)
)
),
HSTACK(
u,
r
)
)
)Excel solution 18 for Pivot Using Suffix Headers, proposed by Charles Roldan:
=LET(ID,
A2:A15,
Num,
B2:B15,
Main,
LAMBDA(
a,
HSTACK(
a,
TOROW(
FILTER(
Num,
ID = a
)
)
)
),
VLoop,
LAMBDA(f,
LAMBDA(
g,
g(
g
)
)(LAMBDA(g,
LAMBDA(x,
IFNA(IF(ROWS(
x
) = 1,
f(
x
),
VSTACK(f(
TAKE(
x,
1
)
),
g(
g
)(DROP(
x,
1
)))),
""))))),
VLoop(
Main
)(SORT(
UNIQUE(
ID
)
)))Excel solution 19 for Pivot Using Suffix Headers, proposed by Charles Roldan:
=LET(ID,
A2:A15,
Num,
B2:B15,
Main,
LAMBDA(
x,
HSTACK(
x,
TOROW(
FILTER(
Num,
ID = x
)
)
)
),
R,
LAMBDA(
f,
LAMBDA(
x,
IFNA(
DROP(
REDUCE(
"",
SEQUENCE(
ROWS(
x
)
),
LAMBDA(
a,
b,
VSTACK(
a,
f(
INDEX(
x,
b
)
)
)
)
),
1
),
""
)
)
),
Body,
R(
Main
)(SORT(
UNIQUE(
ID
)
)),
VSTACK(
HSTACK(
"ID",
"Num " & SEQUENCE(
,
COLUMNS(
Body
) - 1
)
),
Body
))Excel solution 20 for Pivot Using Suffix Headers, proposed by Bilal Mahmoud kh.:
=x))))),
",",
"|",
TRUE),
"")Excel solution 21 for Pivot Using Suffix Headers, proposed by Milan Shrimali:
=let(
a,
A1:A15,
b,
B1:B15,
u,
unique(
a
),
data,
map(
u,
lambda(
x,
transpose(
filter(
b,
a=x
)
)
)
),
header,
"NUM " & sequence(
1,
max(
byrow(
data,
lambda(
aa,
counta(
aa
)
)
)
)
),
hstack(
vstack(
"ID",
u
),
vstack(
header,
data
)
)
)Excel solution 22 for Pivot Using Suffix Headers, proposed by Oscar Javier Rosero Jiménez:
=LET(
_a,
A2:A15,
_b,
B2:B15,
_c,
SORT(
UNIQUE(
_a
)
),
HSTACK(
_c,
IFERROR(
DROP(
REDUCE(
0,
_c,
LAMBDA(
i,
x,
VSTACK(
i,
TOROW(
FILTER(
_b,
_a=x
)
)
)
)
),
1
),
""
)
)
)Excel solution 23 for Pivot Using Suffix Headers, proposed by Giorgi Goderdzishvili:
= pd.read_clipboard()
mapping = df.groupby(
"ID"
).cumcount().add(
1
)
df["Num2"] = "Num" + df.index.map(
mapping
).astype(
str
)
nw_df = df.pivot(
index="ID",
columns="Num2"
).fillna(
''
)Excel solution 24 for Pivot Using Suffix Headers, proposed by Songglod P.:
=LET(
ids,
A2:A15,
nums,
B2:B15,
uids,
SORT(
UNIQUE(
ids
)
),
group_nums,
DROP(
REDUCE(
"",
uids,
LAMBDA(
curr,
val,
VSTACK(
curr,
TOROW(
FILTER(
nums,
ids=val
)
)
)
)
),
1
),
IFNA(
HSTACK(
uids,
group_nums
),
""
)
)Excel solution 25 for Pivot Using Suffix Headers, proposed by Burhan Cesur:
=LET(
b,
VSTACK(
"title",
MAP(
A2:A15,
LAMBDA(
x,
"Num "&COUNTIF(
A2:x,
x
)
)
)
),
t,
A1:B15,
c,
HSTACK(
t,
b
),
DROP(
PIVOTBY(
CHOOSECOLS(
c,
1
),
CHOOSECOLS(
c,
3
),
CHOOSECOLS(
c,
2
),
VALUE,
3,
0,
,
0
),
1
)
)Excel solution 26 for Pivot Using Suffix Headers, proposed by Tyler Cameron:
=LET(
a,
SORT(
UNIQUE(
A2:A15
),
,
1
),
HSTACK(
a,
MAKEARRAY(
4,
5,
LAMBDA(
r,
c,
IFERROR(
INDEX(
FILTER(
B2:B15,
A2:A15=INDEX(
a,
r
)
),
c
),
""
)
)
)
)
)Excel solution 27 for Pivot Using Suffix Headers, proposed by Crispo Mwangi:
="Num "&COUNTIF(
$A$2:A2,
A2
)Excel solution 28 for Pivot Using Suffix Headers, proposed by Luis Couto:
=DROP(PIVOTBY(A1:A15,"Num "&MAP(A1:A15,LAMBDA(x,SUM(--(A2:x=x)))),B1:B15,SUM,3,0,,0,),1)
without PIVOTBY:
=LET(u,UNIQUE(SORT(A2:A15)),n,IFNA(REDUCE("",u,LAMBDA(a,i,VSTACK(a,TOROW(IF(A2:A15=i,B2:B15,T),3)))),""),HSTACK(VSTACK("ID",u),VSTACK("Num "&SEQUENCE(,COLUMNS(n)),DROP(n,1))))Excel solution 29 for Pivot Using Suffix Headers, proposed by James Mott:
=LET(
a,
A2:A15,
VSTACK(
HSTACK(
A1,
B1&SEQUENCE(
,
MAX(
COUNTIF(
a,
a
)
)
)
),
HSTACK(
SORT(
UNIQUE(
a
)
),
IFNA(
TEXTSPLIT(
TEXTJOIN(
".",
,
INDEX(
GROUPBY(
A1:A15,
B1:B15,
ARRAYTOTEXT,
,
0
),
,
2
)
),
", ",
"."
),
""
)
)
)
)Excel solution 30 for Pivot Using Suffix Headers, proposed by Alexandra Popoff:
= LAMBDA(
Col_Header_Name ,
Row_Prefix_Name,
Col_Value,
Data_Value,
[Empty_Default],
LET(
Opt_NF,
if(
isomitted(
Empty_Default
),
"",
Empty_Default
),
Y_List,
SORT(
UNIQUE(
Col_Value
)
),
Y_List_Max,
BYROW(
Y_List,
LAMBDA(
z_i,
ROWS(
FILTER(
Col_Value,
Col_Value=z_i
)
)
)
),
Y_Max,
ROWS(
Y_List
),
X_Max,
MAX(
Y_List_Max
),
Out_Value,
MAKEARRAY(
Y_Max,
X_Max,
LAMBDA(
z_Y,
z_X,
IFERROR(
INDEX(
FILTER(
Data_Value,
Col_Value=INDEX(
Y_List,
z_Y
)
),
z_X
),
Opt_NF
)
)
),
Out_Header,
Row_Prefix_Name&" "&TEXT(
SEQUENCE(
1,
X_Max,
1,
1
),
"0"
),
VSTACK(
HSTACK(
Col_Header_Name,
Out_Header
),
HSTACK(
Y_List,
Out_Value
)
)
)
)Solving the challenge of Pivot Using Suffix Headers with Python
_x000D_Python solution 1 for Pivot Using Suffix Headers, proposed by Luke Jarych:
import xlwings as xw
import pandas as pd
import csv
import io
wb = xw.Book(r'Excel_Challenge_413 - Pivot.xlsx')
sh = wb.sheets[0]
table = sh.tables['Table1']
rng = sh.range(table.range.address)
df = rng.options(pd.DataFrame, header = True, index=False, numbers=int).value
df.sort_values(by='ID', inplace=True)
df['Index'] = 'Num ' + (df.groupby('ID').cumcount() + 1).astype(str)
df = df.pivot(index='ID', columns='Index', values='Num')
df.fillna(0, inplace=True)
df.replace(0, '', inplace = True)
df = df.applymap(lambda x: x.split('.')[0] if '.' in x else x)
Solving the challenge of Pivot Using Suffix Headers with Python in Excel
_x000D_Python in Excel solution 1 for Pivot Using Suffix Headers, proposed by Abdallah Ally:
import pandas as pd
file_path = 'Excel_Challenge_413 - Pivot.xlsx'
df = pd.read_excel(file_path, usecols='A:B')
df_test = pd.read_excel(file_path, usecols='D:I').dropna(subset=['ID.1']).rename(columns={'ID.1': 'ID'})
df['Serial'] = 'Num ' + (df.groupby('ID').cumcount() + 1).astype(str)
df = df.pivot(index='ID', columns='Serial', values='Num')
df = df.rename_axis(None, axis=1).reset_index()
for d in [df, df_test]:
for col in [x for x in d.columns if 'Num' in x]:
d[col] = df[col].apply(lambda x: pd.to_numeric(x, errors='coerce')).fillna(-1).astype(int).astype(str)
d[col] = d[col].replace('-1', '')
print(f'Expected Results:n{df_test}nnMy Results:n{df}')
Solving the challenge of Pivot Using Suffix Headers with R
_x000D_R solution 1 for Pivot Using Suffix Headers, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("Excel/413 Pivot.xlsx", range = "A1:B15")
test = read_excel("Excel/413 Pivot.xlsx", range = "D1:I5")
result = input %>%
group_by(ID) %>%
mutate(rn = row_number()) %>%
pivot_wider(names_from = rn, names_prefix = "Num ", values_from = Num) %>%
ungroup() %>%
arrange(ID)