List the Vice Presidents who have been presidents also and list their vice-presidency and presidency years separated by comma.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 216
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Vice Presidents Turned Presidents with Power Query
_x000D_Power Query solution 1 for Vice Presidents Turned Presidents, proposed by Bo Rydobon 🇹🇭:
let
Source = Table.Buffer(Excel.CurrentWorkbook(){[Name = "Table1"]}[Content]),
Vice = Table.FromValue(
List.Intersect({Source[Vice President], Source[President]}),
[DefaultColumnName = "Vice President"]
),
AYear = List.Accumulate(
{"Vice President", "President"},
Vice,
(A, P) =>
Table.AddColumn(
A,
Text.Replace(P, "t", "cy") & " Year",
each Text.Combine(
List.Transform(
Table.SelectRows(Source, (s) => Record.Field(s, P) = [Vice President])[Year],
Text.From
),
", "
)
)
)
in
AYearPower Query solution 2 for Vice Presidents Turned Presidents, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
AddPYs = Table.AddColumn(
Source,
"Presidency Years",
each Text.Combine(Table.SelectRows(Source, (v) => v[President] = [Vice President])[Year], ", ")
),
Solution = Table.RenameColumns(
Table.SelectRows(AddPYs, each ([Presidency Years] <> ""))[
[Vice President],
[Year],
[Presidency Years]
],
{{"Year", "Vice Presidency Years"}}
)
in
SolutionPower Query solution 3 for Vice Presidents Turned Presidents, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Merge = Table.NestedJoin(
Source,
{"Vice President"},
Source,
{"President"},
"Source",
JoinKind.LeftOuter
),
Expand = Table.SelectRows(
Table.ExpandTableColumn(Merge, "Source", {"Year"}, {"Vice President Year"}),
each ([Vice President Year] <> null)
),
Sol = Table.Group(
Expand,
{"Vice President"},
{
{
"Vice Presidency Years",
each Text.Combine(List.Transform(List.Distinct([Year]), Text.From), ", ")
},
{
"Presidency Years",
each Text.Combine(List.Transform(List.Distinct([Vice President Year]), Text.From), ", ")
}
}
)
in
SolPower Query solution 4 for Vice Presidents Turned Presidents, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Vice = Table.FromColumns(
{List.Intersect({Source[Vice President], Source[President]})},
{"Vice President"}
),
ViceYears = Table.AddColumn(
Vice,
"Vice Presidency Years",
each
if List.ContainsAny(Source[Vice President], {[Vice President]}) then
Text.Combine(
List.Transform(
List.PositionOfAny(Source[Vice President], {[Vice President]}, 5),
each Text.From(Source[Year]{_})
),
", "
)
else
null
),
PresYear = Table.AddColumn(
ViceYears,
"Presidency Years",
each
if List.ContainsAny(Source[President], {[Vice President]}) then
Text.Combine(
List.Transform(
List.PositionOfAny(Source[President], {[Vice President]}, 5),
each Text.From(Source[Year]{_})
),
", "
)
else
null
)
in
PresYearPower Query solution 5 for Vice Presidents Turned Presidents, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
fil = List.Intersect({List.Distinct(Fonte[President]), List.Distinct(Fonte[Vice President])}),
vice = Table.SelectRows(Fonte, each List.Contains(fil, [Vice President]))[
[Vice President],
[Year]
],
mesc = Table.NestedJoin(
vice,
{"Vice President"},
Fonte,
{"President"},
"Presidency Years",
JoinKind.LeftOuter
),
res = Table.TransformColumns(mesc, {{"Presidency Years", each Text.Combine(_[Year], ", ")}})
in
resPower Query solution 6 for Vice Presidents Turned Presidents, proposed by Henriette Hamer:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"VP Removed Columns" = Table.RemoveColumns(Source, {"President"}),
#"VP Changed Type" = Table.TransformColumnTypes(#"VP Removed Columns", {{"Year", type text}}),
#"VP Grouped Rows" = Table.Group(
#"VP Changed Type",
{"Vice President"},
{{"Years", each Text.Combine([Year], ", "), type any}}
),
#"P Removed Columns" = Table.RemoveColumns(Source, {"Vice President"}),
#"P Changed Type" = Table.TransformColumnTypes(#"P Removed Columns", {{"Year", type text}}),
#"P Grouped Rows" = Table.Group(
#"P Changed Type",
{"President"},
{{"Years", each Text.Combine([Year], ", "), type any}}
),
#"Merged Queries" = Table.NestedJoin(
#"VP Grouped Rows",
{"Vice President"},
#"P Grouped Rows",
{"President"},
"Presidents",
JoinKind.Inner
),
#"Expanded Presidents" = Table.ExpandTableColumn(
#"Merged Queries",
"Presidents",
{"Years"},
{"Presidents.Years"}
),
#"Renamed Columns" = Table.RenameColumns(
#"Expanded Presidents",
{{"Years", "Vice Presidency Years"}, {"Presidents.Years", "Presidency Years"}}
)
in
#"Renamed Columns"Solving the challenge of Vice Presidents Turned Presidents with Excel
_x000D_Excel solution 1 for Vice Presidents Turned Presidents, proposed by Bo Rydobon 🇹🇭:
=LET(
b,
B2:B67,
c,
C2:C67,
d,
FILTER(
c,
COUNTIF(
b,
c
)
),
L,
LAMBDA(
x,
MAP(
d,
LAMBDA(
e,
TEXTJOIN(
", ",
,
REPT(
A2:A67,
x=e
)
)
)
)
),
y,
"Presidency Years",
VSTACK(
HSTACK(
C1,
"Vice "&y,
y
),
HSTACK(
d,
L(
c
),
L(
b
)
)
)
)
Without textjoin for Vice Presidency year
=LET(
y,
A2:A67,
p,
B2:B67,
v,
C2:C67,
x,
VLOOKUP(
v,
GROUPBY(
p,
y&"",
ARRAYTOTEXT
),
2,
),
FILTER(
HSTACK(
v,
y,
x
),
ISTEXT(
x
)
)
)Excel solution 2 for Vice Presidents Turned Presidents, proposed by Rick Rothstein:
=LET(
a,
A2:A67,
b,
B2:B67,
c,
C2:C67,
v,
FILTER(
c,
ISNUMBER(
XMATCH(
c,
b
)
)
),
HSTACK(
v,
XLOOKUP(
v,
c,
a
),
MAP(
v,
LAMBDA(
x,
TEXTJOIN(
", ",
,
IF(
x=b,
a,
""
)
)
)
)
)
)
If the headers are to be included in the formula...
=LET(
a,
A2:A67,
b,
B2:B67,
c,
C2:C67,
t,
"Vice Presidency Years",
v,
FILTER(
c,
ISNUMBER(
XMATCH(
c,
b
)
)
),
VSTACK(
HSTACK(
TEXTBEFORE(
t,
" ",
2
),
t,
TEXTAFTER(
t,
" "
)
),
HSTACK(
v,
XLOOKUP(
v,
c,
a
),
MAP(
v,
LAMBDA(
x,
TEXTJOIN(
", ",
,
IF(
x=b,
a,
""
)
)
)
)
)
)
)Excel solution 3 for Vice Presidents Turned Presidents, proposed by John V.:
=LET(
p,
B2:B67,
v,
C2:C67,
r,
TOCOL(
XLOOKUP(
v,
p,
p
),
2
),
f,
LAMBDA(
b,
MAP(
r,
LAMBDA(
x,
ARRAYTOTEXT(
FILTER(
A2:A67,
b=x
)
)
)
)
),
HSTACK(
r,
f(
v
),
f(
p
)
)
)Excel solution 4 for Vice Presidents Turned Presidents, proposed by محمد حلمي:
=LET(
a,
A2:A67,
b,
B2:B67,
c,
C2:C67,
r,
TOCOL(
XLOOKUP(
c,
b,
b
),
2
),
HSTACK(
r,
XLOOKUP(
r,
c,
a&""
),
MAP(
r,
LAMBDA(
v,
ARRAYTOTEXT(
FILTER(
a,
b=v
)
)
)
)
)
)Excel solution 5 for Vice Presidents Turned Presidents, proposed by محمد حلمي:
=LET(
a,
A2:A67,
b,
B2:B67,
c,
C2:C67,
r,
INDEX(
b,
TOCOL(
XMATCH(
c,
b
),
2
)
),
HSTACK(
r,
XLOOKUP(
r,
c,
a
)&"",
MAP(
r,
LAMBDA(
v,
ARRAYTOTEXT(
FILTER(
a,
b=v
)
)
)
)
)
)Excel solution 6 for Vice Presidents Turned Presidents, proposed by Kris Jaganah:
=LET(
a,
A2:A67,
b,
B2:B67,
c,
C2:C67,
d,
XLOOKUP(
c,
b,
a,
""
),
e,
TEXT(
FILTER(
HSTACK(
c,
a
),
d<>""
),
"#"
),
f,
MAP(
TAKE(
e,
,
1
),
LAMBDA(
x,
ARRAYTOTEXT(
FILTER(
a,
b=x
)
)
)
),
HSTACK(
e,
f
)
)Excel solution 7 for Vice Presidents Turned Presidents, proposed by Julian Poeltl:
=LET(
Y,
A2:A67,
P,
B2:B67,
V,
C2:C67,
PY,
MAP(
V,
LAMBDA(
A,
TEXTJOIN(
", ",
,
FILTER(
Y,
P=A
)
)
)
),
FILTER(
HSTACK(
V,
Y,
PY
),
NOT(
ISERR(
PY
)
)
)
)Excel solution 8 for Vice Presidents Turned Presidents, proposed by Timothée BLIOT:
=LET(
D,
A2:A67,
P,
B2:B67,
V,
C2:C67,
A,
UNIQUE(
FILTER(
P,
MAP(
P,
LAMBDA(
x,
ISTEXT(
XLOOKUP(
x,
P,
P
)
)*ISTEXT(
XLOOKUP(
x,
V,
V
)
)
)
)
)
),
REDUCE(
{"VicePresident",
"VicePresidencyYears",
"PresidencyYears"},
A,
LAMBDA(
ac,
z,
VSTACK(
ac,
HSTACK(
z,
ARRAYTOTEXT(
FILTER(
D,
V=z
)
),
ARRAYTOTEXT(
FILTER(
D,
P=z
)
)
)
)
)
)
)Excel solution 9 for Vice Presidents Turned Presidents, proposed by Oscar Mendez Roca Farell:
=LET(
_a,
A2:A67,
_b,
B2:B67,
_c,
C2:C67,
_v,
TOCOL(
UNIQUE(
XLOOKUP(
_b,
_c,
_a
)
),
3
),
_p,
MAP(
C2:C67,
LAMBDA(
i,
TEXTJOIN(
", ",
1,
REPT(
_a,
_b=i
)
)
)
),
IFNA(
FILTER(
HSTACK(
_c,
@_v,
_p
),
_p<>""
),
_v
)
)Excel solution 10 for Vice Presidents Turned Presidents, proposed by Sunny Baggu:
=LET(
_vp,
TOCOL(
XLOOKUP(
UNIQUE(
C2:C67
),
B2:B67,
B2:B67
),
3
),
_e1,
LAMBDA(
rng,
MAP(
_vp,
LAMBDA(
a,
TEXTJOIN(
",",
,
FILTER(
A2:A67,
rng = a
)
)
)
)
),
HSTACK(_vp,
_e1(
C2:C67
),
_e1(B2:B67))
)Excel solution 11 for Vice Presidents Turned Presidents, proposed by Sunny Baggu:
=LET(
_vp,
TOCOL(
XLOOKUP(
UNIQUE(
C2:C67
),
B2:B67,
B2:B67
),
3
),
DROP(
REDUCE(
"",
_vp,
LAMBDA(
a,
v,
VSTACK(
a,
LET(
_f,
LAMBDA(
rng,
& TEXTJOIN(
", ",
,
FILTER(
A2:A67,
rng = v,
""
)
)
),
HSTACK(
v,
_f(
C2:C67
),
_f(
B2:B67
)
)
)
)
)
),
1
)
)Excel solution 12 for Vice Presidents Turned Presidents, proposed by Charles Roldan:
=LET(
Year,
A2:A67,
Pres,
B2:B67,
Vice,
C2:C67,
Headers,
E1:G1,
List,
FILTER(
Vice,
ISNUMBER(
XMATCH(
Vice,
Pres
)
)
),
Eras,
LAMBDA(
Col,
BYROW(
REPT(
TOROW(
Year
),
TOROW(
Col
) = List
),
LAMBDA(
Row,
TEXTJOIN(
", ",
TRUE,
Row
)
)
)
),
VSTACK(
Headers,
HSTACK(
List,
Eras(
Vice
),
Eras(
Pres
)
)
)
)Excel solution 13 for Vice Presidents Turned Presidents, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:
=LET(
a;
LET(
y;
MAP(
C2:C67;
LAMBDA(
x;
TEXTJOIN(
;
;
UNIQUE(
IF(
B2:B67=x;
B2:B67;
""
)
)
)
)
);
FILTER(
y;
y<>""
)
);
HSTACK(
LET(
y;
MAP(
C2:C67;
LAMBDA(
x;
TEXTJOIN(
;
;
UNIQUE(
IF(
B2:B67=x;
B2:B67;
""
)
)
)
)
);
FILTER(
y;
y<>""
)
);
MAP(
a;
LAMBDA(
z;
FILTER(
A2:A67;
C2:C67=z
)
)
);
MAP(
a;
LAMBDA(
z;
TEXTJOIN(
",";
;
FILTER(
A2:A67;
B2:B67=z
)
)
)
)
)
)Excel solution 14 for Vice Presidents Turned Presidents, proposed by Julien Lacaze:
=LET(
y,
A2:A67,
p,
B2:B67,
v,
C2:C67,
_f1,
MAP(
v,
LAMBDA(
a,
--OR(
a=p
)
)
),
l,
FILTER(
v,
_f1
),
HSTACK(
l,
MAP(
l,
LAMBDA(
a,
TEXTJOIN(
", ",
,
FILTER(
y,
a=v
)
)
)
),
MAP(
l,
LAMBDA(
a,
TEXTJOIN(
", ",
,
FILTER(
y,
a=p
)
)
)
)
)
)Excel solution 15 for Vice Presidents Turned Presidents, proposed by Pieter de Bruijn:
=LET(
c,
C2:C67,
b,
B2:B67,
a,
A2:A67,
d,
HSTACK(
FILTER(
HSTACK(
c,
a
),
ISNUMBER(
XMATCH(
c,
b
)
)
)
),
HSTACK(
d,
MAP(
TAKE(
d,
,
1
),
LAMBDA(
x,
TEXTJOIN(
", ",
,
FILTER(
a,
b=x
)
)
)
)
)
)Excel solution 16 for Vice Presidents Turned Presidents, proposed by Nicolas Micot:
=LET(names;
UNIQUE(
ASSEMB.V(
B2:B67;
C2:C67
)
);
vicePresidents;
FILTRE(names;
(NB.SI(
B2:B67;
names
)>0)*(NB.SI(
C2:C67;
names
)>0));
vpDates;
BYROW(
vicePresidents;
LAMBDA(
a;
JOINDRE.TEXTE(
", ";
VRAI;
FILTRE(
A2:A67;
C2:C67=a;
""
)
)
)
);
pDates;
BYROW(
vicePresidents;
LAMBDA(
a;
JOINDRE.TEXTE(
", ";
VRAI;
FILTRE(
A2:A67;
B2:B67=a;
""
)
)
)
);
ASSEMB.H(
vicePresidents;
vpDates;
pDates
))Excel solution 17 for Vice Presidents Turned Presidents, proposed by Daniel Garzia:
=LET(
y,
A2:A67,
p,
B2:B67,
v,
C2:C67,
f,
FILTER(
v,
ISNUMBER(
XMATCH(
v,
p
)
)
),
HSTACK(
f,
MAP(
f,
LAMBDA(
x,
TEXTJOIN(
", ",
,
FILTER(
y,
v=x
)
)
)
),
MAP(
f,
LAMBDA(
x,
TEXTJOIN(
", ",
,
FILTER(
y,
p=x
)
)
)
)
)
)Excel solution 18 for Vice Presidents Turned Presidents, proposed by Hussain Ali Nasser:
=LET(
_pnames,
B2:B67,
_vpnames,
C2:C67,
_years,
A2:A67,
_names,
UNIQUE(
FILTER(
_pnames,
ISNUMBER(
XMATCH(
_pnames,
_vpnames
)
)
)
),
_vpyears,
BYROW(
_names,
LAMBDA(
_names,
TEXTJOIN(
", ",
,
FILTER(
_years,
_vpnames=_names
)
)
)
),
_pyears,
BYROW(
_names,
LAMBDA(
_names,
TEXTJOIN(
", ",
,
FILTER(
_years,
_pnames=_names
)
)
)
),
HSTACK(
_names,
_vpyears,
_pyears
)
)Excel solution 19 for Vice Presidents Turned Presidents, proposed by Victor Yemitan:
=LET(
y,
A2:A67,
p,
B2:B67,
v,
C2:C67,
ir,
UNIQUE(
XLOOKUP(
v,
p,
p,
""
)
),
r,
FILTER(
ir,
ir<>""
),
rv,
XLOOKUP(
r,
v,
y
),
rp,
MAP(
r,
LAMBDA(
x,
ARRAYTOTEXT(
FILTER(
y,
p=x
)
)
)
),
HSTACK(
r,
rv,
rp
)
)Excel solution 20 for Vice Presidents Turned Presidents, proposed by Adam Carter:
=LET(
vps,
C2:C67,
pres,
B2:B67,
years,
A2:A67,
vp_pres,
--ISNUMBER(
MATCH(
vps,
pres,
0
)
),
vp_filtered,
FILTER(
vps,
vp_pres
),
vp_years,
DROP(
REDUCE(
0,
SEQUENCE(
ROWS(
vp_filtered
)
),
LAMBDA(
a,
v,
VSTACK(
a,
FILTER(
years,
vps=INDEX(
vp_filtered,
v
)
)
)
)
),
1
),
p_years,
DROP(REDUCE(0,
SEQUENCE(
ROWS(
vp_filtered
)
),
LAMBDA(a,
v,
VSTACK(a,
TEXTJOIN(", ",
1,
(FILTER(
years,
pres=INDEX(
vp_filtered,
v
)
)))))),
1),
HSTACK(
vp_filtered,
vp_years,
p_years
))Excel solution 21 for Vice Presidents Turned Presidents, proposed by Colin Davidson:
=LET(year_data, A2:A67,
pres_data, B2:B67,
vp_data, C2:C67,
presidents, UNIQUE(pres_data),
vice_presidents, UNIQUE(vp_data),
filter_condition, MAP(vice_presidents,LAMBDA(x, IF(NOT(ISERROR(XMATCH(x,presidents))),1,0))),
vp_presidents, FILTER(vice_presidents, filter_condition),
vp_years, BYROW(vp_presidents, LAMBDA(x, ARRAYTOTEXT(FILTER(year_data, vp_data=x)))),
pres_years, BYROW(vp_presidents, LAMBDA(x, ARRAYTOTEXT(FILTER(year_data, pres_data=x)))),
output_headers, {"Vice President", "Vice Presidency Years", "Presidency Years"},
output_data, HSTACK(vp_presidents, vp_years, pres_years),
VSTACK(output_headers, output_data))