Provide a formula to list the names where all words start with same alphabet.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 45
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Same Initial Letter Names with Power Query
Power Query solution 1 for Same Initial Letter Names, proposed by Omid Motamedisedeh:
let
Source = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
Custom1 = List.Select(
Source[Name],
(ox) => (
try
List.Count(
List.Distinct(List.Transform(Text.Split(ox, " "), (ix) => Text.At(Text.Lower(ix), 0)))
)
otherwise
0
)
= 1 and Text.Contains(ox, " ")
)
in
Custom1Power Query solution 2 for Same Initial Letter Names, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Calculation = Table.AddColumn(
Source,
"Calculation",
each try
[
a = Text.Split([Name], " "),
b = List.Transform(a, (f) => Text.Start(f, 1)),
c = List.Count(a) > 1 and List.Count(List.Distinct(b)) = 1
][c]
otherwise
false
),
Result = Table.SelectRows(Calculation, each ([Calculation] = true))[[Name]]
in
ResultPower Query solution 3 for Same Initial Letter Names, proposed by Luan Rodrigues:
let
Fonte = Excel.CurrentWorkbook(){[Name = "Tabela1"]}[Content],
Result = Table.SelectRows(
Table.AddColumn(
Fonte,
"Personalizar",
each [
x = Text.Select([Name], {"A" .. "Z", " "}),
y = List.Count(
try List.Select(try Text.ToList(x) otherwise null, each _ <> " ") otherwise null
),
a = List.Select(
try List.Select(try Text.ToList(x) otherwise null, each _ <> " ") otherwise null,
each y > 1
),
z = try List.ContainsAll(Text.ToList(a{0}), a) otherwise null
][z]
),
each [Personalizar] = true
)[Name]
in
ResultPower Query solution 4 for Same Initial Letter Names, proposed by Brian Julius:
let
Source = NamesRaw,
#"Duplicate Column" = Table.DuplicateColumn(Source, "Name", "Name - Copy"),
#"Split Column by Delimiter" = Table.SplitColumn(
#"Duplicate Column",
"Name - Copy",
Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv),
{"Name - Copy.1", "Name - Copy.2", "Name - Copy.3"}
),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(
#"Split Column by Delimiter",
{"Name"},
"Attribute",
"Value"
),
#"Split Column by Position" = Table.SplitColumn(
#"Unpivoted Other Columns",
"Value",
Splitter.SplitTextByPositions({0, 1}, false),
{"Value.1", "Value.2"}
),
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Position", {"Attribute", "Value.2"}),
#"Grouped Rows" = Table.Group(
#"Removed Columns",
{"Name"},
{
{"Count", each Table.RowCount(_), Int64.Type},
{"DistinctLetters", each Table.RowCount(Table.Distinct(_)), Int64.Type}
}
),
#"Filtered Rows" = Table.SelectRows(
#"Grouped Rows",
each ([Count] <> 1) and ([DistinctLetters] = 1)
),
#"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows", {"Count", "DistinctLetters"})
in
#"Removed Columns1"Power Query solution 5 for Same Initial Letter Names, proposed by Matthias Friedmann:
let
Source = Excel.CurrentWorkbook(){[Name = "NameAllit"]}[Content],
#"Filtered Rows" = Table.SelectRows(
Source,
each try List.Count(List.Intersect({Text.ToList([Name]), {"A" .. "Z", " "}})) = 2 otherwise false
)
in
#"Filtered Rows"Power Query solution 6 for Same Initial Letter Names, proposed by Antriksh Sharma:
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"LY2xCsMwDER/RXjuTwSnUOhSQqGDyaAmApsokpHT/6+rdBGn0+ldSmHCJRPDDZnDfElhVEFeYfwsm+/XdmQymPRNdjS3fNzxyEwk0AXBC3kj88OTjBq6jGgMUbV2QMS9/hO/V7IiBMOK+8k8+b0mS1Nxa0BTgUcuzKX21PwF",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [Name = _t]
),
Result = List.Transform(
Source[Name],
(Current) =>
let
Names = Text.Split(Current, " "),
Transformation = List.Transform(Names, each Text.Start(_, 1)),
OneChar = List.Count(List.Distinct(Transformation)) = 1,
WordCount = List.Count(Names) > 1,
Result = if OneChar and WordCount then Current else null
in
Result
),
RemoveNulls = {List.RemoveNulls(Result)},
ToTable = Table.FromColumns(RemoveNulls, type table [Names = text])
in
ToTablePower Query solution 7 for Same Initial Letter Names, proposed by Victor Wang:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Select = List.Select(
Source[Name],
(a) =>
let
s = List.Transform(Text.Split(a, " "), each Text.Start(_, 1))
in
not (try s{1})[HasError] and List.Count(List.Distinct(s)) = 1
)
in
SelectPower Query solution 8 for Same Initial Letter Names, proposed by Cristian Angyal:
let
Source = Table.PromoteHeaders(
Excel.CurrentWorkbook(){[Name = "data"]}[Content],
[PromoteAllScalars = true]
),
Added_Index = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
Split_to_Rows = Table.ExpandListColumn(
Table.TransformColumns(
Added_Index,
{
{
"Name",
Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv),
let
itemType = (type nullable text) meta [Serialized.Text = true]
in
type {itemType}
}
}
),
"Name"
),
GetFirstCharacter = Table.TransformColumns(
Split_to_Rows,
{{"Name", each Text.Start(_, 1), type text}}
),
Grouped_Rows_by_Index = Table.Group(
GetFirstCharacter,
{"Index"},
{
{"Words In Name", each Table.RowCount(_), Int64.Type},
{"Distinct First Letters", each Table.RowCount(Table.Distinct(_)), Int64.Type}
}
),
Filter = Table.SelectRows(
Grouped_Rows_by_Index,
each ([Distinct First Letters] = 1) and ([Words In Name] <> 1)
),
Inner_Join = Table.NestedJoin(Added_Index, {"Index"}, Filter, {"Index"}, "Filter", JoinKind.Inner),
Keep_only_Result = Table.SelectColumns(Inner_Join, {"Name"})
in
Keep_only_ResultPower Query solution 9 for Same Initial Letter Names, proposed by Shubham Vashisht:
let
Source = Excel.CurrentWorkbook(){[Name = "Data"]}[Content],
FilteredNull = Table.SelectRows(Source, each ([Name] <> null)),
Newcolumn = Table.AddColumn(
FilteredNull,
"Result",
each
if List.Count(List.Distinct(List.Transform(Text.Split([Name], " "), each Text.Start(_, 1))))
= 1
then
[Name]
else
null
),
Filtercolumn = Table.SelectRows(Newcolumn, each ([Result] <> null))
in
FiltercolumnPower Query solution 10 for Same Initial Letter Names, proposed by Shubham Vashisht:
let
Source = Excel.CurrentWorkbook(){[Name = "Data"]}[Content],
FilteredNull = Table.SelectRows(Source, each ([Name] <> null)),
Newcolumn = Table.AddColumn(
FilteredNull,
"Result",
each
if List.Count(List.Distinct(List.Transform(Text.Split([Name], " "), each Text.Start(_, 1))))
= 1
then
[Name]
else
null
),
Filtercolumn = Table.SelectRows(Newcolumn, each ([Result] <> null)),
#"Removed Columns" = Table.RemoveColumns(Filtercolumn, {"Name"})
in
#"Removed Columns"Power Query solution 11 for Same Initial Letter Names, proposed by Jan Berny:
let
Source = Excel.CurrentWorkbook(){[Name = "TableOfNames"]}[Content],
Filter1 = Table.SelectRows(Source, each ([Name] <> null)),
Duplicate = Table.DuplicateColumn(Filter1, "Name", "Name2"),
Get_NbOfWords = Table.AddColumn(
Duplicate,
"NbOfWords",
each List.Count(Text.Split([#"Name2"], " "))
),
Filter2 = Table.SelectRows(Get_NbOfWords, each ([NbOfWords] <> 1)),
NbOfWords = List.Max(Filter2[NbOfWords]),
ReadyToSplit = Table.RemoveColumns(Filter2, {"NbOfWords"}),
Split = Table.SplitColumn(
ReadyToSplit,
"Name2",
Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv),
NbOfWords
),
UnPivot = Table.UnpivotOtherColumns(Split, {"Name"}, "Atribut", "Value"),
Extract = Table.TransformColumns(UnPivot, {{"Value", each Text.Start(_, 1), type text}}),
UpperLetter = Table.TransformColumns(Extract, {{"Value", Text.Upper, type text}}),
GroupByNameLetter = Table.Group(
UpperLetter,
{"Name", "Value"},
{{"NbOfLines", each Table.RowCount(_), Int64.Type}}
),
GroupByName = Table.Group(
GroupByNameLetter,
{"Name"},
{{"NbOfLines2", each Table.RowCount(_), Int64.Type}}
),
Filter3 = Table.SelectRows(GroupByName, each ([NbOfLines2] = 1)),
Final = Table.RemoveColumns(Filter3, {"NbOfLines2"})
in
FinalSolving the challenge of Same Initial Letter Names with Excel
Excel solution 1 for Same Initial Letter Names, proposed by Rick Rothstein:
=LET(
a,
A2:A11,
FILTER(
a,
IF(
ISNUMBER(
FIND(
" ",
a
)
),
LEN(
SUBSTITUTE(
a,
LEFT(
a
),
""
)
)+1=LEN(
SUBSTITUTE(
a,
" ",
""
)
),
)
)
)Excel solution 2 for Same Initial Letter Names, proposed by John V.:
=FILTER(
A2:A11,
MAP(
A2:A11,
LAMBDA(
x,
AND(
LEFT(
TEXTSPLIT(
x&0,
" "
)
)=LEFT(
x
),
COUNTIF(
x,
"* *"
)
)
)
)
)Excel solution 3 for Same Initial Letter Names, proposed by محمد حلمي:
=LEFT(
a
),
ISNUMBER(
FIND(
" ",
a
)
)))))Excel solution 4 for Same Initial Letter Names, proposed by محمد حلمي:
=FILTER(A2:A11,
MAP(A2:A11&"0",
LAMBDA(a,
LET(d,
TEXTSPLIT(
a,
" "
),
(SUM(--(LEFT(
d
)=LEFT(
a
)))=COUNTA(
d
))*COUNTA(
d
)>1))))Excel solution 5 for Same Initial Letter Names, proposed by محمد حلمي:
=FILTER(A2:A11,
MAP(A2:A11&"0",
LAMBDA(a,
(SUM(--(LEFT(
TEXTSPLIT(
a,
" "
)
)=LEFT(
a
)))=COUNTA(
TEXTSPLIT(
a,
" "
)
))*COUNTA(
TEXTSPLIT(
a,
" "
)
)>1)))Excel solution 6 for Same Initial Letter Names, proposed by 🇰🇷 Taeyong Shin:
=TOCOL(
REGEXEXTRACT(
A2:A11,
"^(w)S*(?: 1S*)+$"
),
2
)Excel solution 7 for Same Initial Letter Names, proposed by 🇰🇷 Taeyong Shin:
=LET(
d,
A2:A11,
c,
REGEXREPLACE(
d,
"b(w)|.",
"$1"
),
FILTER(
d,
REGEXTEST(
c,
"^(?!$)"&LEFT(
c
)&"{2,}$"
)
)
)Excel solution 8 for Same Initial Letter Names, proposed by Julian Poeltl:
=LET(
N,
A2:A11,
FILTER(
N,
IFERROR(
MAP(
N,
LAMBDA(
A,
LET(
L,
LEFT(
TEXTSPLIT(
A,
,
" "
),
1
),
SUM(
--DROP(
DROP(
L,
1
)=L,
-1
)
)=ROWS(
L
)-1
)
)
),
0
)
)
)Excel solution 9 for Same Initial Letter Names, proposed by Aditya Kumar Darak 🇮🇳:
= FILTER(
A2:A11,
MAP(
A2:A11,
LAMBDA(
a,
IF(
COUNTIFS(
a,
"* *"
),
AND(
CODE(
TEXTSPLIT(
a,
" "
)
) = CODE(
a
)
)
)
)
)
)Excel solution 10 for Same Initial Letter Names, proposed by Timothée BLIOT:
=LET(
Name,
A2:A11,
NameSplit,
MAKEARRAY(
ROWS(
Name
),
10,
LAMBDA(
a,
b,
IFERROR(
INDEX(
TEXTSPLIT(
INDEX(
Name,
a
),
" ",
,
TRUE
),
b
),
0
)
)
),
FirstLetter,
LEFT(
NameSplit,
1
),
FirstLetterPrev,
SUBSTITUTE(
HSTACK(
INDEX(
FirstLetter,
,
1
),
TAKE(
FirstLetter,
,
COLUMNS(
FirstLetter
)-1
)
),
0,
"#"
),
Compare,
--(FirstL&etter=FirstLetterPrev),
SumCompare,
BYROW(
Compare,
LAMBDA(
a,
SUM(
a
)
)
),
NameCount,
BYROW(
Name,
LAMBDA(
a,
LEN(
a
)-LEN(
SUBSTITUTE(
a,
" ",
""
)
)+1
)
),
Answer,
IF((SumCompare=NameCount)*(NameCount>1),
1,
0),
FILTER(
Name,
Answer,
""
))Excel solution 11 for Same Initial Letter Names, proposed by Hussein SATOUR:
=LET(a,
A2:A11,
b,
BYROW(
a,
LAMBDA(
x,
COUNTA(
UNIQUE(
LEFT(
TEXTSPLIT(
x,
,
" "
)
)
)
)
)
),
FILTER(a,
(a<>"") * (ISNUMBER(
FIND(
" ",
a
)
)) * (b=1)))Excel solution 12 for Same Initial Letter Names, proposed by Duy Tùng:
=FILTER(
A2:A11,
MAP(
A2:A11,
LAMBDA(
x,
IFERROR(
LET(
a,
LEFT(
TEXTSPLIT(
x,
" "
)
),
AND(
@a=a,
SUM(
LEN(
a
)
)>1
)
),
)
)
)
)Excel solution 13 for Same Initial Letter Names, proposed by Charles Roldan:
=DROP(
REDUCE(
FALSE,
A2:A11,
LAMBDA(
_List,
_Name,
LET(
_Initials,
LEFT(
TEXTSPLIT(
_Name,
" "
)
),
IF(
AND(
COUNTA(
_Initials
)>1,
COUNTA(
UNIQUE(
_Initials,
TRUE
)
)=1
),
VSTACK(
_List,
_Name
),
_List
)
)
)
),
1
)Excel solution 14 for Same Initial Letter Names, proposed by Jardiel Euflázio:
=LET(a,
A2:A11,
FILTER(a,
BYROW(a,
LAMBDA(a,
LET(b,
LEFT(
TEXTSPLIT(
a&" ",
,
" "
)
),
(LEN(
CONCAT(
UNIQUE(
b
)
)
)=1)*(LEN(
CONCAT(
b
)
)>1))))))Excel solution 15 for Same Initial Letter Names, proposed by Victor Momoh (MVP, MOS, R.Eng):
=FILTER(
A2:A11,
IFERROR(
MAP(
A2:A11,
LAMBDA(
x,
LET(
a,
CONCAT(
LEFT(
TEXTSPLIT(
x,
"
"
)
)
),
a=REPT(
LEFT(
a
),
LEN(
a
)
)
)
)
),
0
)
)Excel solution 16 for Same Initial Letter Names, proposed by Philippe Brillault:
=FILTER(
A2:A11,
REGEXTEST(
A2:A11,
"^(w).*(1)[a-z]+$"
)
)