Given multiple columns, concatenate them based on a predefined pattern.
📌 Challenge Details and Links
Challenge Number: 189
Challenge Difficulty: ⭐⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Column Combining! Part 1 with Power Query
Power Query solution 1 for Column Combining! Part 1, proposed by Zoran Milokanović:
let
Source = Table.ToRows(Excel.CurrentWorkbook(){[Name = "Input"]}[Content]),
_ = List.Transform(
Source,
each Text.Combine(
List.ReplaceMatchingItems(
Text.ToList(_{3}),
{{"F", _{0} ?? ""}, {"M", _{1} ?? ""}, {"L", _{2} ?? ""}}
)
)
)
in
_Power Query solution 2 for Column Combining! Part 1, proposed by Brian Julius:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
FormatName = Table.SelectColumns(
Table.AddColumn(
Source,
"Result",
each [
x = Text.Length([Middle Name]),
y = ", ",
z = " ",
q = "-",
p = [Pattern],
a = [First Name],
b = if x = 1 then Text.Upper([Middle Name]) & "." else [Middle Name],
c = [Last Name],
d =
if p = "L, F M" then
c & y & a & z & b
else if p = "F-L" then
a & z & c
else if p = "F M L" then
a & z & b & z & c
else if p = "LF" then
c & a
else if p = "F-ML" then
a & q & b & c
else
"Error"
][d]
),
{"Result"}
)
in
FormatNamePower Query solution 3 for Column Combining! Part 1, proposed by Eric Laforce:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Transfom = Table.TransformRows(
Source,
each Text.Combine(
List.ReplaceMatchingItems(
Text.ToList([Pattern]),
List.Zip({Text.ToList("FML"), Record.ToList(_)})
)
)
),
Result = Table.FromList(Transfom, Splitter.SplitByNothing(), {"Custom Format"})
in
ResultPower Query solution 4 for Column Combining! Part 1, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
T = Table.ReplaceValue(S,null," ",Replacer.ReplaceValue,{"First Name","Middle Name","Last Name"}),
U =
hashtag
#table({"O","N"},{{"F","1"},{"M","2"},{"L","3"}}),
V = List.Accumulate(List.Positions(U[N]),T,(s,c)=>Table.ReplaceValue(s,U[O]{c},U[N]{c},Replacer.ReplaceText,{"Pattern"})),
W = Table.ToRows(V),
X = List.Transform(W,List.RemoveLastN),
Fx = (x)=> let
a = Table.FromRows(List.Zip({{"1","2","3"},x}),{"O","N"}),
Fy = (y)=> let
b = Table.FromRows({y}),
c = List.Accumulate(List.Positions(a[N]),b,(s,c)=>Table.ReplaceValue(s,a[O]{c},a[N]{c},Replacer.ReplaceText,{"Column4"}))[[Column4]]
in c,
d = Table.Combine(List.Transform(W, each Fy(_)))
in d,
Y = List.Transform(X, each Fx(_)),
Z = List.Generate(()=>[i=0], each [i]Power Query solution 5 for Column Combining! Part 1, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Sol = Table.AddColumn(Source, "Custom Format", (x)=>
let
a = x[Pattern],
b = Text.ToList(Text.Remove(a, {" ", ",", "-"})),
c = List.Transform(b, each {_, Table.SelectRows(Record.ToTable(x),
(y)=> Text.StartsWith(y[Name],_))[Value]{0}}),
d = Text.Combine(List.ReplaceMatchingItems(Text.ToList(a), c))
in d)[[Custom Format]]
in
SolPower Query solution 6 for Column Combining! Part 1, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.AddColumn(
A,
"Result",
each [
a = List.Transform(Record.ToList(_), (z) => if z = null then "" else z),
b = List.Accumulate(
List.Zip({{"F", "M", "L"}, {"1", "2", "3"}}),
[Pattern],
(x, y) => Text.Replace(x, y{0}, y{1})
),
c = List.Accumulate(
List.Zip({{"1", "2", "3"}, {a{0}, a{1}, a{2}}}),
b,
(v, w) => Text.Replace(v, w{0}, w{1})
)
][c]
)
in
BPower Query solution 7 for Column Combining! Part 1, proposed by Yaroslav Drohomyretskyi:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
R = Table.AddColumn(Source, "Custom Format", each
Text.Combine(List.ReplaceMatchingItems(Text.ToList([Pattern]),
{{"F",_[First Name]},{"M",_[Middle Name]},{"L",_[Last Name]}}
)))[Custom Format]
in
R
Even without (??"")Power Query solution 8 for Column Combining! Part 1, proposed by Meganathan Elumalai:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Result = Table.AddColumn(
Source,
"Result",
each [
Lst = {{"L", [Last Name]}, {"F", [First Name]}, {"M", [Middle Name]}},
fin = Text.Combine(
List.Combine(
List.Transform(Text.ToList([Pattern]), (f) => List.ReplaceMatchingItems({f}, Lst))
)
)
][fin]
)[[Result]]
in
ResultPower Query solution 9 for Column Combining! Part 1, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Replacements = {{"F", "First Name"}, {"M", "Middle Name"}, {"L", "Last Name"}},
Res = Table.AddColumn(Source, "Custom Format", Fun)[[Custom Format]],
Fun = each [
A = Text.ToList([Pattern]),
B = List.ReplaceMatchingItems(A, Replacements),
C = List.Transform(B, (x) => try Record.Field(_, x) otherwise x),
D = Text.Combine(C)
][D]
in
ResPower Query solution 10 for Column Combining! Part 1, proposed by Aleksandr Mynka:
let
src = Excel.CurrentWorkbook(){[Name = "SourceTable"]}[Content],
dic = [F = "First Name", M = "Middle Name", L = "Last Name"],
COL_NAME = "Custom Format",
getReplacePairs = (rec) =>
List.Zip(
{
Record.FieldNames(dic),
List.Transform(Record.FieldValues(dic), (col) => Record.Field(rec, col))
}
),
f = (rec) =>
Text.Combine(List.ReplaceMatchingItems(Text.ToList(rec[Pattern]), getReplacePairs(rec))),
res = Table.SelectColumns(Table.AddColumn(src, COL_NAME, each f(_)), COL_NAME)
in
resPower Query solution 11 for Column Combining! Part 1, proposed by Alexandre Garcia:
let
H = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
P = List.Transform(Table.ColumnNames(H), each Text.Start(_,1)),
L = (w,x,y,z)=> List.Accumulate(List.Zip({w,{"0".."3"}}), x, (s,c)=> Text.Replace(s, c{y},c{z} ?? "")),
C = Table.AddColumn(H, "Custom Format", each L(Record.ToList(_), L(P, [Pattern], 0, 1), 1,0))[[Custom Format]]
in CPower Query solution 12 for Column Combining! Part 1, proposed by Sahan Jayasuriya:
let
Source = Excel.CurrentWorkbook(){[Name = "Data"]}[Content],
CustFormat = Table.AddColumn(
Source,
"Custom Format",
each [
a = Text.ToList([Pattern]),
b = List.Transform(
a,
(x) =>
if x = "F" then
Record.FieldValues(Record.SelectFields(_, "First Name")){0}
else if x = "M" then
Record.FieldValues(Record.SelectFields(_, "Middle Name")){0}
else if x = "L" then
Record.FieldValues(Record.SelectFields(_, "Last Name")){0}
else
x
),
c = Text.Combine(b)
][c]
)[[Custom Format]]
in
CustFormatPower Query solution 13 for Column Combining! Part 1, proposed by Vida Vaitkunaite:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Custom = Table.AddColumn(
Source,
"Custom Format",
each Text.Combine(
List.Transform(
Text.ToList([Pattern]),
(x) =>
if x = "F" then
[First Name]
else if x = "M" then
[Middle Name]
else if x = "L" then
[Last Name]
else
x
),
""
)
),
Final = Table.SelectColumns(Custom, {"Custom Format"})
in
Final //Last row result was not supposed to have commaSolving the challenge of Column Combining! Part 1 with Excel
Excel solution 1 for Column Combining! Part 1, proposed by Bo Rydobon 🇹🇭:
=REGEXREPLACE(
E3:E7,
"(F)|(M)|(L)",
"${1:+"&B3:B7&"}${2:+"&C3:C7&"}${3:+"&D3:D7&"}"
)Excel solution 2 for Column Combining! Part 1, proposed by 🇰🇷 Taeyong Shin:
=REGEXREPLACE(
E3:E7,
"(F)|(M)|(L)",
BYROW(
"${"&{1,
2,
3}&":+"&B3:D7&"}",
CONCAT
)
)Excel solution 3 for Column Combining! Part 1, proposed by 🇵🇪 Ned Navarrete C.:
=MAP(
E3:E7,
LAMBDA(
r,
REDUCE(
"",
REGEXEXTRACT(
r,
".",
1
),
LAMBDA(
a,
v,
CONCAT(
a,
XLOOKUP(
v,
LEFT(
B2:D2
),
TAKE(
r:B7,
1,
3
),
v
)
)
)
)
)
)Excel solution 4 for Column Combining! Part 1, proposed by Julian Poeltl:
=BYROW(
B3:E7,
LAMBDA(
A,
LET(
P,
TAKE(
A,
,
-1
),
SP,
MID(
P,
SEQUENCE(
LEN(
P
)
),
1
),
CONCAT(
IFERROR(
XLOOKUP(
SP&"*",
$B$2:$D$2,
TAKE(
A,
,
3
),
,
2
),
SP
)
)
)
)
)Excel solution 5 for Column Combining! Part 1, proposed by Kris Jaganah:
=BYROW(
B3:E7,
LAMBDA(
x,
LET(
a,
TAKE,
b,
a(
x,
,
-1
),
c,
REGEXEXTRACT(
b,
"[A-Z]",
1
),
d,
XLOOKUP(
c,
B2:D2,
a(
x,
,
3
),
,
3
),
e,
EXPAND(
IFERROR(
TEXTSPLIT(
b,
c,
,
1
),
""
),
,
COLUMNS(
d
),
""
),
CONCAT(
IFNA(
d&e,
d
)
)
)
)
)Excel solution 6 for Column Combining! Part 1, proposed by Sunny Baggu:
=BYROW( B3:E7, LAMBDA(
a, LET(
_a,
TAKE(
a,
,
-1
),
_b,
DROP(
a,
,
-1
),
_m,
MID(
_a,
SEQUENCE(
LEN(
_a
)
),
1
),
TEXTJOIN(
"",
0,
IFERROR(
XLOOKUP(
_m,
LEFT(
B2:D2
),
_b
),
_m
)
)
) ))Excel solution 7 for Column Combining! Part 1, proposed by Asheesh Pahwa:
=REDUCE(
H2,
SEQUENCE(
5
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
I,
INDEX(
B3:E7,
y,
),
t,
TOCOL(
DROP(
I,
,
-1
)
),
p,
{"F";"M";"L"},
_t,
TAKE(
I,
,
-1
),
m,
MID(
_t,
SEQUENCE(
LEN(
_t
)
),
1
),
CONCAT(
IFERROR(
VLOOKUP(
m,
HSTACK(
p,
t
),
2,
0
),
m
)
)
)
)
)
)Excel solution 8 for Column Combining! Part 1, proposed by ferhat CK:
=MAP(
SEQUENCE(
5
),
E3:E7,
LAMBDA(
x,
y,
TAKE(
SCAN(
"",
REGEXEXTRACT(
y,
".",
1
),
LAMBDA(
a,
v,
IFERROR(
a&CHOOSECOLS(
CHOOSEROWS(
B3:D7,
x
),
XMATCH(
v,
{"F",
"M",
"L"}
)
),
a&v
)
)
),
,
-1
)
)
)Excel solution 9 for Column Combining! Part 1, proposed by Hamidi Hamid:
=LET(
tu,
B3:D7,
st,
SEQUENCE(
,
COLUMNS(
tu
)
),
_s,
SEQUENCE(
ROWS(
tu
)
),
sb,
SUBSTITUTE,
b,
TOCOL(
tu
),
v,
{"F",
"M",
"L"},
k,
_s&v,
c,
TOCOL(
k
),
ee,
MID(
sb(
sb(
sb(
E3:E7,
" ",
""
),
"-",
),
",",
),
st,
1
),
es,
_s&ee,
ds,
TOCOL(
es
),
j,
MID(
sb(
sb(
sb(
E3:E7,
"F",
""
),
"M",
),
"L",
),
st,
1
),
by,
BYROW(
WRAPROWS(
XLOOKUP(
ds,
c,
b,
""
),
3
)&j,
CONCAT
),
by
)Excel solution 10 for Column Combining! Part 1, proposed by Hussein SATOUR:
=BYROW(
B3:E7,
LAMBDA(
y,
CONCAT(
MAP(
REGEXEXTRACT(
TAKE(
y,
,
-1
),
".",
1
),
LAMBDA(
x,
XLOOKUP(
x,
{"F",
"M",
"L"},
TAKE(
y,
,
3
),
x
)
)
)
)
)
)Excel solution 11 for Column Combining! Part 1, proposed by Md. Zohurul Islam:
=LET(
u,
B3:D7,
v,
LEFT(
B2:D2
),
w,
E3:E7,
z,
MAP(
SEQUENCE(
COUNTA(
w
)
),
LAMBDA(
x,
LET(
a,
INDEX(
w,
x
),
b,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
d,
IFERROR(
XLOOKUP(
b,
v,
CHOOSEROWS(
u,
x
)
),
b
),
e,
CONCAT(
d
),
e
)
)
),
z
)Excel solution 12 for Column Combining! Part 1, proposed by Meganathan Elumalai:
=MAP(
B3:B7,
C3:C7,
D3:D7,
E3:E7,
LAMBDA(
a,
b,
c,
d,
LET(
e,
MID(
d,
SEQUENCE(
LEN(
d
)
),
1
),
CONCAT(
IF(
e="F",
a,
IF(
e="M",
b,
IF(
e="L",
c,
e
)
)
)
)
)
)
)Excel solution 13 for Column Combining! Part 1, proposed by Pieter de B.:
=BYROW(
B3:E7,
LAMBDA(
b,
LET(
e,
DROP(
b,
,
3
),
z,
{"F",
"M",
"L"},
x,
FIND(
z,
e
),
y,
TEXTSPLIT(
e,
z,
,
1
),
q,
TOCOL(
IFS(
SORTBY(
x,
x
),
SORTBY(
TAKE(
b,
,
3
),
x
)
),
2
),
TEXTJOIN(
IF(
LEN(
q
)=1,
", ",
IFERROR(
y,
""
)
),
,
q
)
)
)
)Excel solution 14 for Column Combining! Part 1, proposed by Rick Rothstein:
=MAP(
B3:B7,
C3:C7,
D3:D7,
E3:E7,
LAMBDA(
b,
c,
d,
e,
LET(
x,
MID(
e,
SEQUENCE(
99
),
1
),
CONCAT(
IF(
x="F",
b,
IF(
x="M",
c,
IF(
x="L",
d,
x
)
)
)
)
)
)
)Solving the challenge of Column Combining! Part 1 with Python
Python solution 1 for Column Combining! Part 1, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "CH-189 Combining the columns.xlsx"
input = pd.read_excel(path, usecols="B:E", skiprows=1, nrows=6)
def replace_pattern(row):
return ''.join(row['First Name'] if c == 'F' else row['Last Name'] if c == 'L' else row['Middle Name'] if c == 'M' else c for c in row['Pattern'].strip())
input['Custom Format'] = input.apply(replace_pattern, axis=1)
result = input.groupby('First Name')['Custom Format'].apply(''.join).reset_index()
print(result)
Solving the challenge of Column Combining! Part 1 with Python in Excel
Python in Excel solution 1 for Column Combining! Part 1, proposed by Alejandro Campos:
df = xl("B2:E7", headers=True).fillna('')
df["Concatenated"] = df.apply(lambda r: r["Pattern"].replace("F", "{F}").replace("M", "{M}").replace("L", "{L}")
.format(F=r["First Name"], M=r["Middle Name"], L=r["Last Name"]).replace(" ", " ").strip(), axis=1)
dfSolving the challenge of Column Combining! Part 1 with R
R solution 1 for Column Combining! Part 1, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "CH-189 Combining the columns.xlsx"
input = read_excel(path, range = "B2:E7")
test = read_excel(path, range = "H2:H7")
result = input %>%
mutate(Pattern = trimws(Pattern)) %>%
separate_rows(Pattern, sep = "") %>%
mutate(repl = case_when(
Pattern == "F" ~ `First Name`,
Pattern == "L" ~ `Last Name`,
Pattern == "M" ~ `Middle Name`,
TRUE ~ Pattern
)) %>%
summarise(`Custom Format` = paste(repl, collapse = ""), .by = `First Name`)
Solving the challenge of Column Combining! Part 1 with Google Sheets
Google Sheets solution 1 for Column Combining! Part 1, proposed by Peter Krkos:
PowerQuery solution:
https://docs.google.com/spreadsheets/d/1zR5IZLz8OT76vhaPEHfsPrw8-RDKnLyyqS49IJjdhFk/edit?pli=1&gid=1221901023#gid=1221901023