Transpose the problem table into result table.
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 225
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Shift Rows into Columns with Power Query
Power Query solution 1 for Shift Rows into Columns, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
S = Table.FromRows(
List.TransformMany(
Table.ToRows(Table.Group(Source, {"Group", "Dept"}, {"A", each [[Emp ID], [Name]]})),
each {{_{0}, _{1}}} & Table.ToRows(_{2}),
(i, _) => _
)
)
in
SPower Query solution 2 for Shift Rows into Columns, proposed by Kris Jaganah:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Ans = Table.Combine(
Table.Group(
Source,
{"Group"},
{
"All",
each Table.DemoteHeaders(
Table.RenameColumns(_, {{"Name", [Dept]{0}}, {"Emp ID", [Group]{0}}})
)[[Column1], [Column2]]
}
)[All]
)
in
AnsPower Query solution 3 for Shift Rows into Columns, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.Combine(
Table.Group(
Source,
{"Dept", "Group"},
{
{
"A",
(x) =>
let
a = {Table.ToRows(x[[Group], [Dept]]){0}} & Table.ToRows(x[[Emp ID], [Name]]),
b = Table.FromRows(a)
in
b
}
}
)[A]
)
in
SolPower Query solution 4 for Shift Rows into Columns, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
grp = Table.Group(
Fonte,
{"Group"},
{
{
"tab",
each
let
a = _[[Emp ID], [Name]],
b = Table.RenameColumns(a, {{"Emp ID", _[Group]{0}}, {"Name", _[Dept]{0}}})
in
Table.DemoteHeaders(b)
}
}
)[tab],
res = Table.Combine(grp)
in
resPower Query solution 5 for Shift Rows into Columns, proposed by Hussein SATOUR:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Groupe = Table.Group(
Source,
{"Group", "Dept"},
{
{
"All",
each
let
a = [Group],
b = [Dept]
in
List.Combine({{List.First(List.Zip({a, b}))}, List.Zip({_[Emp ID], _[Name]})})
}
}
),
ExpendExtract = Table.TransformColumns(
Table.ExpandListColumn(Groupe, "All"),
{"All", each Text.Combine(List.Transform(_, Text.From), ",")}
),
SplitCols = Table.SplitColumn(
ExpendExtract,
"All",
Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv),
{"Column1", "Column2"}
),
RemovCols = Table.RemoveColumns(SplitCols, {"Group", "Dept"})
in
RemovColsPower Query solution 6 for Shift Rows into Columns, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Transform = List.Transform(
List.Distinct(Source[Dept]),
each [
a = Table.SelectRows(Source, (x) => x[Dept] = _),
b = {{a{0}[Group], a{0}[Dept]}} & Table.ToRows(a[[Emp ID], [Name]])
][b]
),
Result = Table.FromRows(List.Combine(Transform))
in
ResultPower Query solution 7 for Shift Rows into Columns, proposed by 🇵🇪 Ned Navarrete C.:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Grouped = Table.Combine(
Table.Group(
Source,
{"Group"},
{
"X",
each [
a = Table.ToRows(Table.Distinct([[Group], [Dept]])),
b = Table.ToRows([[Emp ID], [Name]]),
c = Table.FromRows(a & b)
][c]
}
)[X]
)
in
GroupedPower Query solution 8 for Shift Rows into Columns, proposed by Eric Laforce:
let
Source = Excel.CurrentWorkbook(){[Name = "tData225"]}[Content],
Group = Table.Group(
Source,
{"Group", "Dept"},
{
"G",
each Table.FromRows(
{Record.FieldValues(_{0}[[Group], [Dept]])} & Table.ToRows([[Emp ID], [Name]])
)
}
),
Combine = Table.Combine(Group[G])
in
CombinePower Query solution 9 for Shift Rows into Columns, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
a = Table.Group(Source, {"Dept", "Group"}, {{"T", each _}}),
b = Table.AddColumn(
a,
"C",
each
let
A = {[Group]} & [T][Emp ID],
B = {[Dept]} & [T][Name],
C = Table.FromColumns({A, B})
in
C
),
c = Table.SelectColumns(b, {"C"}),
d = Table.ExpandTableColumn(c, "C", {"Column1", "Column2"}, {"Column1", "Column2"})
in
dPower Query solution 10 for Shift Rows into Columns, proposed by Luke Jarych:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Grouped = Table.Group(
Source,
{"Group"},
{
{
"All",
each
let
a = Table.RenameColumns(_, {{"Name", [Dept]{0}}, {"Emp ID", [Group]{0}}}),
b = Table.DemoteHeaders(a)
in
b[[Column1], [Column2]]
}
}
),
Com = Table.Combine(Grouped[All])
in
ComPower Query solution 11 for Shift Rows into Columns, proposed by Sandeep Marwal:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Grouped Rows" = Table.Group(
Source,
{"Dept", "Group"},
{
{
"Count",
each Table.InsertRows(_[[Emp ID], [Name]], 0, {[Emp ID = [Group]{0}, Name = [Dept]{0}]})
}
}
),
#"Expanded Count" = Table.ExpandTableColumn(
#"Grouped Rows",
"Count",
{"Emp ID", "Name"},
{"Column1", "Column2"}
),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Count", {"Dept", "Group"})
in
#"Removed Columns"Power Query solution 12 for Shift Rows into Columns, proposed by Francesco Bianchi 🇮🇹:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Sol = Table.Combine( Table.Group(Source, {"Group"}, {{"All", each
hashtag#table(null,{{_[Group]{0},_[Dept]{0}}})&Table.FromColumns(List.FirstN(Table.ToColumns (_),2))}})[All])
in
Sol
Power Query solution 13 for Shift Rows into Columns, proposed by Sanket Doijode:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Grouped Rows" = Table.ExpandTableColumn(
Table.SelectColumns(
Table.Group(
Source,
{"Group"},
{
{
"Count",
each Table.RenameColumns(
Table.SelectColumns(
Table.InsertRows(
_,
0,
{[Emp ID = _{1}[Group], Name = _{1}[Dept], Dept = null, Group = null]}
),
{"Emp ID", "Name"}
),
{{"Emp ID", "Column1"}, {"Name", "Column2"}}
)
}
}
),
"Count"
),
"Count",
{"Column1", "Column2"}
)
in
#"Grouped Rows"Power Query solution 14 for Shift Rows into Columns, proposed by Sahan Jayasuriya:
let
Source = Excel.CurrentWorkbook(){[Name = "Data"]}[Content],
GroupedRows = Table.Group(
Source,
{"Group", "Dept"},
{{"Name List", each _[Emp ID]}, {"Emp ID LIst", each _[Name]}}
),
RowsToList = Table.ToRows(GroupedRows),
ListCombine_1 = {
List.Transform(RowsToList, each {_{0}} & _{2}),
List.Transform(RowsToList, each {_{1}} & _{3})
},
ListCombine_2 = List.Transform(ListCombine_1, each List.Combine(_)),
ColsToTable = Table.FromColumns(ListCombine_2)
in
ColsToTablePower Query solution 15 for Shift Rows into Columns, proposed by Daniel Madhadha:
let
Source = Excel.CurrentWorkbook(){[Name = "Table5"]}[Content],
#"Grouped Rows" = Table.Group(
Source,
{"Dept", "Group"},
{{"A", each _, type table [Emp ID = number, Name = text, Dept = text, Group = text]}}
),
#"Added Custom" = Table.AddColumn(
#"Grouped Rows",
"Res",
each
let
X = {[Group]} & [A][Emp ID],
Y = {[Group]} & [A][Name],
Z = Table.FromColumns({X, Y})
in
Z
),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom", {"Dept", "Group", "A"}),
#"Expanded Res" = Table.ExpandTableColumn(
#"Removed Columns",
"Res",
{"Column1", "Column2"},
{"Res.Column1", "Res.Column2"}
),
#"Promoted Headers" = Table.PromoteHeaders(#"Expanded Res", [PromoteAllScalars = true]),
#"Changed Type" = Table.TransformColumnTypes(
#"Promoted Headers",
{{"Group A", type any}, {"Group A_1", type text}}
)
in
#"Changed Type"Solving the challenge of Shift Rows into Columns with Excel
Excel solution 1 for Shift Rows into Columns, proposed by Bo Rydobon 🇹🇭:
=LET(
g,
D2:D9&"_"&C2:C9,
DROP(
REDUCE(
0,
UNIQUE(
g
),
LAMBDA(
a,
v,
VSTACK(
a,
TEXTSPLIT(
v,
"_"
),
FILTER(
A2:B9,
g=v
)
)
)
),
1
)
)Excel solution 2 for Shift Rows into Columns, proposed by Rick Rothstein:
=DROP(
REDUCE(
0,
UNIQUE(
D2:D9
),
LAMBDA(
a,
x,
LET(
f,
FILTER(
A2:D9,
D2:D9=x
),
VSTACK(
a,
INDEX(
f,
,
{4,
3}
),
TAKE(
f,
,
2
)
)
)
)
),
1
)
With headers...
=REDUCE(
"Column"&{1,
2},
UNIQUE(
D2:D9
),
LAMBDA(
a,
x,
LET(
f,
FILTER(
A2:D9,
D2:D9=x
),
VSTACK(
a,
INDEX(
f,
,
{4,
3}
),
TAKE(
f,
,
2
)
)
)
)
)Excel solution 3 for Shift Rows into Columns, proposed by 🇰🇷 Taeyong Shin:
=WRAPROWS(TOCOL(HSTACK(IFS(D2:D9<>DROP(VSTACK(0,D2:D9),-1),CHOOSECOLS(C2:D9,2,1)),A2:B9),2),2)Excel solution 4 for Shift Rows into Columns, proposed by Kris Jaganah:
=REDUCE(
"Column"&{1,
2},
UNIQUE(
D2:D9
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
a,
FILTER(
A2:D9,
D2:D9=y
),
VSTACK(
TAKE(
CHOOSECOLS(
a,
4,
3
),
1
),
TAKE(
a,
,
2
)
)
)
)
)
)Excel solution 5 for Shift Rows into Columns, proposed by Julian Poeltl:
=LET(
D,
C2:C9,
R,
WRAPROWS(
TEXTSPLIT(
TEXTJOIN(
",",
,
MAP(
UNIQUE(
D
),
LAMBDA(
A,
TEXTJOIN(
",",
,
TAKE(
FILTER(
HSTACK(
D2:D9,
D
),
D=A
),
1
),
FILTER(
A2:B9,
D=A
)
)
)
)
),
","
),
2
),
IFERROR(
R*1,
R
)
)Excel solution 6 for Shift Rows into Columns, proposed by Oscar Mendez Roca Farell:
=TEXTSPLIT(CONCAT(GROUPBY(D2:D9&"|"&C2:C9,A2:A9&"|"&B2:B9,ARRAYTOTEXT,,0)&", "),"|",", ",1)Excel solution 7 for Shift Rows into Columns, proposed by Duy Tùng:
=DROP(
REDUCE(
0,
UNIQUE(
D2:D9&"/"&C2:C9
),
LAMBDA(
x,
y,
VSTACK(
x,
VSTACK(
TEXTSPLIT(
y,
"/"
),
FILTER(
A2:B9,
D2:D9&"/"&C2:C9=y
)
)
)
)
),
1
)Excel solution 8 for Shift Rows into Columns, proposed by Sunny Baggu:
=REDUCE(
"Column" & {1,
2},
UNIQUE(
D2:D9
),
LAMBDA(
x,
y,
VSTACK(
& x,
VSTACK(
HSTACK(
y,
XLOOKUP(
y,
D2:D9,
C2:C9
)
),
FILTER(
A2:B9,
D2:D9 = y
)
)
)
)
)Excel solution 9 for Shift Rows into Columns, proposed by LEONARD OCHEA 🇷🇴:
=LET(
t,
A2:D9,
UNIQUE(
WRAPROWS(
TOCOL(
CHOOSECOLS(
t,
4,
3,
1,
2
)
),
2
)
)
)Excel solution 10 for Shift Rows into Columns, proposed by 🇵🇪 Ned Navarrete C.:
=TEXTSPLIT(TEXTJOIN("|",,BYROW(GROUPBY(D2:D9&"-"&C2:C9,"|"&A2:A9&"-"&B2:B9,CONCAT,,0),CONCAT)),"-","|")Excel solution 11 for Shift Rows into Columns, proposed by Hamidi Hamid:
=LET(
aa,
A2:A9,
bb,
B2:B9,
cc,
C2:C9,
dd,
D2:D9,
a,
IF(
aa=A1:A8,
"",
A2:A9
),
b,
IF(
bb=B1:B8,
"",
bb
),
c,
IF(
cc=C1:C8,
1/0,
C2:C9
),
d,
IF(
D1:D8=dd,
1/0,
dd
),
gu,
TOCOL(
IF(
ISBLANK(
d
),
1/0,
HSTACK(
d,
a
)
),
3
),
dc,
TOCOL(
HSTACK(
c,
b
),
3
),
HSTACK(
gu,
dc
)
)Excel solution 12 for Shift Rows into Columns, proposed by Asheesh Pahwa:
=LET(
g,
D2:D9,
u,
UNIQUE(
g
),
DROP(
REDUCE(
"",
u,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
f,
FILTER(
A2:C9,
g=y
),
t,
TAKE(
f,
,
2
),
VSTACK(
HSTACK(
y,
TAKE(
f,
1,
-1
)
),
t
)
)
)
)
),
1
)
)Excel solution 13 for Shift Rows into Columns, proposed by ferhat CK:
=LET(
a,
D2:D9,
b,
C2:C9,
c,
A2:B9,
REDUCE(
{"Column1",
"Column2"},
UNIQUE(
a
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
q,
HSTACK(
y,
UNIQUE(
FILTER(
b,
a=y
)
)
),
VSTACK(
q,
FILTER(
c,
a=y
)
)
)
)
)
)
)Excel solution 14 for Shift Rows into Columns, proposed by Ankur Sharma:
=LET(a, D2:D9 & "-" & C2:C9, TEXTSPLIT(TEXTJOIN(" : ", , MAP(UNIQUE(a), LAMBDA(z, TEXTJOIN(" : ", , z, FILTER(A2:A9 & "-" & B2:B9, a = z))))), "-", " : "))Excel solution 15 for Shift Rows into Columns, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:
=LET(
i;
D2:D9;
o;
A2:A9;
y;
LAMBDA(
q;
w;
e;
TEXTJOIN(
",";
;
IF(
q=1;
VSTACK(
e;
w
);
w
)
)
);
j;
BYROW(
VALUE(
DROP(
TRANSPOSE(
TEXTSPLIT(
TEXTJOIN(
;
;
BYROW(
MAP(
UNIQUE(
i
);
LAMBDA(
x;
MINIFS(
o;
i;
x
)
)
);
LAMBDA(
j;
TEXTJOIN(
",";
;
IF(
j=o;
1;
0
)
)&"/"
)
)
);
",";
"/"
)
);
;
-1
)
);
LAMBDA(
a;
SUM(
a
)
)
);
HSTACK(
TEXTSPLIT(
TEXTJOIN(
",";
;
MAP(
j;
o;
i;
y
)
);
;
","
);
TEXTSPLIT(
TEXTJOIN(
",";
;
MAP(
j;
B2:B9;
C2:C9;
y
)
);
;
","
)
)
)Excel solution 16 for Shift Rows into Columns, proposed by Imam Hambali:
=LET(
g,
D2:D9,
d,
C2:C9,
u,
UNIQUE(
HSTACK(
g,
d
)
),
dr,
DROP(
REDUCE(
"",
TAKE(
u,
,
1
),
LAMBDA(
x,
y,
VSTACK(
x,
TOROW(
FILTER(
A2:B9,
D2:D9=y
)
)
)
)
),
1
),
VSTACK(
F1:G1,
WRAPROWS(
TOROW(
HSTACK(
u,
dr
),
3
),
2
)
)
)Excel solution 17 for Shift Rows into Columns, proposed by Eddy Wijaya:
=LET(
d,
A2:D9,
gr,
TAKE(
d,
,
-1
),
REDUCE(
F1:G1,
UNIQUE(
gr
),
LAMBDA(
a,
v,
VSTACK(
a,
VSTACK(
HSTACK(
v,
XLOOKUP(
v,
gr,
CHOOSECOLS(
d,
3
)
)
),
DROP(
FILTER(
d,
gr=v
),
,
-2
)
)
)
)
)
)Excel solution 18 for Shift Rows into Columns, proposed by Ziad A.:
=ARRAYFORMULA(
SPLIT(
UNIQUE(
TOCOL(
{D2:D9&"❅"&C2:C9,
A2:A9&"❅"&B2:B9}
)
),
"❅"
)
)Excel solution 19 for Shift Rows into Columns, proposed by RIJESH T.:
=REDUCE(
F1:G1,
UNIQUE(
D2:D9
),
LAMBDA(
a,
b,
LET(
f,
FILTER(
A2:D9,
D2:D9=b
),
VSTACK(
a,
TAKE(
CHOOSECOLS(
f,
4,
3
),
1
),
CHOOSECOLS(
f,
1,
2
)
)
)
)
)Excel solution 20 for Shift Rows into Columns, proposed by Songglod P.:
=LET(
g,
D2:D9,
d,
HSTACK(
g,
C2:C9
),
DROP(
REDUCE(
0,
UNIQUE(
g
),
LAMBDA(
a,
v,
VSTACK(
a,
UNIQUE(
FILTER(
d,
g=v
)
),
FILTER(
A2:B9,
g=v
)
)
)
),
1
)
)Excel solution 21 for Shift Rows into Columns, proposed by Hazem Hassan:
=UNIQUE(WRAPROWS(TOCOL(HSTACK(D2:D9,C2:C9,A2:B9)),2))Solving the challenge of Shift Rows into Columns with Python
Python solution 1 for Shift Rows into Columns, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "PQ_Challenge_225.xlsx"
input = pd.read_excel(path, usecols="A:D", nrows=8)
test = pd.read_excel(path, usecols="F:G", nrows=12)
input['Id'] = input['Group'].ne(input['Group'].shift()).cumsum().astype(str)
input['Group'] = input['Group'].replace('Group A', 'GroupA')
r1_1 = input.iloc[:, [0, 1, 4]].rename(columns={input.columns[0]: 'Column1', input.columns[1]: 'Column2', 'Id': 'ID'})
r1_2 = input.iloc[:, [3, 2, 4]].rename(columns={input.columns[3]: 'Column1', input.columns[2]: 'Column2', 'Id': 'ID'})
r2 = pd.concat([r1_2, r1_1]).sort_values(by='ID').drop_duplicates().drop(columns='ID').reset_index(drop=True)
print(r2.equals(test)) # True
Python solution 2 for Shift Rows into Columns, proposed by Luan Rodrigues:
PY Solution!
import pandas as pd
file = "PQ_Challenge_225.xlsx"
df = pd.read_excel(file,usecols="A:D")
def fx(tab):
a = tab[['Emp ID', 'Name']]
b = tab[['Group', 'Dept']]
c = pd.concat([b,a])
return c
result = df.groupby('Group').apply(fx).reset_index(drop=True)
result['Name'] = result.apply(lambda x: x['Dept'] if pd.isna(x['Name']) else x['Name'], axis=1)
result['Emp ID'] = result.apply(lambda x: x['Group'] if pd.isna(x['Emp ID']) else x['Emp ID'], axis=1)
res = result.iloc[:,2:].drop_duplicates()
res.columns = ['Column1','Column2']
print(res)
Python solution 3 for Shift Rows into Columns, proposed by Luke Jarych:
import pandas as pd
import xlwings as xw
wb = xw.Book(r'Grouping table.xlsx')
sh = wb.sheets[0]
table1 = sh.tables['Table1']
rng1 = sh.range(table1.range.address)
df = rng1.options(pd.DataFrame, header=True, index=False, numbers=float).value
def process_group(group):
initial_row = [group['Group'].iloc[0], group['Dept'].iloc[0]]
employee_rows = group[['Emp ID', 'Name']].values.tolist()
combined_rows = [initial_row] + employee_rows
return combined_rows
grouped = df.groupby(['Group', 'Dept']).apply(process_group)
new_data = [item for sublist in grouped for item in sublist]
new_df = pd.DataFrame(new_data, columns=['Column1', 'Column2'])
new_df['Column1'] = new_df['Column1'].apply(lambda x: int(x) if isinstance(x, float) else x)
new_df
Solving the challenge of Shift Rows into Columns with Python in Excel
Python in Excel solution 1 for Shift Rows into Columns, proposed by Alejandro Campos:
df = xl("A1:D9", headers=True)
transformed_data = []
for group, group_data in df.groupby(['Group', 'Dept']):
group_name, dept_name = group
transformed_data.append([group_name, dept_name])
for _, row in group_data.iterrows():
transformed_data.append([row['Emp ID'], row['Name']])
transformed_df = pd.DataFrame(transformed_data, columns=['Column1', 'Column2'])
transformed_df
Python in Excel solution 2 for Shift Rows into Columns, proposed by Abdallah Ally:
df = xl("A1:D9", headers=True)
# Perform data manipulation
dfs = []
for group in df['Group'].unique():
dfg = df[df['Group'] == group]
dept = dfg['Dept'].values[0]
top = pd.DataFrame({'Emp ID': [group], 'Name': [dept]})
dfs.append(pd.concat([top, dfg.iloc[:, :2]]))
df = pd.concat(dfs, ignore_index=True)
df.columns = ['Column1', 'Column2']
df
Python in Excel solution 3 for Shift Rows into Columns, proposed by Victor Wang:
tbl = df.groupby(['Group', 'Dept'])
col1 = []
col2 = []
for (group, dept), rows in tbl:
col1 += [group]
col1 += rows['Emp ID'].tolist()
col2 += [dept]
col2 += rows['Name'].tolist()
pd.DataFrame({'Column1': col1, 'Column2': col2})
Python in Excel solution 4 for Shift Rows into Columns, proposed by Ümit Barış Köse, MSc:
df=xl("A2:D10", headers=True)
df_result = df.groupby('Group').apply(
lambda group_data: [[group_data.name.replace(' ', '')] + [group_data['Dept'].iloc[0]]] +
group_data[['Emp ID', 'Name']].values.tolist()
).explode().tolist()
df_result = pd.DataFrame(df_result, columns=['Column1', 'Column2'])
Solving the challenge of Shift Rows into Columns with R
R solution 1 for Shift Rows into Columns, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Power Query/PQ_Challenge_225.xlsx"
input = read_excel(path, range = "A1:D9")
test = read_excel(path, range = "F1:G12")
r1 = input %>%
mutate(Id = consecutive_id(Group),
`Emp ID` = as.character(`Emp ID`),
Group = ifelse(Group == "Group A", "GroupA", Group))
r1_1 = r1 %>% select(Column1 = 1, Column2 = 2, ID = 5)
r1_2 = r1 %>% select(Column1 = 4, Column2 = 3, ID = 5)
r2 = rbind(r1_2, r1_1) %>%
arrange(ID) %>%
distinct() %>%
select(-ID)
all.equal(r2, test, check.attributes = FALSE)
#> [1] TRUE
&