Given multiple columns, concatenate them based on the order presented on the pattern column using space as a delimiter.
📌 Challenge Details and Links
Challenge Number: 199
Challenge Difficulty: ⭐⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Column Combining! Part 2 with Power Query
Power Query solution 1 for Column Combining! Part 2, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
_ = Table.TransformRows(
Source,
each
let
l = Record.ToList(_)
in
Text.Combine(List.Transform(Text.Split(l{3}, ","), each l{Number.From(_) - 1}), " ")
)
in
_Power Query solution 2 for Column Combining! Part 2, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
_ = Table.TransformRows(
Source,
each
let
l = Record.ToList(_)
in
Text.Combine(List.Transform(Expression.Evaluate("{" & l{3} & "}"), each l{_ - 1}), " ")
)
in
_Power Query solution 3 for Column Combining! Part 2, proposed by Luan Rodrigues:
let
Fonte = Table.AddColumn(
Tabela1,
"Result",
each
let
a = Text.Split([Pattern], ","),
b = Record.FieldValues(_),
c = List.Transform(a, (x) => Number.From(x) - 1),
d = Text.Combine(List.Transform(c, (y) => b{y}), " ")
in
d
)[[Result]]
in
FontePower Query solution 4 for Column Combining! Part 2, proposed by Rafael González B.:
let
Source = Question_Table,
Result = Table.AddColumn(
Source,
"Custom Format",
each
let
a = Record.ToList(_),
b = List.RemoveLastN(a, 1),
c = Text.Split(List.Last(a), ","),
d = List.Transform(c, each b{Number.From(_) - 1}),
e = Text.Combine(d, " ")
in
e,
type text
)[[Custom Format]]
in
ResultPower Query solution 5 for Column Combining! Part 2, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
a = Table.ToRows(S),
Fx = (x)=> let
b = List.Transform(Text.Split(List.Last(x),","),Number.From),
c = List.RemoveLastN(x),
d = {1..List.Count(c)},
e = List.Zip({c,d}),
f = List.Sort(e,{each List.PositionOf(b,_{1})}),
g = List.Select(f, each List.ContainsAll(b,{_{1}})),
h = Text.Combine(List.Transform(g, each _{0})," "),
i = Table.FromValue(h,[DefaultColumnName="Custom Format"])
in i,
Sol = Table.Combine(List.Transform(a, each Fx(_)))
in
SolPower Query solution 6 for Column Combining! Part 2, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Sol = Table.AddColumn(Source, "Result", each
let
a = Record.ToList(_),
b = List.RemoveLastN(a),
c = List.Transform(Text.Split(List.Last(a), ","), each b{Number.From(_)-1}),
d = Text.Combine(c, " ")
in d)[[Result]]
in
SolPower Query solution 7 for Column Combining! Part 2, proposed by Kris Jaganah:
Table.AddColumn(
Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
"Custom Format",
each Text.Combine(
List.Transform(Text.Split([Pattern], ","), (v) => Record.ToList(_){Number.From(v) - 1}),
" "
)
)Power Query solution 8 for Column Combining! Part 2, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Result = Table.AddColumn(
Source,
"Result",
each [
a = Record.ToList(_),
b = List.Transform(Text.Split(a{3}, ","), (x) => a{Number.From(x) - 1}),
c = Text.Combine(b, " ")
][c]
)[[Result]]
in
ResultPower Query solution 9 for Column Combining! Part 2, proposed by CA Raghunath Gundi:
able.AddColumn(
Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
"Custom Format",
each Text.Combine(
List.ReplaceMatchingItems(
Text.Split([Pattern], ","),
{{"3", [Last Name]}, {"2", [Middle Name]}, {"1", [First Name]}}
),
" "
)
)[[Custom Format]]Power Query solution 10 for Column Combining! Part 2, proposed by Meganathan Elumalai:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Result = Table.AddColumn(
Source,
"Custom Format",
each Text.Combine(
List.Transform(Text.Split([Pattern], ","), (f) => Record.ToList(_){Number.From(f) - 1}),
" "
)
)[[Custom Format]]
in
ResultPower Query solution 11 for Column Combining! Part 2, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Res = Table.FromList(Table.ToRows(Source), Fun, {"Custom Format"}),
Fun = each [
A = List.Transform(Text.Split(_{3}, ","), (x) => _{Number.From(x) - 1}),
B = {Text.Combine(A, " ")}
][B]
in
ResPower Query solution 12 for Column Combining! Part 2, proposed by Glyn Willis:
let
cols = List.Buffer(Table.ColumnNames(Source)),
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Added Custom" = Table.AddColumn(
Source,
"Result",
each Text.Combine(
List.Transform(
Text.Split([Pattern], ","),
(x) => Record.FieldOrDefault(_, cols{Number.From(x) - 1})
),
" "
),
type text
)[[Result]]
in
#"Added Custom"Power Query solution 13 for Column Combining! Part 2, proposed by Sahan Jayasuriya:
let
Source = Excel.CurrentWorkbook(){[Name = "Data"]}[Content],
RowlevelTransform = Table.TransformRows(
Source,
each [
a = List.Transform(Text.Split(_[Pattern], ","), (x) => Number.From(x)),
b = List.RemoveLastN(Record.ToList(_), 1),
c = List.Transform(a, (x) => b{x - 1}),
d = {Text.Combine(c, " ")}
][d]
),
Result = Table.FromRows(RowlevelTransform, {"Result"})
in
ResultPower Query solution 14 for Column Combining! Part 2, proposed by Tyler N.:
Table.FromColumns(
{
List.Transform(
Table.ToRows(YourTable),
(x) =>
Text.Combine(List.Transform(Text.Split(List.Last(x), ","), (y) => x{Int8.From(y) - 1}), " ")
)
},
{"Custom Format"}
)Power Query solution 15 for Column Combining! Part 2, proposed by Vida Vaitkunaite:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Custom = Table.AddColumn(
Source,
"Custom Format",
each
let
a = List.RemoveLastN(Record.FieldValues(_)),
b = Text.Split([Pattern], ","),
c = Text.Combine(List.Transform(b, (x) => a{Number.From(x) - 1}), " ")
in
c
)[[Custom Format]]
in
CustomSolving the challenge of Column Combining! Part 2 with Excel
Excel solution 1 for Column Combining! Part 2, proposed by Oscar Mendez Roca Farell:
=MAP(
E3:E7,
LAMBDA(
b,
CONCAT(
INDEX(
TAKE(
B3:b,
1
),
TEXTSPLIT(
b,
","
)
)&" "
)
)
)
A little bit shorter applying great idea of John Jairo Vergara Domínguez:
=MAP(
E3:E7,
LAMBDA(
b,
CONCAT(
INDEX(
B7:b,
,
TEXTSPLIT(
b,
","
)
)&" "
)
)
)Excel solution 2 for Column Combining! Part 2, proposed by Kris Jaganah:
=BYROW(
B3:E7,
LAMBDA(
x,
TEXTJOIN(
" ",
,
INDEX(
DROP(
x,
,
-1
),
,
--TEXTSPLIT(
TAKE(
x,
,
-1
),
","
)
)
)
)
)Excel solution 3 for Column Combining! Part 2, proposed by John Jairo Vergara Domínguez:
=MAP(
E3:E7,
LAMBDA(
x,
TEXTJOIN(
" ",
,
INDEX(
B7:x,
,
TEXTSPLIT(
x,
","
)
)
)
)
)Excel solution 4 for Column Combining! Part 2, proposed by Ivan William:
=MAP(
E3:E7,
LAMBDA(
v,
TEXTJOIN(
" ",
,
CHOOSECOLS(
TAKE(
B7:v,
1,
3
),
--TEXTSPLIT(
v,
","
)
)
)
)
)
#Reduce
=DROP(
REDUCE(
0,
E3:E7,
LAMBDA(
x,
v,
VSTACK(
x,
TEXTJOIN(
" ",
,
CHOOSECOLS(
TAKE(
B7:v,
1,
3
),
--TEXTSPLIT(
v,
","
)
)
)
)
)
),
1
)Excel solution 5 for Column Combining! Part 2, proposed by Sunny Baggu:
=BYROW( B3:E7, LAMBDA(
a, TEXTJOIN(
" ",
1,
INDEX(
DROP(
a,
,
-1
),
--TEXTSPLIT(
TAKE(
a,
,
-1
),
","
)
)
) ))Excel solution 6 for Column Combining! Part 2, proposed by Sunny Baggu:
=MAP( SEQUENCE(
ROWS(
B3:E7
)
), LAMBDA(
a, TEXTJOIN(
" ",
,
XLOOKUP(
TEXTSPLIT(
INDEX(
E3:E7,
a,
),
","
) + 0,
SEQUENCE(
,
3
),
INDEX(
B3:D7,
a,
)
)
) ))Excel solution 7 for Column Combining! Part 2, proposed by abdelaziz allam:
=BYROW(
B3:E7,
LAMBDA(
a,
CONCAT(
" "&INDEX(
TAKE(
a,
,
3
),
TEXTSPLIT(
TAKE(
a,
,
-1
),
","
)
)
)
)
)Excel solution 8 for Column Combining! Part 2, proposed by Bilal Mahmoud kh.:
=BYROW(B3:E7,
LAMBDA(r,
TEXTJOIN(" ",
,
MAP(TEXTSPLIT((TAKE(
r,
,
-1
)),
,
","),
LAMBDA(
n,
CHOOSECOLS(
r,
n
)
)))))Excel solution 9 for Column Combining! Part 2, proposed by CA Mohit Saxena:
=MAP(
E3:E7,
LAMBDA(
a,
TEXTJOIN(
" ",
INDEX(
OFFSET(
a,
,
-3,
1,
3
),
,
TEXTSPLIT(
a,
","
)
)
)
)
)Excel solution 10 for Column Combining! Part 2, proposed by CA Raghunath Gundi:
=BYROW(
B3:E7,
LAMBDA(
a,
LET(
name,
DROP(
a,
,
-1
),
pat,
TEXTSPLIT(
TAKE(
a,
,
-1
),
,
","
),
TEXTJOIN(
" ",
TRUE,
INDEX(
name,
pat
)
)
)
)
)Excel solution 11 for Column Combining! Part 2, proposed by Eddy Wijaya:
=MAP(
E3:E7,
LAMBDA(
m, LET(
p,
TEXTSPLIT(
m,
","
),
TEXTJOIN(
" ",
,
INDEX(
OFFSET(
m,
,
-3,
1,
3
),
1,
p
)
)
)
)
)Excel solution 12 for Column Combining! Part 2, proposed by Fausto Bier:
=BYROW(
B3:E7,
LAMBDA(
x,
TEXTJOIN(
" ",
1,
INDEX(
DROP(
x,
,
-1
),
TEXTSPLIT(
TAKE(
x,
,
-1
),
","
)
)
)
)
)Excel solution 13 for Column Combining! Part 2, proposed by Gerson Pineda:
=BYROW(
B3:E7,
LAMBDA(
x,
TEXTJOIN(
" ",
,
CHOOSECOLS(
x,
--TEXTSPLIT(
TAKE(
x,
,
-1
),
","
)
)
)
)
)Excel solution 14 for Column Combining! Part 2, proposed by Hussein SATOUR:
=BYROW(
B3:E7,
LAMBDA(
x,
TEXTJOIN(
" ",
,
CHOOSECOLS(
TAKE(
x,
,
3
),
--TEXTSPLIT(
TAKE(
x,
,
-1
),
","
)
)
)
)
)Excel solution 15 for Column Combining! Part 2, proposed by Leonid Koyfman:
=SUBSTITUTE( REGEXREPLACE(
E3:E7,
"(1)|(2)|(3)",
"
${1:+"&B3:B7&"}
${2:+"&C3:C7&"}
${3:+"&D3:D7&"}
"
),
",",
" "
)Excel solution 16 for Column Combining! Part 2, proposed by Md. Zohurul Islam:
=LET(
u,
B3:D7,
v,
E3:E7,
w,
SEQUENCE(
ROWS(
u
)
),
I,
INDEX,
cc,
CHOOSECOLS, z,
MAP(
w,
LAMBDA(
x,
LET(
a,
--TEXTSPLIT(
I(
v,
x,
),
","
),
TEXTJOIN(
" ",
,
cc(
I(
u,
x,
),
a
)
)
)
)
), z
)Excel solution 17 for Column Combining! Part 2, proposed by Meganathan Elumalai:
=BYROW(
B3:E7,
LAMBDA(
x,
LET(
a,
TEXTSPLIT(
TAKE(
x,
,
-1
),
","
),
TEXTJOIN(
" ",
,
INDEX(
x,
a
)
)
)
)
)Excel solution 18 for Column Combining! Part 2, proposed by Milan Shrimali:
=BYROW(
B3:E7,
LAMBDA(
X,
LET(
A,
TOCOL(
SPLIT(
CHOOSECOLS(
X,
4
),
","
)
),
B,
HSTACK(
SEQUENCE(
COUNT(
ARRAYFORMULA(
IF(
ISBLANK(
CHOOSECOLS(
X,
1,
2,
3
)
),
1,
1
)
)
),
1,
1,
1
),
TOCOL(
CHOOSECOLS(
X,
1,
2,
3
)
)
),
JOIN(
" ",
BYROW(
A,
LAMBDA(
X,
FILTER(
CHOOSECOLS(
B,
2
),
CHOOSECOLS(
B,
1
)=X
)
)
)
)
)
)
)Excel solution 19 for Column Combining! Part 2, proposed by Nicolas Micot:
=JOINDRE.TEXTE(
" ";
VRAI;
CHOISIRCOLS(
B3:D3;
FRACTIONNER.TEXTE(
E3;
;
","
)+0
)
)Excel solution 20 for Column Combining! Part 2, proposed by Peter Bartholomew:
= BYROW(
NameTable,
SORTNAMEλ
)
where
SORTNAMEλ
= LAMBDA(
record, LET( name,
TAKE(
record,
,
3
), pattern,
TAKE(
record,
,
-1
), order,
TEXTSPLIT(
pattern,
","
), result,
INDEX(
name,
order
), TEXTJOIN(
" ",
1,
result
) ))Excel solution 21 for Column Combining! Part 2, proposed by Pieter de B.:
=BYROW(
B3:E7,
LAMBDA(
b,
TEXTJOIN(
" ",
,
INDEX(
b,
TEXTSPLIT(
DROP(
b,
,
3
),
","
)
)
)
)
)Excel solution 22 for Column Combining! Part 2, proposed by Rick Rothstein:
=BYROW(
B3:E7,
LAMBDA(
r,
TEXTJOIN(
" ",
,
INDEX(
r,
,
TEXTSPLIT(
TAKE(
r,
,
-1
),
","
)
)
)
)
)Excel solution 23 for Column Combining! Part 2, proposed by Seokho MOON:
=BYROW(
B3:E7,
LAMBDA(
x,
TEXTJOIN(
" ",
,
INDEX(
x,
TEXTSPLIT(
INDEX(
x,
4
),
","
)
)
)
)
)Excel solution 24 for Column Combining! Part 2, proposed by Surendra Reddy:
=BYROW(
B3:E7,
LAMBDA(
x,
TEXTJOIN(
" ",
,
CHOOSECOLS(
x,
TEXTSPLIT(
TAKE(
x,
,
-1
),
","
)*1
)
)
)
)Solving the challenge of Column Combining! Part 2 with Python
Python solution 1 for Column Combining! Part 2, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "CH-199 Combining the columns.xlsx"
input = pd.read_excel(path, usecols="B:E", skiprows=1, nrows=6)
test = pd.read_excel(path, usecols="H", skiprows=1, nrows=6)
def combine_columns(row):
first_name, middle_name, last_name, pattern = row
order = list(map(int, pattern.split(',')))
names = [first_name, middle_name, last_name]
return ' '.join([names[i-1] for i in order])
input['Custom Format'] = input.apply(combine_columns, axis=1)
result = input['Custom Format']
print(all(input['Custom Format'] == test['Custom Format'])) # True_x000D_
Python solution 2 for Column Combining! Part 2, proposed by Luan Rodrigues:
import pandas as pd