List the names if first character of first name and first character of last name are same. Ex. Billy James, Bob Simon Julian (First name 1st char: B and Last name 1st char: J are same)
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 612
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Match Names with Same Initials with Power Query
Power Query solution 1 for Match Names with Same Initials, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.AddColumn(
A,
"Ans",
each [a = Text.Split([Names], " "), b = Text.Start(a{0}, 1) & Text.Start(List.Last(a), 1)][b]
),
C = Table.SelectRows(B, each List.Count(List.Select(B[Ans], (x) => x = [Ans])) > 1),
D = Table.Sort(C, "Names")[[Names]]
in
DPower Query solution 2 for Match Names with Same Initials, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Helper = Table.AddColumn(
Source,
"H",
each Text.Start([Names], 1) & Text.End(Text.Select([Names], {"A" .. "Z"}), 1)
),
Group = Table.Group(Helper, "H", {"Names", each [[Names]]}),
Filter = List.Select(Group[Names], each Table.RowCount(_) > 1),
Return = Table.Combine(Filter)
in
ReturnPower Query solution 3 for Match Names with Same Initials, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Col = Table.AddColumn(
Source,
"A",
each
let
a = List.Transform(Text.Split([Names], " "), each Text.Start(_, 1)),
b = a{0} & List.Last(a)
in
b
),
Group = Table.Group(Col, {"A"}, {{"B", each _}, {"C", each Table.RowCount(_)}}),
Sol = Table.Combine(Table.SelectRows(Group, each ([C] <> 1))[B])[[Names]]
in
SolPower Query solution 4 for Match Names with Same Initials, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Lst = Table.AddColumn(
Source,
"A",
each
let
a = List.Transform(Text.Split([Names], " "), each Text.Start(_, 1)),
b = a{0} & List.Last(a)
in
b
),
Lst2 = List.Select(List.Distinct(Lst[A]), each List.Count(List.Select(Lst[A], (x) => x = _)) > 1),
Tbl = Table.SelectRows(Lst, each List.Contains(Lst2, [A])),
Sol = Table.Sort(Tbl, {{"A", 0}})[[Names]]
in
SolPower Query solution 5 for Match Names with Same Initials, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
f = (x) => [a = Text.Split(x, " "), b = Text.At(a{0}, 0) & Text.At(List.Last(a), 0)][b],
Initials = List.Transform(Source[Names], f),
Result = Table.Sort(
Table.SelectRows(Source, each List.Count(List.Select(Initials, (x) => x = f([Names]))) > 1),
each [Names]
)
in
ResultPower Query solution 6 for Match Names with Same Initials, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
LT = List.Transform,
LZ = List.Zip,
a = LT(S[Names], each Text.Split(_, " ")),
b = LT(a, each if List.Count(_) > 2 then {_{0}} & {_{2}} else _),
c = LT(b, each LT(_, each Text.Start(_, 1))),
d = Table.FromColumns(LZ(LT(LZ({c, a}), List.Combine))),
e = Table.Group(d, {"Column1", "Column2"}, {"G", each _})[G],
f = Table.Combine(List.Select(e, each Table.RowCount(_) > 1)),
Sol = Table.AddColumn(
f,
"Answer Expected",
each Text.Combine({[Column3], [Column4], [Column5]}, " ")
)[[Answer Expected]]
in
SolPower Query solution 7 for Match Names with Same Initials, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
FC = Table.AddColumn(
Source,
"FC",
each [A = Text.Select([Names], {"A" .. "Z"}), B = Text.Start(A, 1) & Text.End(A, 1)][B]
),
Grouped = Table.Group(FC, {"FC"}, {"tbl", each _}),
Res = Table.Combine(List.Select(Grouped[tbl], each Table.RowCount(_) >= 2), {"Names"})
in
ResPower Query solution 8 for Match Names with Same Initials, proposed by Rafael González B.:
let
Source = Question_Table,
AC = Table.AddColumn(Source, "textselect", each
let
a = Text.Select([Names], {"A".."Z"}),
b = Text.Start(a,1) & Text.End(a,1)
in
b
),
GR = Table.Group(AC,
{"textselect"},
{{"Count", each Table.RowCount(_)}, {"Details", each _}}
),
FT = Table.Combine(Table.SelectRows(GR, each [Count] > 1)[Details], {"Names"})
in
FT
🧙🏻♂️🧙🏻♂️🧙🏻♂️
Power Query solution 9 for Match Names with Same Initials, proposed by CA Raghunath Gundi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
FirstNameFirst = Table.AddColumn(Source, "FirstNameFirst", each Text.Start([Names], 1)),
DuplicateColumn = Table.DuplicateColumn(FirstNameFirst, "Names", "Name"),
LastNameFirst = Table.TransformColumns(
Table.SplitColumn(
DuplicateColumn,
"Names",
Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true),
{"Name.1", "LastNameFirst"}
),
{"LastNameFirst", each Text.Start(_, 1)}
),
Merge = Table.CombineColumns(
LastNameFirst,
{"FirstNameFirst", "LastNameFirst"},
Combiner.CombineTextByDelimiter("", QuoteStyle.None),
"Criteria"
),
Group = Table.Group(
Merge,
{"Criteria"},
{{"Count", each Table.RowCount(_), Int64.Type}, {"Table", each _}}
),
Result = Table.Combine(Table.SelectRows(Group, each [Count] > 1)[Table])[[Name]]
in
ResultPower Query solution 10 for Match Names with Same Initials, proposed by Mihai Radu O:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
a = List.Transform(
Source[Names],
(x) =>
[a = Text.Split(x, " "), b = List.First(a) & List.Last(a), c = Text.Select(b, {"A" .. "Z"})][
c
]
),
b = List.Sort(
List.RemoveNulls(
List.Transform(
List.Zip({Source[Names], a}),
(x) => if List.Count(List.PositionOf(a, x{1}, Occurrence.All)) > 1 then x{0} else null
)
)
)
in
bPower Query solution 11 for Match Names with Same Initials, proposed by Mihai Radu O:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
a = Table.AddColumn(
Source,
"r",
each [
a = Text.Split([Names], " "),
b = List.First(a) & List.Last(a),
c = Text.Select(b, {"A" .. "Z"})
][c]
),
b = List.Distinct(List.Difference(a[r], List.Distinct(a[r]))),
c = Table.Sort(Table.SelectRows(a, each List.Contains(b, [r])), {"Names"})[Names]
in
cPower Query solution 12 for Match Names with Same Initials, proposed by Krzysztof Kominiak:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
AddTmp = Table.AddColumn(
Source,
"tmp",
each Text.Combine(
List.Transform(
{Text.ToList(Text.Select([Names], {"A" .. "Z"}))},
each _{0} & List.Reverse(_){0}
)
)
),
Result = Table.Combine(
Table.SelectRows(
Table.Group(AddTmp, {"tmp"}, {{"Count", each Table.RowCount(_) > 1}, {"NT", each [[Names]]}}),
each [Count] = true
)[NT]
)
in
ResultPower Query solution 13 for Match Names with Same Initials, proposed by Alejandra Horvath CPA, CGA:
let
S = Table.Sort(Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], {"Names"}),
C = Table.AddColumn(S, "I", each Text.Split([Names], " ")),
T = Table.TransformColumns(
C,
{"I", each List.Transform(_, (x) => Text.Start(_{0}, 1) & Text.Start(List.Last(_), 1)){0}}
),
D =
let
cn = {"I"},
ac = Table.Group(T, cn, {{"C", Table.RowCount}}),
sd = Table.SelectRows(ac, each [C] > 1)[[I]]
in
Table.Join(T, cn, sd, cn, JoinKind.Inner)[[Names]]
in
DSolving the challenge of Match Names with Same Initials with Excel
Excel solution 1 for Match Names with Same Initials, proposed by Bo Rydobon 🇹🇭:
=LET(
z,
A2:A20,
FILTER(
z,
COUNTIF(
z,
LEFT(
z
)&"* "&LEFT(
TEXTAFTER(
z,
" ",
-1
)
)&"*"
)>1
)
)
=LET(
z,
A2:A20,
b,
LEFT(
z
)&LEFT(
TEXTAFTER(
z,
" ",
-1
)
),
FILTER(
z,
MAP(
b,
LAMBDA(
c,
SUM(
N(
b=c
)
)
)
)>1
)
)Excel solution 2 for Match Names with Same Initials, proposed by Rick Rothstein:
=LET(
n,
A2:A20,
i,
LEFT(
n
)&LEFT(
TEXTAFTER(
n,
" ",
-1
)
),
r,
REDUCE(
"",
UNIQUE(
i
),
LAMBDA(
a,
x,
LET(
f,
FILTER(
n,
i=x
),
VSTACK(
a,
IF(
COUNTA(
f
)>1,
f,
""
)
)
)
)
),
FILTER(
r,
LEN(
r
)
)
)Excel solution 3 for Match Names with Same Initials, proposed by John V.:
=LET(
n,
A2:A20,
i,
LEFT(
n
)&LEFT(
TEXTAFTER(
n,
" ",
-1
)
),
SORT(
FILTER(
n,
BYROW(
N(
i=TOROW(
i
)
),
SUM
)>1
)
)
)Excel solution 4 for Match Names with Same Initials, proposed by 🇰🇷 Taeyong Shin:
=LET(d,A2:A20,x,REGEXREPLACE(d,"(.).*(?<= )(.)|.","$1$2"),FILTER(d,MMULT(XMATCH(x,x,,{1,-1}),{-1;1})))Excel solution 5 for Match Names with Same Initials, proposed by Kris Jaganah:
=LET(
a,
A2:A20,
b,
LEFT(
a
)&LEFT(
TEXTAFTER(
a,
" ",
-1
)
),
c,
MAP(
b,
LAMBDA(
x,
SUM(
N(
x=b
)
)
)
),
SORT(
FILTER(
a,
c>1
)
)
)Excel solution 6 for Match Names with Same Initials, proposed by Julian Poeltl:
=LET(
N,
A2:A20,
C,
LEFT(
N,
1
)&LEFT(
TEXTAFTER(
N,
" ",
-1
),
1
),
G,
GROUPBY(
C,
N,
COUNTA,
,
0
),
DROP(
REDUCE(
0,
FILTER(
INDEX(
G,
,
1
),
INDEX(
G,
,
2
)>1
),
LAMBDA(
A,
B,
VSTACK(
A,
FILTER(
N,
C=B
)
)
)
),
1
)
)Excel solution 7 for Match Names with Same Initials, proposed by Aditya Kumar Darak 🇮🇳:
=LET(
_names,
A2:A20,
_fl,
LEFT(
_names
) & LEFT(
TEXTAFTER(
_names,
" ",
-1
)
),
_grp,
GROUPBY(
_fl,
_fl,
ROWS,
0,
0
),
_fltr,
FILTER(
TAKE(
_grp,
,
1
),
TAKE(
_grp,
,
-1
) > 1
),
_rtrn,
SORT(
FILTER(
_names,
ISNUMBER(
XMATCH(
_fl,
_fltr
)
)
)
),
_rtrn
)Excel solution 8 for Match Names with Same Initials, proposed by Timothée BLIOT:
=LET(A,A2:A20,B,LEFT(A)&LEFT(TEXTAFTER(A," ",-1)), SORT(FILTER(A,MAP(B,LAMBDA(x,SUM(--(x=B))>1)))))Excel solution 9 for Match Names with Same Initials, proposed by Hussein SATOUR:
=LET(
L,
LEFT,
F,
FILTER,
n,
A2:A20,
a,
L(
n
)&L(
TEXTAFTER(
n,
" ",
-1
)
),
SORT(
F(
n,
MAP(
a,
LAMBDA(
x,
COUNTA(
F(
a,
a=x
)
)
)
)-1
)
)
)Excel solution 10 for Match Names with Same Initials, proposed by Oscar Mendez Roca Farell:
=LET(
n,
A2:A20,
m,
LEFT(
n
)&LEFT(
TEXTAFTER(
n,
" ",
-1
)
),
FILTER(
n,
1-BYROW(
N(
m=TOROW(
m
)
),
SUM
)
)
)Excel solution 11 for Match Names with Same Initials, proposed by Sunny Baggu:
=LET(
_a,
LEFT(
A2:A20
) & LEFT(
TEXTAFTER(
A2:A20,
" ",
-1
)
),
_b,
UNIQUE(
_a
),
_c,
MAP(
_b,
LAMBDA(
a,
SUM(
N(
_a = a
)
)
)
),
_d,
FILTER(
_b,
_c > 1
),
DROP(
REDUCE(
"",
_d,
LAMBDA(
a,
v,
VSTACK(
a,
FILTER(
A2:A20,
_a = v
)
)
)
),
1
)
)Excel solution 12 for Match Names with Same Initials, proposed by Abdallah Ally:
= workbook.getActiveWorksheet();
const rngValues = curSheet.getRange(
'A2:A20'
).getValues() as string[][];
function getInitials(
name: string
): string {
const split = name.split(
' '
);
return split[0][0] + split[split.length - 1][0];
}
const Initials = rngValues.map(
x => getInitials(
x[0]
)
);
const filtered = rngValues
.filter((x, index) => Initials.filter(
y => y === Initials[index]
).length > 1)
.sort((x, y) => x[0].localeCompare(
y[0]
));
curSheet.getRange(
`C2:C${1 + filtered.length}`
).setValues(
filtered
)Excel solution 13 for Match Names with Same Initials, proposed by Abdallah Ally:
WITH CTE1 AS (
SELECT Names,
LEFT(
Names,
1
) + SUBSTRING(
Names,
LEN(
Names
) - CHARINDEX(
' ',
REVERSE(
Names
)
) + 2,
1
) AS Initials
FROM ExcelChallenge612
),
CTE2 AS (
SELECT Initials,
COUNT(
*
) AS Occurrences
FROM CTE1
GROUP BY Initials
)
SELECT CTE1.Names
FROM CTE1
INNER JOIN CTE2 ON CTE1.Initials = CTE2.Initials
ORDER BY CTE1.NamesExcel solution 14 for Match Names with Same Initials, proposed by Abdallah Ally:
=LET(f,
LAMBDA(
x,
LEFT(
x
)&LEFT(
TEXTAFTER(
x,
" ",
-1
)
)
),
a,
A2:A20,
b,
MAP(
a,
f
),
SORT(FILTER(a,
MAP(a,
LAMBDA(x,
SUM(--(f(
x
)=b))>1)))))Excel solution 15 for Match Names with Same Initials, proposed by Anshu Bantra:
=LET(
data_,
A2:A20,
full_ini_,
MAP(
data_,
LAMBDA(
val,
ARRAYTOTEXT(
LEFT(
TEXTSPLIT(
val,
" "
)
)
)
)
),
short_ini_,
BYROW(
full_ini_,
LAMBDA(
x,
TEXTJOIN(
", ",
,
LEFT(
x
),
RIGHT(
x
)
)
)
),
FILTER(
data_,
MAP(
short_ini_,
LAMBDA(
x,
SUM(
IF(
short_ini_=x,
1,
0
)
)
)
)>1
)
)Excel solution 16 for Match Names with Same Initials, proposed by Md. Zohurul Islam:
=LET(
z,
A2:A20,
a,
MAP(
z,
LAMBDA(
x,
LET(
p,
TEXTSPLIT(
x,
" "
),
q,
LEFT(
TAKE(
p,
,
1
)
),
s,
LEFT(
TAKE(
p,
,
-1
)
),
u,
q&s,
u
)
)
),
b,
MAP(
a,
LAMBDA(
y,
SUM(
ABS(
a=y
)
)
)
),
d,
SORT(
FILTER(
z,
b>1
)
),
d
)Excel solution 17 for Match Names with Same Initials, proposed by Pieter de B.:
=LET(a,SORT(A2:A20),b,LEFT(a)&LEFT(TEXTAFTER(a," ",-1)),FILTER(a,BYROW(b,LAMBDA(c,SUM(N(b=c))))-1))Excel solution 18 for Match Names with Same Initials, proposed by Hamidi Hamid:
=LET(
z,
HSTACK(
A2:A20,
LEFT(
A2:A20,
1
)&LEFT(
TEXTAFTER(
A2:A20,
" ",
-1
),
1
)
),
x,
MAP(
UNIQUE(
TAKE(
z,
,
-1
)
),
LAMBDA(
a,
TEXTJOIN(
"-",
,
FILTER(
TAKE(
z,
,
1
),
TAKE(
z,
,
-1
)=a
)
)
)
),
r,
FILTER(
x,
ISNUMBER(
SEARCH(
"-",
x
)
)
),
DROP(
REDUCE(
0,
r,
LAMBDA(
a,
b,
VSTACK(
a,
TEXTSPLIT(
b,
,
"-",
)
)
)
),
1
)
)Excel solution 19 for Match Names with Same Initials, proposed by ferhat CK:
=LET(r,A2:A20,b,LEFT(TEXTBEFORE(r," ",-1))&LEFT(TEXTAFTER(r," ",-1)),SORT(FILTER(r,ISNA(XMATCH(b,UNIQUE(b,,1))))))Excel solution 20 for Match Names with Same Initials, proposed by Andy Heybruch:
=LET(_names,A2:A20,
_initials,BYROW(_names,LAMBDA(a,LEFT(a)&MID(a,XMATCH(" ",MID(a,SEQUENCE(LEN(a)),1),0,-1)+1,1))),
_ct,GROUPBY(_initials,_initials,COUNTA,0,0,-2),
_filt,TAKE(FILTER(_ct,TAKE(_ct,,-1)>1),,1),
FILTER(_names,ISNUMBER(XMATCH(_initials,_filt))))Excel solution 21 for Match Names with Same Initials, proposed by Imam Hambali:
=LET(
n, A2:A20,
a, LEFT(TEXTAFTER(" "&n," ",SEQUENCE(,3),,1)),
cc, CHOOSECOLS,
i, cc(a,1)&IF(cc(a,3)<>"",cc(a,3),cc(a,2)),
g, GROUPBY(i,i,COUNTA,0,0),
FILTER(n, BYROW(i=TRANSPOSE(FILTER(cc(g,1),cc(g,2)>1)),OR))
)Excel solution 22 for Match Names with Same Initials, proposed by Gerson Pineda:
=LET(l,LEFT,t,SORT(A2:A20),i,l(t)&l(TEXTAFTER(t," ",-1)),FILTER(t,MAP(i,LAMBDA(x,SUM(N(x=i))))>1))Excel solution 23 for Match Names with Same Initials, proposed by Victor Momoh (MVP, MOS, R.Eng):
=LET(x,A2:A20,a,LEFT(x)&LEFT(TEXTAFTER(x," ",-1)),FILTER(x,1Excel solution 24 for Match Names with Same Initials, proposed by Nicolas Micot:
=LET(_names;
A2:A20;
_letters;
MAP(
_names;
LAMBDA(
l_name;
GAUCHE(
l_name;
1
)&GAUCHE(
PRENDRE(
FRACTIONNER.TEXTE(
l_name;
;
" "
);
-1
);
1
)
)
);
_hasDupplicateLetters;
MAP(_letters;
LAMBDA(l_letters;
SOMME(--(_letters=l_letters))>=2));
TRIER(
FILTRE(
_names;
_hasDupplicateLetters
)
))Excel solution 25 for Match Names with Same Initials, proposed by LUIS FLORENTINO COUTO CORTEGOSO:
=LET(m,
MAP(
A2:A20,
LAMBDA(
x,
CONCAT(
REGEXEXTRACT(
x,
"^([A-Z]).*([A-Z]).*$",
2
)
)
)
),
SORT(FILTER(A2:A20,
MAP(m,
LAMBDA(y,
SUM(--(y=m))))-1)))Excel solution 26 for Match Names with Same Initials, proposed by Md Ismail Hosen:
=LAMBDA(names,LET(_FirstNameFirstChar,LEFT(names,1),_LastNameFirstChar,MAP(names,LAMBDA(a,LEFT(TAKE(TEXTSPLIT(a," "),,-1),1))),_Mask,BYROW(HSTACK(_FirstNameFirstChar,_LastNameFirstChar),LAMBDA(row,SUM(--BYROW(row=HSTACK(_FirstNameFirstChar,_LastNameFirstChar),AND))>1)),_Result,FILTER(names,_Mask),_Result))(A2:A20)Excel solution 27 for Match Names with Same Initials, proposed by Songglod P.:
=LET(n,A2:A20,a,MAP(n,LAMBDA(x,LEFT(x)&LEFT(TEXTAFTER(x," ",-1)))),SORT(FILTER(n,MAP(a,LAMBDA(x,SUM(N(x=a))))>1)))Excel solution 28 for Match Names with Same Initials, proposed by Ben Warshaw:
=LET(
_Names, $A$2:$A$20,
_Step1, MAP(_Names, LAMBDA(x, LEFT(x, 1) & LEFT(TEXTAFTER(x, " ", -1), 1))),
_Step2, MAP(_Step1, LAMBDA(x, ROWS(FILTER(_Step1, _Step1 = x)) > 1)),
_Result, LET(a, IF(_Step2, _Names), FILTER(a, a <> FALSE)),
_Result
)Excel solution 29 for Match Names with Same Initials, proposed by Gabriel Pugliese:
=LET(m,
MAP(
A2:A20,
LAMBDA(
w,
LET(
a,
CONCAT(
LEFT(
TEXTSPLIT(
w,
" "
)
)
),
LEFT(
a
)&RIGHT(
a
)
)
)
),
i,
SCAN(0,
m,
LAMBDA(a,
v,
SUM(--(v=m)))),
SORT(FILTER(A2:A20,
(i>1))))Excel solution 30 for Match Names with Same Initials, proposed by Moshe Moses, FCCA:
=LET(rng,A2:A20,LEFT(TEXTBEFORE(rng," ",1),1)&LEFT(TEXTAFTER(rng," ",-1),1))
=SORT(FILTER(A2:A20,COUNTIF($B$2#,B2#)>1))Solving the challenge of Match Names with Same Initials with Python
Python solution 1 for Match Names with Same Initials, proposed by Konrad Gryczan, PhD:
import pandas as pd
import re
input = pd.read_excel(path, usecols="A", nrows=20)
test = pd.read_excel(path, usecols="B", nrows=5)
extract_initials = lambda name: ''.join(re.findall(r'[A-Z]', name)[:1] + re.findall(r'[A-Z]', name)[-1:])
result = input.assign(initials=input['Names']
.apply(extract_initials))
.groupby('initials')
.filter(lambda x: len(x) > 1)
.sort_values(by='Names').reset_index()
print(result['Names'].equals(test['Answer Expected'])) # True
Python solution 2 for Match Names with Same Initials, proposed by Abdallah Ally:
import pandas as pd
def get_initials(text):
split = text.split()
initials = split[0][0] + split[-1][0]
return initials
df = pd.read_excel(file_path, usecols='A')
# Perform data manipulation
initials = [get_initials(x) for x in df.Names]
df = df[df['Names'].map(lambda x: initials.count(get_initials(x)) > 1)]
df = df.sort_values(by='Names', ignore_index=True)
df
Solving the challenge of Match Names with Same Initials with Python in Excel
Python in Excel solution 1 for Match Names with Same Initials, proposed by Alejandro Campos:
import re
df = pd.DataFrame({"Names": [name.strip() for name in xl("A2:A20")[0]]})
df_result = (df.assign(init=df['Names'].apply(lambda n: ''
.join(re.findall(r'[A-Z]', n)[::len(re.findall(r'[A-Z]', n))-1])))
.groupby('init').filter(lambda x: len(x) > 1)
.sort_values('Names').reset_index(drop=True)).drop(columns='init')
df_result
Python in Excel solution 2 for Match Names with Same Initials, proposed by Aditya Kumar Darak 🇮🇳:
data = xl("A1:A20", headers=True)
data["H"] = [i[0] + i.split()[-1][0] for i in data["Names"]]
result = data[data["H"].isin(data[data["H"].duplicated()]["H"])].reset_index(drop=True)
result = result.drop(columns="H")
result
Python in Excel solution 3 for Match Names with Same Initials, proposed by Anshu Bantra:
def get_initials(name):
initials = ''.join([part[0].upper() for part in name.split()])
return ''.join([initials[0]+initials[-1]])
df = xl("A1:A20", headers=True)
df['Initials'] = df['Names'].apply(get_initials)
initial_counts = df['Initials'].value_counts()
df['Is_Duplicate'] = df['Initials'].map(lambda x: initial_counts[x] > 1)
df[df['Is_Duplicate']].sort_values('Names').reset_index(drop=True['Names']
Python in Excel solution 4 for Match Names with Same Initials, proposed by Ümit Barış Köse, MSc:
data = xl("A1:A20", headers=True)
data["G"] = data["Names"].apply(lambda i: i[0&] + i.split()[-1][0])
result = data.sort_values(by="Names")
result = result[result["G"].duplicated(keep=False)]
result = result.drop(columns="G").reset_index(drop=True)
Solving the challenge of Match Names with Same Initials with R
R solution 1 for Match Names with Same Initials, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel(path, range = "A1:A20")
test = read_excel(path, range = "B1:B6")
result = input %>%
mutate(initials = str_extract_all(Names, "[A-Z]") %>%
map_chr(~ paste0(.[1], .[length(.)]))) %>%
filter(n() > 1, .by = initials) %>%
select(Names) %>%
arrange(Names)
all.equal(result$Names, test$`Answer Expected`)
# [1] TRUE
&