Provide a formula to find common unique alphabets between 2 cells in same row. Hence for “deer” and “seer”, answer would be “er”.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 40
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Find Common Letters Between Words with Power Query
Power Query solution 1 for Find Common Letters Between Words, proposed by Brian Julius:
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"NY1LCsAgDETvkrU3Ehd+BhGCpX7b2zcqXQTmzSQTrWmSkjFKk3VedID7KQjeE6W92xmdWy8Qs9iBvD2BdbbkVaznleLx4NORoyhFkWHP/skUbeBUGzLWm5oYuZExHw==",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [#"Text1 " = _t, Text2 = _t]
),
FixName = Table.RenameColumns(Source, {{"Text1 ", "Text1"}}),
ToListA = Table.AddColumn(FixName, "X", each Text.ToList([Text1])),
ToListB = Table.AddColumn(ToListA, "Y", each Text.ToList([Text2])),
Intersect = Table.AddColumn(ToListB, "Expected Answer", each List.Intersect({[X], [Y]})),
Extract = Table.TransformColumns(
Intersect,
{"Expected Answer", each Text.Combine(List.Transform(_, Text.From)), type text}
),
Clean = Table.RemoveColumns(Extract, {"X", "Y"})
in
CleanPower Query solution 2 for Find Common Letters Between Words, proposed by Matthias Friedmann:
let
Source = Excel.CurrentWorkbook(){[Name = "CommonAlphabets"]}[Content],
#"Added Custom" = Table.AddColumn(
Source,
"Common Unique Alpabet",
each List.Distinct(List.Intersect({Text.ToList([#"Text1 "]), Text.ToList([#"Text2"])}))
),
#"Extracted Values" = Table.TransformColumns(
#"Added Custom",
{"Common Unique Alpabet", each Text.Combine(List.Transform(_, Text.From)), type text}
)[[Common Unique Alpabet]]
in
#"Extracted Values"Power Query solution 3 for Find Common Letters Between Words, proposed by Antriksh Sharma:
let
Source = DataSource,
AddedCustom = Table.AddColumn(
Source,
"Result",
each Text.Combine(
List.Intersect({Text.ToList([Text1]), Text.ToList([Text2])}, Comparer.OrdinalIgnoreCase)
),
type text
)
in
AddedCustomPower Query solution 4 for Find Common Letters Between Words, proposed by Venkata Rajesh:
List.Accumulate(
List.Distinct(Text.ToList([#"Text1 "])),
"",
(s, c) => if Text.Contains([Text2], c) then s & c else s & ""
)Power Query solution 5 for Find Common Letters Between Words, proposed by Venkata Rajesh:
Text.Combine(List.Intersect({Text.ToList([#"Text1 "]), Text.ToList([Text2])}))Power Query solution 6 for Find Common Letters Between Words, proposed by Sergei Baklan:
let
Source = Excel.CurrentWorkbook(){[Name = "range"]}[Content],
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
Answer = Table.SelectColumns(
Table.AddColumn(
#"Promoted Headers",
"Answer",
each Text.Combine(
List.Intersect(List.Transform(Record.FieldValues(_), (q) => Text.ToList(q ?? "")))
)
),
{"Answer"}
)
in
AnswerPower Query solution 7 for Find Common Letters Between Words, proposed by Sue Bayes:
let
Source = Data,
TextToList = Table.AddColumn(Source, "Custom", each Text.ToList([#"Text1 "] ?? "") ),
TextToList2 = Table.AddColumn (TextToList, "Custom1", each Text.ToList([Text2] ?? "") ),
ListIntersect = Table.AddColumn(TextToList2, "Answer", each List.Intersect({[Custom], [Custom1]})),
Extract = Table.RemoveColumns(
Table.TransformColumns(
ListIntersect, {"Answer", each Text.Combine(List.Transform(_, Text.From)), type text}),
{"Custom", "Custom1"})
in
Extract
a little bit of Coalesce Melissa de Korte 😁
https://www.youtube.com/watch?v=qnrHia7ELyU
hashtag#powerqueryeverything
Solving the challenge of Find Common Letters Between Words with Excel
Excel solution 1 for Find Common Letters Between Words, proposed by Rick Rothstein:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
a,
b,
LET(
C,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
IFERROR(
CONCAT(
UNIQUE(
FILTER(
C,
ISNUMBER(
FIND(
C,
b
)
)
)
)
),
""
)
)
)
)Excel solution 2 for Find Common Letters Between Words, proposed by Rick Rothstein:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
a,
b,
LET(
c,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
CONCAT(
UNIQUE(
IF(
ISNUMBER(
FIND(
c,
b
)
),
c,
""
)
)
)
)
)
)Excel solution 3 for Find Common Letters Between Words, proposed by John V.:
=LET(
f,
LAMBDA(
x,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)
),
IFERROR(
MAP(
A2:A10,
B2:B10,
LAMBDA(
a,
b,
CONCAT(
UNIQUE(
XLOOKUP(
f(
a
),
f(
b
),
f(
b
),
""
)
)
)
)
),
""
)
)
An another (similar to already proposed):
=MAP(
A2:A10,
B2:B10,
LAMBDA(
a,
b,
LET(
c,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
CONCAT(
UNIQUE(
IF(
ISERR(
FIND(
c,
b
)
),
"",
c
)
)
)
)
)
)Excel solution 4 for Find Common Letters Between Words, proposed by محمد حلمي:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
a,
b,
LET(
c,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
IFERROR(
CONCAT(
UNIQUE(
FILTER(
c,
IFNA(
XMATCH(
c,
MID(
b,
SEQUENCE(
LEN(
b
)
),
1
)
),
)
)
)
),
""
)
)
)
)Excel solution 5 for Find Common Letters Between Words, proposed by 🇰🇷 Taeyong Shin:
=LET(
Func,
LAMBDA(
a,
b,
[is_intersect],
LET(
intersect,
IF(
ISOMITTED(
is_intersect
),
TRUE,
is_intersect
),
txt_1,
IFERROR(
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
""
),
txt_2,
IFERROR(
MID(
b,
SEQUENCE(
LEN(
b
)
),
1
),
""
),
IF(
intersect,
CONCAT(
UNIQUE(
XLOOKUP(
txt_1,
txt_2,
txt_2,
""
)
)
),
CONCAT(
UNIQUE(
VSTACK(
UNIQUE(
txt_1
),
UNIQUE(
txt_2
)
),
,
1
)
)
)
)
),
IFERROR(
HSTACK(
MAP(
A2:A11,
B2:B11,
LAMBDA(
x,
y,
Func(
x,
y
)
)
),
MAP(
A2:A11,
B2:B11,
LAMBDA(
x,
y,
Func(
x,
y,
FALSE
)
)
)
),
""
)
)Excel solution 6 for Find Common Letters Between Words, proposed by Julian Poeltl:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
T,
TT,
LET(
S,
MID(
T,
SEQUENCE(
LEN(
T
)
),
1
),
ST,
MID(
TT,
SEQUENCE(
LEN(
TT
)
),
1
),
U,
UNIQUE(
S
),
IFERROR(
CONCAT(
FILTER(
U,
ISNUMBER(
XMATCH(
U,
ST
)
)
)
),
""
)
)
)
)Excel solution 7 for Find Common Letters Between Words, proposed by Aditya Kumar Darak 🇮🇳:
= MAP(
A2:A10,
B2:B10,
LAMBDA(
a,
b,
LET(
_splt,
MID(
a,
SEQUENCE(
MAX(
LEN(
a
),
1
)
),
1
),
_calc,
ISNUMBER(
SEARCH(
_splt,
b
)
),
CONCAT(
UNIQUE(
FILTER(
_splt,
_calc,
""
)
)
)
)
)
)
Sol. 2 with the COUNTIFS function:
= MAP(
A2:A10,
B2:B10,
LAMBDA(
a,
b,
LET(
_splt,
MID(
a,
SEQUENCE(
MAX(
LEN(
a
),
1
)
),
1
),
_calc,
COUNTIFS(
b,
"*" & _splt & "*"
),
CONCAT(
UNIQUE(
FILTER(
_splt,
_calc,
""
)
)
)
)
)
)
Sol. 3 with IF function:
= MAP(
A2:A10,
B2:B10,
LAMBDA(
a,
b,
LET(
_splt,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
_calc,
ISNUMBER(
SEARCH(
_splt,
b
)
),
CONCAT(
UNIQUE(
IF(
_calc,
_splt,
""
)
)
)
)
)
)Excel solution 8 for Find Common Letters Between Words, proposed by Timothée BLIOT:
=LET(
Text1,
UNIQUE(
MID(
$A2,
SEQUENCE(
LEN(
& $A2
)
),
1
)
),
Text2,
UNIQUE(
MID(
$B2,
SEQUENCE(
LEN(
$B2
)
),
1
)
),
TEXTJOIN(
,
1,
IFERROR(
FILTER(
Text1,
ISNUMBER(
XMATCH(
Text1,
Text2,
0
)
),
""
),
""
)
)
)Excel solution 9 for Find Common Letters Between Words, proposed by Duy Tùng:
=IFERROR(
MAP(
B2:B10,
LAMBDA(
x,
LET(
a,
MID(
@+A10:x,
ROW(
1:20
),
1
),
CONCAT(
UNIQUE(
FILTER(
a,
REGEXTEST(
x,
a
)
)
)
)
)
)
),
""
)Excel solution 10 for Find Common Letters Between Words, proposed by Bhavya Gupta:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
t_1,
t_2,
LET(
a,
IFERROR(
MID(
t_1,
SEQUENCE(
LEN(
t_1
)
),
1
),
""
),
CONCAT(
UNIQUE(
FILTER(
a,
ISNUMBER(
FIND(
a,
t_2
)
),
""
)
)
)
)
)
)Excel solution 11 for Find Common Letters Between Words, proposed by Charles Roldan:
=BYROW(A2:B10,
LAMBDA(x,
LET(a,
UNIQUE(LAMBDA(
y,
MID(
y,
SEQUENCE(
LEN(
y
)
),
1
)
)(CONCAT(
x
))),
CONCAT(
REPT(
a,
BYROW(
ISNUMBER(
FIND(
a,
x
)
),
AND
)
)
))))Excel solution 12 for Find Common Letters Between Words, proposed by Jardiel Euflázio:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
a,
b,
LET(
c,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
CONCAT(
UNIQUE(
IF(
ISNUMBER(
SEARCH(
c,
b
)
),
c,
""
)
)
)
)
)
)Excel solution 13 for Find Common Letters Between Words, proposed by Jardiel Euflázio:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
a,
b,
LET(
c,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
CONCAT(
UNIQUE(
IF(
COUNTIF(
b,
"*"&c&"*"
),
c,
""
)
)
)
)
)
)Excel solution 14 for Find Common Letters Between Words, proposed by Jardiel Euflázio:
=MAP(
A2:A10,
B2:B10,
LAMBDA(
a,
b,
LET(
c,
SEQUENCE(
LEN(
a
)
),
d,
MID(
a,
c,
1
),
CONCAT(
UNIQUE(
IF(
ISNUMBER(
MATCH(
"*"&d&"*",
b,
0
)
),
d,
""
)
)
)
)
)
)Excel solution 15 for Find Common Letters Between Words, proposed by Victor Momoh (MVP, MOS, R.Eng):
=MAP(
$A$2:$A$10,
$B$2:$B$10,
LAMBDA(
a,
b,
LET(
p,
MID(
a,
SEQUENCE(
,
1+LEN(
a
)
),
1
),
CONC
AT(
UNIQUE(
FILTER(
p,
COUNTIF(
b,
"*"&p&"*"
),
""
),
1
)
)
)
)
)Excel solution 16 for Find Common Letters Between Words, proposed by Miguel Angel Franco García:
=LET(
a;
EXTRAE(
A2;
SECUENCIA(
LARGO(
A2
)
);
1
);
b;
EXTRAE(
B2;
SECUENCIA(
LARGO(
B2
)
);
1
);
UNIRCADENAS(
"";
VERDADERO;
SI(
ESNUMERO(
COINCIDIR(
a;
b;
0
)
);
a;
""
)
)
)