Split and align the data as shown.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 681
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Split And Align Columns with Power Query
Power Query solution 1 for Split And Align Columns, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content][Data],
B = Table.Combine(
List.Transform(
A,
(v) =>
[
a = Text.Split(v, ", "),
b = List.Positions(a),
c = Table.FromRows(
List.Split(
List.TransformMany(b, each b, (x, y) => if x = y then a{y} else null),
List.Count(a)
)
)
][c]
)
)
in
BPower Query solution 2 for Split And Align Columns, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
AddCol = Table.AddColumn(Source, "Tbl", each Table.FromColumns(Fun([Data]))),
Fun = (x) =>
[
A = Text.PositionOf(x, ", ", 2),
B = {List.Repeat({null}, List.Count(A)) & {Text.Range(x, List.Last(A) + 2)}},
C = if A = {} then {{x}} else @Fun(Text.Start(x, List.Last(A))) & B
][C],
Res = Table.Combine(AddCol[Tbl])
in
ResPower Query solution 3 for Split And Align Columns, proposed by Meganathan Elumalai:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Result = Table.Combine(
List.Transform(
Source[Data],
(f) =>
[
l = Text.Split(f, ", "),
fin = Table.FromRows(
List.Transform(
List.Positions(l),
(x) => List.Repeat({null}, x) & {l{x}} & List.Repeat({null}, List.Count(l) - x - 1)
)
)
][fin]
)
)
in
ResultPower Query solution 4 for Split And Align Columns, proposed by Antriksh Sharma:
let
Source = Table,
Split = List.Transform(
Source[Data],
each
let
a = Text.Split(_, ","),
b = List.Positions(a),
c = List.Zip({a, b})
in
c
),
Result = List.Transform(
{0 .. List.Count(Split) - 1},
(x) =>
List.Accumulate(
Split{x},
Table.FromColumns({{0 .. List.Count(Split{x}) - 1}}, type table [n = number]),
(s, c) => Table.AddColumn(s, Text.From(c{1}), each if [n] = c{1} then c{0} else null)
)
),
Combine = Table.PrefixColumns(Table.RemoveColumns(Table.Combine(Result), "n"), "Column")
in
CombinePower Query solution 5 for Split And Align Columns, proposed by Peter Krkos:
PowerQuerz solution:
= Table.Combine(Table.AddColumn(Source, "L", each
[ a = Text.Split([Data], ", "),
b = Table.Combine(List.Transform(List.Zip({List.Transform(List.Positions(a), (x)=> List.Repeat({null}, x)), List.Split(a, 1)}), (y)=>
[ b1 = List.Combine(y),
b2 = Table.FromRecords({Record.Combine(List.Transform(List.Positions(b1), (z)=> Record.AddField([], "Column" & Text.From(z+1), b1{z})))})
][b2]))
][b])[L])
Power Query solution 6 for Split And Align Columns, proposed by Peter Krkos:
v2:
= Table.Combine(List.TransformMany(List.Transform(Source[Data], each Text.Split(_, ", ")),
each List.Transform(_, (x)=> List.Repeat({null}, List.PositionOf(_, x)) & {x}),
(x,y)=> Table.FromRows({y})))Power Query solution 7 for Split And Align Columns, proposed by Melissa de Korte:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
A = Table.Combine(
Table.TransformRows(
S,
each Table.FromColumns(
[
t = Text.Split([Data], ","),
i = List.Positions(t),
a = List.Transform(i, (x) => List.Repeat({null}, x) & {t{x}})
][a]
)
)
)
in
ASolving the challenge of Split And Align Columns with Excel
Excel solution 1 for Split And Align Columns, proposed by Rick Rothstein:
=DROP(
REDUCE(
0,
A2:A5,
LAMBDA(
a,
x,
LET(
t,
TEXTSPLIT(
x,
,
", "
),
IFNA(
VSTACK(
a,
LEFT(
t,
MUNIT(
ROWS(
t
)
)
)
),
""
)
)
)
),
1
)Excel solution 2 for Split And Align Columns, proposed by John V.:
=DROP(
REDUCE(
0,
A2:A5,
LAMBDA(
a,
v,
LET(
i,
TEXTSPLIT(
v,
", "
),
IFNA(
VSTACK(
a,
IFS(
i=TOCOL(
i
),
i
)
),
""
)
)
)
),
1
)Excel solution 3 for Split And Align Columns, proposed by 🇰🇷 Taeyong Shin:
=LET(n,SEQUENCE(,9),r,REGEXREPLACE(A2:A5,REPT("(?:, )?(.)?",MAX(n)),"$"&n),F,LAMBDA(x,TOCOL(IFS(r>"",x),2)),s,F(n),DROP(PIVOTBY(SEQUENCE(ROWS(s)),s,F(r),CONCAT,,0,,0),1,1))Excel solution 4 for Split And Align Columns, proposed by Kris Jaganah:
=DROP(
REDUCE(
"",
A2:A5,
LAMBDA(
x,
y,
IFNA(
VSTACK(
x,
LET(
a,
TEXTSPLIT(
y,
,
", "
),
IF(
MUNIT(
ROWS(
a
)
),
a,
""
)
)
),
""
)
)
),
1
)Excel solution 5 for Split And Align Columns, proposed by Julian Poeltl:
=IFNA(DROP(REDUCE(0,
A2:A5,
LAMBDA(A,
B,
VSTACK(A,
LET(L,
(LEN(
B
)-LEN(
SUBSTITUTE(
B,
", ",
""
)
))/2+1,
MAKEARRAY(L,
L,
LAMBDA(C,
D,
IF(C=D,
MID(B,
(C-1)*3+1,
1),
""))))))),
1),
"")Excel solution 6 for Split And Align Columns, proposed by Timothée BLIOT:
=IFNA(DROP(REDUCE(0,A2:A5,LAMBDA(w,v,LET(A,TEXTSPLIT(v,,", "),VSTACK(w,IF(MUNIT(ROWS(A)),A,""))))),1),"")Excel solution 7 for Split And Align Columns, proposed by Duy Tùng:
=DROP(REDUCE(0,A2:A5,LAMBDA(x,v,LET(a,TEXTSPLIT(v,,", "),IFNA(VSTACK(x,IFS(MUNIT(ROWS(a)),a)),"")))),1)
=LET(S,SEQUENCE,a,TEXTSPLIT(TEXTJOIN("/",,A2:A5),", ","/"),b,TOCOL(IF(a>0,S(,COLUMNS(a))),3),REPT(TOCOL(a,3),b=S(,MAX(b))))
=LET(S,SEQUENCE,b,TEXTSPLIT(TEXTJOIN("/",,A2:A5),", ","/"),c,TOCOL(IF(b>0,S(,COLUMNS(b))),3),DROP(PIVOTBY(S(ROWS(c)),c,TOCOL(b,3),SINGLE,,0,,0),1,1))Excel solution 8 for Split And Align Columns, proposed by Sunny Baggu:
=IFNA(
DROP(
REDUCE(
"💗 all",
A2:A5,
LAMBDA(x, y,
VSTACK(
x,
LET(
_a, TEXTSPLIT(y, , ", "),
_b, SEQUENCE(ROWS(_a)) - 1,
IFNA(
DROP(
REDUCE(
"",
REPT(",", _b) & _a,
LAMBDA(a, v, VSTACK(a, TEXTSPLIT(v, ",")))
),
1
),
""
)
)
)
)
),
1
),
""
)Excel solution 9 for Split And Align Columns, proposed by LEONARD OCHEA 🇷🇴:
=DROP(IFNA(REDUCE(0,A2:A5,LAMBDA(a,x,LET(h,TEXTSPLIT(x,", "),VSTACK(a,IFS(h=TOCOL(h),h))))),""),1)
With PIVOT
=LET(d,A2:A5,w,TEXTSPLIT(CONCAT(d&", "),,", ",1),DROP(PIVOTBY(SEQUENCE(ROWS(w)),MAP(w,LAMBDA(x,(2+CONCAT(IFERROR(FIND(x,d),"")))/3)),w,SINGLE,,0,,0),1,1))Excel solution 10 for Split And Align Columns, proposed by Anshu Bantra:
= to_df(REF("A1:A5"))
data['Data'] = data['Data'].str.replace(',', '')
data['Len'] = [len(row[0]) for row in data.values]
grid = np.zeros( ( sum(data['Len']), max(data['Len']) ) , dtype=str)
agg_row = 0
for row, row_data in enumerate(data['Data']):
for col, col_data in enumerate(row_data)Excel solution 11 for Split And Align Columns, proposed by Md. Zohurul Islam:
=IFNA(DROP(REDUCE("",A2:A5,LAMBDA(x,y,LET(
a,TEXTSPLIT(y,,", "),
r,SEQUENCE(ROWS(a)),c,SEQUENCE(,ROWS(a)),
d,IF((r+c)=2*r,a,""),
VSTACK(x,d)))),1),"")Excel solution 12 for Split And Align Columns, proposed by Pieter de B.:
=DROP(REDUCE("",A2:A5,LAMBDA(a,b,LET(c,TEXTSPLIT(b,,", "),IFNA(VSTACK(a,IF(MUNIT(ROWS(c)),c,"")),"")))),1)Excel solution 13 for Split And Align Columns, proposed by Hamidi Hamid:
=DROP(IFNA(REDUCE(0,SUBSTITUTE(A2:A5,", ",),LAMBDA(a,b,VSTACK(a,LET(x,MID(b,SEQUENCE(LEN(b)),1),s,SEQUENCE(LEN(b)),d,SEQUENCE(,LEN(b)),IF(s=d,x,""))))),""),1)Excel solution 14 for Split And Align Columns, proposed by Asheesh Pahwa:
=IFNA(
DROP(
REDUCE(
"",
A2:A5,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
t,
TEXTSPLIT(
y,
,
", "
),
s,
ROWS(
t
),
m,
MUNIT(
s
),
IF(
m,
t,
""
)
)
)
)
),
1
),
""
)Excel solution 15 for Split And Align Columns, proposed by Eric Laforce:
=LET(v; $A$2:$A$5; r; REDUCE(""; v; LAMBDA(s;c; LET(a; TEXTSPLIT(c; ; ", "); rs; VSTACK(IF(MUNIT(ROWS(a)); a; "")); IFNA(VSTACK(s; rs); "")))); DROP(r; 1))Excel solution 16 for Split And Align Columns, proposed by ferhat CK:
=IFNA(DROP(REDUCE(0,A2:A5,LAMBDA(a,v,VSTACK(a,LET(n,TEXTSPLIT(v,,", "),i,ROWS(n),MAKEARRAY(i,i,LAMBDA(x,y,IF(x=y,INDEX(n,x),""))))))),1),"")Excel solution 17 for Split And Align Columns, proposed by Jaroslaw Kujawa:
=IFNA(
DROP(
REDUCE(
"";
A2:A5;
LAMBDA(
a;
x;
LET(
y;
SUBSTITUTE(
x;
", ";
""
);
VSTACK(
a;
MAKEARRAY(
LEN(
y
);
LEN(
y
);
LAMBDA(
r;
c;
IF(
r=c;
MID(
y;
r;
1
);
""
)
)
)
)
)
)
);
1
);
""
)
=IFNA(
DROP(
REDUCE(
"";
A2:A5;
LAMBDA(
a;
x;
LET(
y;
TEXTSPLIT(
x;
;
", "
);
VSTACK(
a;
MAKEARRAY(
ROWS(
y
);
ROWS(
y
);
LAMBDA(
r;
c;
IF(
r=c;
CHOOSEROWS(
y;
r
);
""
)
)
)
)
)
)
);
1
);
""
)Excel solution 18 for Split And Align Columns, proposed by Meganathan Elumalai:
=IFNA(
DROP(
REDUCE(
"",
A2:A5,
LAMBDA(
a,
v,
VSTACK(
a,
LET(
x,
TEXTSPLIT(
v,
,
& ", "
),
s,
ROWS(
x
),
IF(
MUNIT(
s
),
x,
""
)
)
)
)
),
1
),
""
)Excel solution 19 for Split And Align Columns, proposed by Eddy Wijaya:
=DROP(IFNA(REDUCE(0,A2:A5,LAMBDA(a,v,VSTACK(a,LET(
sp,TRIM(TEXTSPLIT(v,,",")),
a,COUNTA(sp),
MAKEARRAY(a,a,LAMBDA(r,c,IF(r=c,INDEX(sp,c),""))))))),""),1)Excel solution 20 for Split And Align Columns, proposed by Mihai Radu O:
=IFNA(DROP(REDUCE("",A2:A5,
LAMBDA(x,y,
LET(a,TEXTSPLIT(y,,", "),b,ROWS(a), c,MAKEARRAY(b,b,LAMBDA(r,c,IF(r=c,INDEX(a,r,),""))),
VSTACK(x,c)))),1),"")Excel solution 21 for Split And Align Columns, proposed by Edwin Tisnado:
=DROP(IFERROR(REDUCE(0,A2:A5,LAMBDA(x,y,LET(t,TEXTSPLIT(y,,", "),VSTACK(x,IF(MUNIT(ROWS(t)),t,j))))),""),1)Excel solution 22 for Split And Align Columns, proposed by Fredson Alves Pinho:
=DROP(TEXTSPLIT(REDUCE("",
A2:A5,
LAMBDA(a,
x,
a&ARRAYTOTEXT(REPT(",",
SEQUENCE(,
(LEN(
x
)+2)/3,
0))&TEXTSPLIT(
x,
", "
))&";")),
",",
";",
,
,
""),
-1)Excel solution 23 for Split And Align Columns, proposed by Ernesto Vega Castillo:
=DROP(REDUCE(0,A2:A5,LAMBDA(x,y,LET(a,TEXTSPLIT(y,","),b,IFNA(VSTACK(x,IF(MUNIT(COUNTA(a)),a)),""),IF(b=FALSE,"",b)))),1)Excel solution 24 for Split And Align Columns, proposed by CA Mohit Saxena:
=DROP(REDUCE(
"",
A3:A5,
LAMBDA(
x,
y,
IFNA(
VSTACK(
x,
LET(
t,
TEXTSPLIT(
y,
", "
),
IF(
MUNIT(
COLUMNS(
t
),
t,
""
)
)
),
""
)
)
),
1
)Solving the challenge of Split And Align Columns with Python
Python solution 1 for Split And Align Columns, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "681 Split and Align.xlsx"
input = pd.read_excel(path, usecols="A", nrows=4)
test = pd.read_excel(path, usecols="C:G", nrows=11, names=["1", "2", "3", "4", "5"])
input["RowNumber"] = input.index + 1
input = input.assign(**{"Split": input["Data"].str.split(", ")}).explode("Split")
input["row"] = range(1, len(input) + 1)
input["col"] = input.groupby("RowNumber").cumcount() + 1
input = input.pivot(index="row", columns="col", values="Split").reset_index(drop=True)
input.columns.name = None
input.columns = test.columns
print(test.equals(input)) # True
Solving the challenge of Split And Align Columns with Python in Excel
Python in Excel solution 1 for Split And Align Columns, proposed by Alejandro Campos:
data = xl("A1:A5", headers=True)["Data"]
aligned_df = pd.DataFrame([[*['']*i, c] for s in data for i,
c in enumerate(map(str.strip, s.split(',')))]).fillna('')Python in Excel solution 2 for Split And Align Columns, proposed by Aditya Kumar Darak 🇮🇳:
data = xl("A1:A5", True)["Data"]
grid = [[None] * i + [x] for row in data for i, x in enumerate(row.split(", "))]
result = pd.DataFrame(grid).fillna("")
result
Solving the challenge of Split And Align Columns with R
R solution 1 for Split And Align Columns, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/681 Split and Align.xlsx"
input = read_excel(path, range = "A1:A5")
test = read_excel(path, range = "C2:G12", col_names = c("1", "2", "3", "4", "5"))
result = input %>%
mutate(group= row_number()) %>%
separate_rows(Data, sep = ", ") %>%
mutate(col = row_number(), .by = group) %>%
mutate(row = row_number()) %>%
pivot_wider(names_from = col, values_from = Data) %>%
select(-c(group, row))
all.equal(result, test, check.attributes = FALSE) # TRUE
&&