List those presidents’ names whose first characters of all parts of names are same.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 504
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Presidents with Same Initials with Power Query
Power Query solution 1 for Presidents with Same Initials, proposed by Kris Jaganah:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Ans = Table.SelectRows(
Source,
(x) =>
List.Count(
List.Distinct(List.Transform(Text.Split(x[US Presidents], " "), each Text.Start(_, 1)))
)
= 1
)
in
AnsPower Query solution 2 for Presidents with Same Initials, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Return = Table.SelectRows(
Source,
each [
S = Text.Split([US Presidents], " "),
T = List.Transform(S, (f) => Text.Start(f, 1)),
R = List.Count(List.Distinct(T)) = 1
][R]
)
in
ReturnPower Query solution 3 for Presidents with Same Initials, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.SelectRows(
Source,
each
let
a = Text.Split([US Presidents], " "),
b = List.Transform(a, each Text.Start(_, 1)),
c = List.Count(List.Distinct(b)) = 1
in
c
)
in
SolPower Query solution 4 for Presidents with Same Initials, proposed by Luan Rodrigues:
let
Fonte = Table.SelectRows(
Tabela1,
each List.Count(
List.Distinct(List.Transform(Text.Split([US Presidents], " "), each Text.Start(_, 1)))
)
= 1
)
in
FontePower Query solution 5 for Presidents with Same Initials, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Result = List.Select(
Source[US Presidents],
each [
s = Text.Split(_, " "),
f = List.Transform(s, (x) => Text.Start(x, 1)),
r = List.ContainsAll({f{0}}, f)
][r]
)
in
ResultPower Query solution 6 for Presidents with Same Initials, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
L = List.Transform,
P = List.Positions,
a = S[US Presidents],
b = List.Zip({P(a), a}),
c = L(P(a), each {_}),
d = L(a, each Text.Split(_, " ")),
e = L(P(d), each d{_} & c{_}),
f = L(e, each L(_, each if _ is text then Text.Start(_, 1) else _)),
g = List.Select(f, each if List.Count(_) = 3 then _{0} = _{1} else _{0} = _{2}),
h = L(g, List.Last),
Sol = Table.FromColumns({List.ReplaceMatchingItems(h, b)}, {"Answer Expected"})
in
SolPower Query solution 7 for Presidents with Same Initials, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source, {{"US Presidents", type text}}),
#"Added Custom" = Table.AddColumn(
#"Changed Type",
"Answer",
each List.Count(
List.Distinct(List.Transform(Text.Split([US Presidents], " "), each Text.Start(_, 1)))
)
= 1
),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Answer] = true)),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows", {"US Presidents"})
in
#"Removed Other Columns"Power Query solution 8 for Presidents with Same Initials, proposed by Yaroslav Drohomyretskyi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Filter = Table.SelectRows(
Source,
each Text.Length(Text.Select([US Presidents], Text.Start([US Presidents], 1)))
> Text.Length(Text.Select([US Presidents], " "))
)
in
FilterPower Query solution 9 for Presidents with Same Initials, proposed by Ahmed Ariem:
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"bVPRjtpADPwVi+cq/wBcAcHRXlNapKJ78LGGbNnYlTeB5u/rzRJdOPUpijOe8Yydw2GyJNEzwR5j5fncCE9ePx0ma6kYpg7r2L/uKqkxwppOJ9I4YLCmCFt0/kNFWIXeab61no/diG3KTukGazxehs4tauMZfiLDrFXKxb0PwWMNK2LtYIWq70qJeNcF0pHwpoAXCZe+8guPFVrXDrsgGbQ1OlQHC3vWonnChSJfgkm/eNIjjdhmrTEwZr3pm2JlozybFQn8YMNGGcb6EboYrfd7AUsjbvpi2TYV6UlMelaYjY7iSGZqUNSTp+D66ryi2JCm+lSbqs2zL1WuVpwHulJAztAZ8W+sbfaHaP4LHaLcHjeeA3X3rZI4CwJKkZjQzWPscktx7fB0r4s4lRvY90Frb8LEsEy21Nn9ZAsYrjbVHOYiwbtzTnVF+kbaGG2a7zH8p+LDDMlRl2LcaVvfd/B08+eqSdjPPhJXchu2n45hUcCGmMllb88dO+EU+Hg/pU9X4WBbwBf/dwiMFIODsoCF7Sgz+rruYG5HeZcohXsMlIRnHPr6P2fWxiqvw3Kz2D038gDYFyMMql09fH3DGrOpTJxs/rmbMUrv0i/w+g8=",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [#"US Presidents" = _t]
),
#"Changed Type" = Table.TransformColumnTypes(Source, {{"US Presidents", type text}}),
Custom1 = Table.SelectRows(
#"Changed Type",
(x) =>
List.Count(List.Distinct(Text.Split(Text.Select(x[US Presidents], {"A" .. "Z", " "}), " ")))
= 1
)
in
Custom1Power Query solution 10 for Presidents with Same Initials, proposed by Ahmed Ariem:
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"bVPRjtpADPwVi+cq/wBcAcHRXlNapKJ78LGGbNnYlTeB5u/rzRJdOPUpijOe8Yydw2GyJNEzwR5j5fncCE9ePx0ma6kYpg7r2L/uKqkxwppOJ9I4YLCmCFt0/kNFWIXeab61no/diG3KTukGazxehs4tauMZfiLDrFXKxb0PwWMNK2LtYIWq70qJeNcF0pHwpoAXCZe+8guPFVrXDrsgGbQ1OlQHC3vWonnChSJfgkm/eNIjjdhmrTEwZr3pm2JlozybFQn8YMNGGcb6EboYrfd7AUsjbvpi2TYV6UlMelaYjY7iSGZqUNSTp+D66ryi2JCm+lSbqs2zL1WuVpwHulJAztAZ8W+sbfaHaP4LHaLcHjeeA3X3rZI4CwJKkZjQzWPscktx7fB0r4s4lRvY90Frb8LEsEy21Nn9ZAsYrjbVHOYiwbtzTnVF+kbaGG2a7zH8p+LDDMlRl2LcaVvfd/B08+eqSdjPPhJXchu2n45hUcCGmMllb88dO+EU+Hg/pU9X4WBbwBf/dwiMFIODsoCF7Sgz+rruYG5HeZcohXsMlIRnHPr6P2fWxiqvw3Kz2D038gDYFyMMql09fH3DGrOpTJxs/rmbMUrv0i/w+g8=",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [#"US Presidents" = _t]
),
#"Changed Type" = Table.TransformColumnTypes(Source, {{"US Presidents", type text}}),
Custom1 = Table.SelectRows(
#"Changed Type",
(x) =>
[
a = Text.Split(x[US Presidents], " "),
b = List.Transform(a, (x) => Text.Select(x, {"A" .. "Z"})),
c = List.Count(List.Distinct(b)) = 1
][c]
= true
)
in
Custom1Power Query solution 11 for Presidents with Same Initials, proposed by Mihai Radu O:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
sol = Table.SelectRows(
Source,
each (List.Count(List.Distinct(Text.ToList(Text.Select([US Presidents], {"A" .. "Z"})))) = 1)
)
in
solSolving the challenge of Presidents with Same Initials with Excel
Excel solution 1 for Presidents with Same Initials, proposed by Bo Rydobon 🇹🇭:
=FILTER(A2:A47,MAP(A2:A47,LAMBDA(a,AND(LEFT(TEXTSPLIT(a," "))=LEFT(a)))))
=FILTER(A2:A47,MAP(A2:A47,LAMBDA(a,ROWS(UNIQUE(LEFT(TEXTSPLIT(a,," "))))=1)))Excel solution 2 for Presidents with Same Initials, proposed by Rick Rothstein:
=FILTER(A2:A47,MAP(A2:A47,LAMBDA(x,AND(CODE(TEXTSPLIT(x," "))=CODE(x)))))Excel solution 3 for Presidents with Same Initials, proposed by Rick Rothstein:
=FILTER(A2:A47,MAP(A2:A47,LAMBDA(x,AVERAGE(CODE(TEXTSPLIT(x," ")))=CODE(x))))Excel solution 4 for Presidents with Same Initials, proposed by John V.:
=FILTER(A2:A47,MAP(A2:A47,LAMBDA(x,AND(LEFT(x)=LEFT(TEXTSPLIT(x," "))))))Excel solution 5 for Presidents with Same Initials, proposed by محمد حلمي:
=FILTER(A1:A47,MAP(A1:A47,LAMBDA(A,LET(
I,LEFT(TEXTSPLIT(A," ")),AND(@I=I)))))Excel solution 6 for Presidents with Same Initials, proposed by Kris Jaganah:
=FILTER(
A2:A47,
MAP(
A2:A47,
LAMBDA(
x,
LEN(
CONCAT(
UNIQUE(
REGEXEXTRACT(
x,
"[A-Z]",
1
),
1
)
)
)
)
)=1
)Excel solution 7 for Presidents with Same Initials, proposed by Julian Poeltl:
=FILTER(
A2:A47,
MAP(
A2:A47,
LAMBDA(
N,
COLUMNS(
UNIQUE(
LEFT(
TEXTSPLIT(
N,
" "
),
1
),
1
)
)=1
)
)
)Excel solution 8 for Presidents with Same Initials, proposed by Timothée BLIOT:
=FILTER(A2:A47,MAP(A2:A47,LAMBDA(z,ROWS(UNIQUE(LEFT(TEXTSPLIT(z,," "))))=1)))Excel solution 9 for Presidents with Same Initials, proposed by Nikola Z Grujicic – Nikola Ž Grujičić:
=LET(x, MAP(A1:A47,LAMBDA(n, LET(f, TEXTSPLIT(n,," "), g, LEFT(f), h, COUNTA(UNIQUE(g)), XLOOKUP(1,h,n)))), FILTER(x, ISTEXT(x)))Excel solution 10 for Presidents with Same Initials, proposed by Hussein SATOUR:
=FILTER(
A2:A47,
MAP(
A2:A47,
LAMBDA(
x,
COUNTA(
UNIQUE(
LEFT(
TEXTSPLIT(
x,
" "
)
),
1
)
)
)
)=1
)Excel solution 11 for Presidents with Same Initials, proposed by Duy Tùng:
=FILTER(
A2:A47,
REGEXTEST(
A2:A47,
"^(.)S*( 1S*)*$"
)
)Excel solution 12 for Presidents with Same Initials, proposed by Sunny Baggu:
=FILTER(
A2:A47,
MAP(
A2:A47,
LAMBDA(
t,
LET(
_a,
LEFT(
TEXTSPLIT(
t,
,
" "
)
),
AND(
_a = TAKE(
_a,
1
)
)
)
)
)
)Excel solution 13 for Presidents with Same Initials, proposed by Abdallah Ally:
=FILTER(A2:A47,
MAP(A2:A47,
LAMBDA(x,
LET(a,
LEFT(
TEXTSPLIT(
x,
" "
)
),
COUNTA(
a
)=SUM(--(LEFT(
x
)=a))))))Excel solution 14 for Presidents with Same Initials, proposed by Anshu Bantra:
=LET(
data_, A2:A47,
initials_, DROP(
REDUCE(
"",
data_,
LAMBDA(ini, ro,
IFERROR(
VSTACK(ini, TEXTJOIN("", TRUE, REGEXEXTRACT(ro, "[A-Z]", 1))),
""
)
)
),
1
),
FILTER(data_, LEN(SUBSTITUTE(initials_, LEFT(initials_), "")) = 0)
)Excel solution 15 for Presidents with Same Initials, proposed by Pieter de B.:
=LET(
a,
A2:A47,
FILTER(
a,
MAP(
a,
LAMBDA(
b,
AND(
LEFT(
b
)=LEFT(
TEXTSPLIT(
b,
" "
)
)
)
)
)
)
)Excel solution 16 for Presidents with Same Initials, proposed by Hamidi Hamid:
=TOCOL(
LET(
x,
A2:A47,
IF(
LEFT(
TEXTAFTER(
x,
" ",
-1
),
1
)=LEFT(
x,
1
),
x,
1/0
)
),
3
)Excel solution 17 for Presidents with Same Initials, proposed by Asheesh Pahwa:
=LET(
up,
A2:A47,
r,
DROP(
REDUCE(
"",
up,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
t,
LEFT(
TEXTSPLIT(
y,
" "
)
),
COUNTA(
UNIQUE(
t,
TRUE
)
)
& )
)
)
),
1
),
FILTER(
up,
r=1
)
)Excel solution 18 for Presidents with Same Initials, proposed by ferhat CK:
=TOCOL(BYROW(A2:A47,LAMBDA(x,IF(COUNTA(UNIQUE(LEFT(TEXTSPLIT(x," "),1),1))=1,x,1/0))),2)Excel solution 19 for Presidents with Same Initials, proposed by Andy Heybruch:
=LET(
_names,
A2:A47,
_filt,
(LEFT(
_names
)=LEFT(
TEXTAFTER(
_names,
" "
)
))*
(LEFT(
_names
)=LEFT(
TEXTAFTER(
_names,
" ",
2,
,
,
LEFT(
_names
)
)
)),
FILTER(
_names,
_filt
))Excel solution 20 for Presidents with Same Initials, proposed by Ankur Sharma:
=LET(
a,
A2:A47,
b,
MAP(
a,
LAMBDA(
z,
LET(
c,
LEFT(
TEXTSPLIT(
z,
" "
),
1
),
COUNTA(
c
) = COUNTA(
FILTER(
c,
c = TAKE(
c,
,
1
)
)
)
)
)
),
FILTER(
a,
b
)
)Excel solution 21 for Presidents with Same Initials, proposed by Bilal Mahmoud kh.:
=FILTER(
A2:A47,
MAP(
A2:A47,
LAMBDA(
n,
LET(
a,
MID(
TEXTSPLIT(
n,
" "
),
1,
1
),
AND(
MAP(
SEQUENCE(
,
COUNTA(
a
)-1
),
LAMBDA(
x,
INDEX(
a,
x
)=INDEX(
a,
x+1
)
)
)
)
)
)
)
)Excel solution 22 for Presidents with Same Initials, proposed by JvdV –:
=FILTER(A2:A47,REGEXTEST(A2:A47,"^(.)S*( 1S*)*$"))
My initial thought without regex nor lambda:
=LET(a,A2:A47,FILTER(a,ISERR(FIND(" ",SUBSTITUTE(a," "&LEFT(a),)))))Excel solution 23 for Presidents with Same Initials, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:
=FILTER(
A2:A47,
MAP(
A2:A47,
LAMBDA(
y,
LET(
x,
LEFT(
TEXTSPLIT(
y,
,
" "
),
1
),
NOT(
ISNUMBER(
SEARCH(
"false",
TEXTJOIN(
,
,
TAKE(
x,
1
)=x
)
)
)
)
)
)
)
)Excel solution 24 for Presidents with Same Initials, proposed by Imam Hambali:
=LET(
a, A2:A47,
b, CODE(LEFT(TEXTSPLIT(TEXTJOIN("|",1,a)," ","|"),1)),
c, BYROW(b, LAMBDA(x,AVERAGE(IFNA(x,""))=TAKE(x,,1) )),
FILTER(a, c)
)Excel solution 25 for Presidents with Same Initials, proposed by Eddy Wijaya:
=REDUCE({"Answer Expected"},A2:A47,LAMBDA(a,v,
LET(
_code,CODE(LEFT(TEXTSPLIT(v," "),1)),
_checksame,IF(AVERAGE(_code)=TAKE(_code,,-1),"Take","X"),
newDb,IFNA(VSTACK(a,HSTACK(v,_checksame)),""),
TAKE(FILTER(newDb,TAKE(newDb,,-1)<>"X"),,1))))Excel solution 26 for Presidents with Same Initials, proposed by Milan Shrimali:
=let(
a,
A1:A21,
splt,
arrayformula(
trim(
left(
split(
a,
" "
),
1
)
)
),
unq,
byrow(
arrayformula(
if(
splt<>"",
trim(
splt
),
""
)
),
lambda(
x,
unique(
x,
1,
0
)
)
),
stck,
hstack(
a,
byrow(
unq,
lambda(
x,
if(
counta(
x
)<=2,
x,
""
)
)
)
),
filter(
choosecols(
stck,
1
),
CHOOSECOLS(
stck,
2
)<>""
)
)Excel solution 27 for Presidents with Same Initials, proposed by Nicolas Micot:
=FILTRE(
A2:A47;
MAP(
A2:A47;
LAMBDA(
l_name;
LET(
_firstLetters;
GAUCHE(
FRACTIONNER.TEXTE(
l_name;
;
" "
);
1
);
ET(
_firstLetters=INDEX(
_firstLetters;
1
)
)
)
)
)
)Excel solution 28 for Presidents with Same Initials, proposed by Sandeep Marwal:
=LET(INPUT,A2:A47,
SPLIT,LEFT(TEXTSPLIT(TEXTJOIN(":",,INPUT)," ",":")),
EQUALITYCHECK,BYROW(SPLIT,LAMBDA(A,(INDEX(A,,1)=INDEX(A,,2))*IFERROR(INDEX(A,,2)=INDEX(A,,3),1))),
FILTER(INPUT,EQUALITYCHECK))Excel solution 29 for Presidents with Same Initials, proposed by El Badlis Mohd Marzudin:
=FILTER(
A2:A47,
MAP(
A2:A47,
LAMBDA(
x,
COUNTA(
UNIQUE(
TOCOL(
REGEXEXTRACT(
x,
"bw",
1
)
)
)
)=1
)
)
)Excel solution 30 for Presidents with Same Initials, proposed by Songglod P.:
=LET(names,A2:A47,fc,BYROW(names,LAMBDA(name,CONCAT(UNIQUE(LEFT(TEXTSPLIT(name,," "),1))))),FILTER(names,LEN(fc)=1))Excel solution 31 for Presidents with Same Initials, proposed by Zbigniew Szyszkowski:
=REDUCE(
"Answer Expected",
A2:A47,
LAMBDA(
a,
i,
IF(
AND(
REGEXEXTRACT(
i,
"[A-Z]",
1
)=LEFT(
i
)
),
VSTACK(
a,
i
),
a
)
)
)Excel solution 32 for Presidents with Same Initials, proposed by Ogunronbi Taiwo Fisayo:
=FILTER(
A2:A47,
MAP(
A2:A47,
LAMBDA(
x,
LET(
a,
CHOOSECOLS(
LEFT(
TEXTSPLIT(
x,
" "
),
1
),
1
),
b,
CHOOSECOLS(
LEFT(
TEXTSPLIT(
x,
" "
),
1
),
2
),
c,
IFERROR(
CHOOSECOLS(
LEFT(
TEXTSPLIT(
x,
" "
),
1
),
3
),
""
),
IF(
COUNTA(
TEXTSPLIT(
T3,
" "
)
)>2,
IF(
a=b,
b=c,
FALSE
),
a=b
)
)
)
)
)Solving the challenge of Presidents with Same Initials with Python
Python solution 1 for Presidents with Same Initials, proposed by Konrad Gryczan, PhD:
import pandas as pd
input = pd.read_excel(path, usecols="A")
test = pd.read_excel(path, usecols="B", nrows = 4)
result = input[input['US Presidents'].apply(lambda x: len(set([i[0] for i in x.split()])) == 1)].reset_index(drop=True)
print(result["US Presidents"].equals(test["Answer Expected"])) # True
Solving the challenge of Presidents with Same Initials with Python in Excel
Python in Excel solution 1 for Presidents with Same Initials, proposed by Alejandro Campos:
presidents = xl("A1:A47", headers=True)
presidents= presidents['US Presidents'].astype(str).values.tolist()
def same_initials(name):
parts = name.split()
first_name_initial = parts[0][0].upper()
last_name_initial = parts[-1][0].upper()
return first_name_initial == last_name_initial
filtered_presidents = [name for name in presidents if same_initials(name)]
filtered_presidents
Python in Excel solution 2 for Presidents with Same Initials, proposed by Abdallah Ally:
df = xl("A1:A47", headers=True)
# Perform data munging
lst = df['US Presidents'][
df['US Presidents'].map(
lambda x: all([y[0] == x[0] for y in x.split()])
)
].reset_index(drop=True).values
lst
Python in Excel solution 3 for Presidents with Same Initials, proposed by Anshu Bantra:
def has_matching_initial(name):
parts = name.lower().split()
if len(parts) > 1:
initials = ''.join([part[0] for part in parts])
return initials.count(initials[0])==len(initials)
return False
df = xl("A1:A47", headers=True)
df['matching'] = df['US Presidents'].apply(has_matching_initial)
df[df['matching']]['US Presidents'].values
Solving the challenge of Presidents with Same Initials with R
R solution 1 for Presidents with Same Initials, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel(path, range = "A1:A47")
test = read_excel(path, range = "B1:B5")
result = input %>%
filter(map(`US Presidents`, ~ length(str_extract_all(., "[A-Z]") %>%
unlist() %>% unique())) == 1)
identical(result$`US Presidents`, test$`Answer Expected`)
# [1] TRUE
R solution 2 for Presidents with Same Initials, proposed by Anil Kumar Goyal:
library(readxl)
library(tidyverse)
library(tidytext)
df %>%
set_names("presidents") %>%
unnest_tokens(part, presidents, drop = FALSE) %>%
filter(n_distinct(str_sub(part, 1, 1)) == 1, .by = presidents) %>%
distinct(presidents)
Solving the challenge of Presidents with Same Initials with Excel VBA
Excel VBA solution 1 for Presidents with Same Initials, proposed by Ümit Barış Köse, MSc:
Sub C504()
Dim EndRow As Integer, i1 As Integer, say1 As Integer, say2 As Integer, x1 As Integer, Row As Integer
Dim a As String, f As String
EndRow = Cells(Rows.Count, 1).End(xlUp).Row
Row = 2
For i1 = 2 To EndRow
a = Cells(i1, 1)
f = Left(a, 1)
say1 = 0
say2 = 0
x1 = 1
While x1 > 0
x1 = InStr(x1, a, " ")
If x1 > 0 Then
k = Mid(a, x1 + 1, 1)
say1 = say1 + 1
If f = k Then say2 = say2 + 1
x1 = x1 + 1
End If
Wend
If say1 = say2 Then
Cells(Row, 3) = a
Row = Row + 1
End If
Next i1
End Sub
&&