Merge rows on the basis of names. Sort on names. Result cells should also be sorted.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 606
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Merge and Sort Name Rows with Power Query
_x000D_Power Query solution 1 for Merge and Sort Name Rows, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.UnpivotOtherColumns(A, {"Name"}, "A", "V"),
C = Table.Pivot(B, List.Distinct(B[A]), "A", "V", each Text.Combine(List.Sort(_), ", "))
in
CPower Query solution 2 for Merge and Sort Name Rows, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Unpivot = Table.UnpivotOtherColumns(Source, {"Name"}, "A", "V"),
Return = Table.Pivot(
Unpivot,
List.Skip(Table.ColumnNames(Source)),
"A",
"V",
(x) => Text.Combine(List.Sort(x), ", ")
)
in
ReturnPower Query solution 3 for Merge and Sort Name Rows, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(
Source,
{"Name"},
{
{
"A",
each
let
a = _,
b = List.Skip(Table.ToColumns(a)),
c = List.Transform(b, each Text.Combine(List.Sort(_), ", ")),
d = Table.FromRows({c}, List.Skip(Table.ColumnNames(a)))
in
d
}
}
),
Sort = Table.Sort(Group, {{"Name", 0}}),
Sol = Table.ExpandTableColumn(Sort, "A", Table.ColumnNames(Sort[A]{0}))
in
SolPower Query solution 4 for Merge and Sort Name Rows, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Result = Table.Group(
Source,
"Name",
List.Transform(
List.Skip(Table.ColumnNames(Source)),
(x) => {x, each Text.Combine(List.Sort(Table.Column(_, x)), ", ")}
),
1,
(x, y) => Comparer.Ordinal(x, y)
)
in
ResultPower Query solution 5 for Merge and Sort Name Rows, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Unpivot = Table.UnpivotOtherColumns(Source, {"Name"}, "A", "V"),
Result = Table.Pivot(
Unpivot,
List.Distinct(Unpivot[A]),
"A",
"V",
each Text.Combine(List.Sort(_), ", ")
)
in
ResultPower Query solution 6 for Merge and Sort Name Rows, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
LT = List.Transform,
a = Table.Group(S, {"Name"}, {"G", each _})[G],
b = LT(a, Table.ToColumns),
c = LT(b, each LT(_, each List.Sort(List.Distinct(List.RemoveNulls(_))))),
d = Table.FromRows(LT(c, each LT(_, each Text.Combine(_, ", ")))),
Sol = Table.RenameColumns(
Table.Sort(d, {"Column1", 0}),
List.Zip({Table.ColumnNames(d), Table.ColumnNames(S)})
)
in
SolPower Query solution 7 for Merge and Sort Name Rows, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
ColNames = Table.ColumnNames(Source),
Grouped = Table.Group(
Source,
{"Name"},
{
{
"Temp",
each List.Transform(
Table.ToColumns(_),
each Text.Combine(List.Sort(List.Distinct(_)), ", ")
)
}
}
),
Res = Table.Sort(Table.FromRows(Grouped[Temp], ColNames), {{"Name", 0}})
in
ResPower Query solution 8 for Merge and Sort Name Rows, proposed by Meganathan Elumalai:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Unpivot = Table.UnpivotOtherColumns(Source, {"Name"}, "A", "V"),
Pivot = Table.Pivot(
Unpivot,
List.Distinct(Unpivot[A]),
"A",
"V",
each Text.Combine(List.Sort(_), ", ")
)
in
PivotPower Query solution 9 for Merge and Sort Name Rows, proposed by CA Raghunath Gundi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Unpivot = Table.UnpivotOtherColumns(Source, {"Name"}, "Attribute", "Value"),
Group = Table.Group(
Unpivot,
{"Name", "Attribute"},
{{"A", each _[Value], type table [Name = text, Attribute = text, Value = text]}}
),
Transformation = Table.TransformColumns(
Group,
{{"A", each Text.Combine(List.Sort(_, Order.Ascending), ", ")}}
),
Result = Table.Pivot(Transformation, List.Distinct(Transformation[Attribute]), "Attribute", "A")
in
ResultPower Query solution 10 for Merge and Sort Name Rows, proposed by Ahmed Ariem:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(
Source,
"Name",
{
"tmp",
(x) =>
[
a = List.Skip(Table.ToColumns(x)),
b = List.Transform(a, (x) => Text.Combine(List.Sort(x), ",")),
c = Table.FromRows({b}, {"Value1", "Value2", "Value3", "Value4"})
][c]
}
),
Expand = Table.ExpandTableColumn(Group, "tmp", {"Value1", "Value2", "Value3", "Value4"})
in
ExpandPower Query solution 11 for Merge and Sort Name Rows, proposed by Krzysztof Kominiak:
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"XY7bCsIwDIbfpdd7CzsURBjoXelFWGNXVlOI3cre3mZVESHk+OdLjFG9K8BOdeqEVL1exrkGMdsZdcY1SLt/LNUPwJmD8yj5hPAEyjW9LTzjtuu/NNkBH0V54FT+ec2G4DHR+/bvWKdVNq8FaAcA4YeggQLGWh1TdPdA49Qe45Qb9LIRKGtf",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [Name = _t, Value1 = _t, Value2 = _t, Value3 = _t, Value4 = _t]
),
LH = List.Skip(Table.ColumnNames(Source)),
GroupRows = Table.Group(
Source,
{"Name"},
{
{
"NT",
each Table.FromRows(
{
List.Transform(
List.Skip(Table.ToColumns(_)),
(x) => Text.Combine(List.RemoveMatchingItems(List.Sort(x), {""}), ", ")
)
},
LH
)
}
}
),
Result = Table.Sort(Table.ExpandTableColumn(GroupRows, "NT", LH), {{"Name", 0}})
in
ResultSolving the challenge of Merge and Sort Name Rows with Excel
_x000D_Excel solution 1 for Merge and Sort Name Rows, proposed by Bo Rydobon 🇹🇭:
=GROUPBY(A1:A7,B1:E7,LAMBDA(x,TEXTJOIN(", ",,SORT(x))),3,0)Excel solution 2 for Merge and Sort Name Rows, proposed by Rick Rothstein:
=LET(n,SORT(UNIQUE(A2:A7)),DROP(REDUCE("",n,LAMBDA(b,y,LET(f,T(FILTER(B2:E7,A2:A7=y)),VSTACK(b,REDUCE(A2,SEQUENCE(,4),LAMBDA(a,x,HSTACK(a,TEXTJOIN(", ",,SORT(INDEX(f,SEQUENCE(ROWS(f)),x)))))))))),1))Excel solution 3 for Merge and Sort Name Rows, proposed by John V.:
=GROUPBY(A1:A7,B1:E7,LAMBDA(x,TEXTJOIN(", ",,SORT(x))),3,0)Excel solution 4 for Merge and Sort Name Rows, proposed by Kris Jaganah:
=GROUPBY(
A1:A7,
TEXT(
B1:E7,
""
),
LAMBDA(
x,
TEXTJOIN(
", ",
,
SORT(
x
)
)
),
3,
0
)Excel solution 5 for Merge and Sort Name Rows, proposed by Julian Poeltl:
=LET(
N,
A2:A7,
T,
B2:E7,
REDUCE(
A1:E1,
UNIQUE(
SORT(
N
)
),
LAMBDA(
A,
B,
VSTACK(
A,
HSTACK(
B,
BYCOL(
FILTER(
T,
N=B
),
LAMBDA(
A,
TEXTJOIN(
", ",
,
SORT(
A
)
)
)
)
)
)
)
)
)Excel solution 6 for Merge and Sort Name Rows, proposed by Aditya Kumar Darak 🇮🇳:
=GROUPBY(
A2:A7,
B2:E7,
LAMBDA(
a,
TEXTJOIN(
", ",
TRUE,
SORT(
a
)
)
),
0,
0
)Excel solution 7 for Merge and Sort Name Rows, proposed by Timothée BLIOT:
=GROUPBY(A1:A7,B1:E7,LAMBDA(x,TEXTJOIN(", ",,SORT(x))),1,0)Excel solution 8 for Merge and Sort Name Rows, proposed by Duy Tùng:
=GROUPBY(
A1:A7,
B1:E7,
LAMBDA(
x,
TEXTJOIN(
", ",
,
SORT(
x
)
)
),
3,
0
)Excel solution 9 for Merge and Sort Name Rows, proposed by Sunny Baggu:
=LET(
_u, SORT(UNIQUE(A2:A7)),
_v, DROP(
REDUCE(
"",
_u,
LAMBDA(a, v,
VSTACK(
a,
BYCOL(
FILTER(IF(B2:E7 = "", "", B2:E7), A2:A7 = v),
LAMBDA(a, TEXTJOIN(", ", , SORT(a)))
)
)
)
),
1
),
HSTACK(_u, _v)
)Excel solution 10 for Merge and Sort Name Rows, proposed by Md. Zohurul Islam:
=LET(p,A2:A7,q,B1:E1,s,B2:E7,a,TOCOL(IFNA(p,q)),b,TOCOL(IFNA(q,p)),d,TOCOL(s),rng,FILTER(HSTACK(a,b,d),d<>0),
e,SORT(UNIQUE(p))&q,f,TAKE(rng,,1)&CHOOSECOLS(rng,2),g,MAP(e,LAMBDA(x,ARRAYTOTEXT(SORT(FILTER(TAKE(rng,,-1),f=x,""))))),h,HSTACK(SORT(UNIQUE(p)),g),j,VSTACK(HSTACK(A1,q),h),j)Excel solution 11 for Merge and Sort Name Rows, proposed by Md. Zohurul Islam:
=LET(
p,
A2:A7,
q,
B1:E1,
s,
B2:E7,
a,
TOCOL(
IFNA(
p,
q
)
),
b,
TOCOL(
IFNA(
q,
p
)
),
d,
TOCOL(
s
),
e,
PIVOTBY(
a,
b,
d,
LAMBDA(
x,
TEXTJOIN(
", ",
1,
SORT(
x
)
)
),
0,
0,
,
0
),
f,
VSTACK(
A1,
DROP(
TAKE(
e,
,
1
),
1
)
),
g,
HSTACK(
f,
DROP(
e,
,
1
)
),
g
)Excel solution 12 for Merge and Sort Name Rows, proposed by Hamidi Hamid:
=LET(x,DROP(TEXTSPLIT(CONCAT("/"&A2:A7&"-"&B1:E1&"-"&B2:E7),"-","/"),1),PIVOTBY(TAKE(x,,1),CHOOSECOLS(x,2),TAKE(x,,-1),LAMBDA(a,TEXTJOIN(", ",,SORT(a,1))),,0,,0))Excel solution 13 for Merge and Sort Name Rows, proposed by Asheesh Pahwa:
=LET(n,A2:A7,DROP(REDUCE("",SORT(UNIQUE(n)),LAMBDA(x,y,VSTACK(x,
LET(f,FILTER(B2:E7,n=y),HSTACK(y,BYCOL(f,LAMBDA(a,ARRAYTOTEXT(SORT(FILTER(a,a<>"","")))))))))),1))Excel solution 14 for Merge and Sort Name Rows, proposed by ferhat CK:
=REDUCE(
A1:E1,
SORT(
UNIQUE(
A2:A7
)
),
LAMBDA(
x,
y,
VSTACK(
x,
HSTACK(
y,
BYCOL(
FILTER(
B2:E7,
A2:A7=y
),
LAMBDA(
v,
TEXTJOIN(
", ",
,
v
)
)
)
)
)
)
)Excel solution 15 for Merge and Sort Name Rows, proposed by Ankur Sharma:
=GROUPBY(
A3:A8,
B3:E8,
LAMBDA(
z,
TEXTJOIN(
", ",
,
SORT(
z
)
)
),
0,
0,
1
)Excel solution 16 for Merge and Sort Name Rows, proposed by Meganathan Elumalai:
=LET(z,B2:E7,nm,A2:A7,vl,B1:E1,unm,SORT(UNIQUE(nm)),REDUCE(A1:E1,unm,LAMBDA(a,v,VSTACK(a,HSTACK(v,BYCOL(vl,LAMBDA(x,TEXTJOIN(", ",,SORT(FILTER(FILTER(z,vl=x),nm=v,""))))))))))Excel solution 17 for Merge and Sort Name Rows, proposed by Imam Hambali:
=LET(
l, LAMBDA(x, TOCOL(IF(B2:E7<>"",x,NA()),3)),
p, PIVOTBY(l(A2:A7), l(B1:E1), l(B2:E7),ARRAYTOTEXT,0,0,,0),
VSTACK(IF(TAKE(p,1)="","Name",TAKE(p,1)), DROP(p,1))
)Excel solution 18 for Merge and Sort Name Rows, proposed by Milan Shrimali:
=let(tbl,
let(
data,
byrow(
a2:e7,
lambda(
x,
& arrayformula(
if(
x<>"",
arrayformula(
choosecols(
x,
1
)&"-"&x&"-"&bycol(
$a$1:$e$1,
lambda(
y,
y
)
)
),
""
)
)
)
),
col,
tocol(
data
),
rng,
arrayformula(
split(
filter(
col,
col<>""
),
"-"
)
),
filter(
rng,
choosecols(
rng,
3
)<>"name"
)
),
tb_2,
vstack(
hstack(
"name",
b1:e1
),
sort(
unique(
a2:a6
),
1,
1
)
),
fnl,
iferror(byrow(choosecols(
tb_2,
1
),
lambda(x,
bycol(chooserows(
tb_2,
1
),
lambda(y,
join(",",
sort(filter(choosecols(
tbl,
2
),
(choosecols(
tbl,
3
)=y)*(choosecols(
tbl,
1
)=x)),
1,
1)))))),
""),
iferror(
tb_2,
fnl
))Solving the challenge of Merge and Sort Name Rows with Python
_x000D_Python solution 1 for Merge and Sort Name Rows, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "606 Merge Rows.xlsx"
input = pd.read_excel(path, usecols="A:E", nrows=7)
test = pd.read_excel(path, usecols="A:E", skiprows=9, nrows=4).fillna("")
result = input.groupby('Name').agg(lambda x: ', '.join(sorted(x.dropna().astype(str)))).reset_index()
result = result.sort_values(by='Name')
print(result.equals(test)) # True
Python solution 2 for Merge and Sort Name Rows, proposed by Abdallah Ally:
import pandas as pd
file_path = 'Excel_Challenge_606 - Merge Rows.xlsx'
df = pd.read_excel(file_path, usecols='A:E', nrows=6)
# Perform data manipulation
df = (
df
.groupby('Name').agg(lambda x: ', '.join(sorted(filter(pd.notna, x))))
.reset_index()
)
df
Solving the challenge of Merge and Sort Name Rows with Python in Excel
_x000D_Python in Excel solution 1 for Merge and Sort Name Rows, proposed by Alejandro Campos:
sorted_df = (xl("A1:E7", headers=True).fillna('')
.groupby('Name', as_index=False)
.agg(lambda x: ', '.join(sorted(set(x), key=lambda y: (y == '', y))).rstrip(', '))
.sort_values('Name', ignore_index=True))
sorted_df
Python in Excel solution 2 for Merge and Sort Name Rows, proposed by Aditya Kumar Darak 🇮🇳:
data = xl("A1:E7", headers=True)
result = data.groupby("Name").agg(lambda x: ", ".join(sorted(x.dropna()))).reset_index()
result
Python in Excel solution 3 for Merge and Sort Name Rows, proposed by Aditya Kumar Darak 🇮🇳:
data = xl("A1:E7", headers=True)
data = data.melt(id_vars=["Name"]).dropna()
result = data.pivot_table(
"value", "Name", "variable", lambda x: ", ".join(sorted(x)), ""
)
result
Python in Excel solution 4 for Merge and Sort Name Rows, proposed by Anshu Bantra:
df = xl("A1:E7", headers=True)
df.groupby('Name').agg(lambda x: ', '.join(sorted(filter(None, x)))).reset_index()Solving the challenge of Merge and Sort Name Rows with R
_x000D_R solution 1 for Merge and Sort Name Rows, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/606 Merge Rows.xlsx"
input = read_excel(path, range = "A1:E7")
test = read_excel(path, range = "A10:E13") %>% replace(is.na(.), "")
result = input %>%
summarise(across(everything(), ~paste(sort(.), collapse = ", ")), .by = Name) %>%
arrange(Name)
all.equal(result, test, check.attributes = FALSE)
# [1] TRUE