An anagram is a word which is made by arranging the letters of another word. For ex. dues, sued, used are anagrams of each other. Find the list of words which are anagrams of each other. List the group of words in different rows.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 282
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Group Words by Anagrams with Power Query
Power Query solution 1 for Group Words by Anagrams, proposed by Omid Motamedisedeh:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Custom2 = Table.SelectRows(
Table.Distinct(
Table.TransformColumns(
Source,
{
"Words",
(x) =>
List.Sort(
List.Select(Source[Words], each List.Sort(Text.ToList(_)) = List.Sort(Text.ToList(x)))
)
}
)
),
each List.Count(_[Words]) > 1
),
#"Extracted Values" = Table.TransformColumns(
Custom2,
{"Words", each Text.Combine(List.Transform(_, Text.From), ","), type text}
)
in
#"Extracted Values"Power Query solution 2 for Group Words by Anagrams, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Ans = Table.FromColumns(
List.Zip(
List.RemoveNulls(
Table.Group(
Table.AddColumn(Source, "K", each List.Sort(Text.ToList([Words]))),
"K",
{"T", each if Table.RowCount(_) > 1 then [Words] else null}
)[T]
)
)
)
in
AnsPower Query solution 3 for Group Words by Anagrams, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
S = Table.FromRows(
Table.ToColumns(
Table.FromColumns(
Table.SelectRows(
Table.Group(
Table.AddColumn(Source, "T", each List.Sort(Text.ToList([Words]))),
{"T"},
{{"A", each [Words]}, {"C", each Table.RowCount(_)}}
),
each ([C] <> 1)
)[A]
)
)
)
in
SPower Query solution 4 for Group Words by Anagrams, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Helper = Table.AddColumn(Source, "H", each Text.Combine(List.Sort(Text.ToList([Words])))),
Group = Table.Group(
Helper,
"H",
{{"All", each Table.FromRows({[Words]})}, {"Count", Table.RowCount}}
),
Filter = Table.SelectRows(Group, each [Count] > 1),
Return = Table.Combine(Filter[All])
in
ReturnPower Query solution 5 for Group Words by Anagrams, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
AddCol = Table.AddColumn(Source, "Custom", each Text.Combine(List.Sort(Text.ToList([Words])))),
Group = Table.Combine(
Table.SelectRows(
Table.Group(
AddCol,
{"Custom"},
{{"Count", each List.Count(_)}, {"All", each Table.FromRows({[Words]})}}
),
each [Count] > 1
)[All]
)
in
GroupPower Query solution 6 for Group Words by Anagrams, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
add = Table.AddColumn(Fonte, "Personalizar", each Text.Combine(List.Sort(Text.ToList([Words])))),
gp = Table.Group(add, {"Personalizar"}, {{"Contagem", each _[Words]}}),
res = Table.Transpose(
Table.FromColumns(Table.SelectRows(gp, each List.Count([Contagem]) > 1)[Contagem])
)
in
resPower Query solution 7 for Group Words by Anagrams, proposed by Rafael González B.:
let
Source = Excel.CurrentWorkbook(){0}[Content],
R = Table.AddColumn(
Source,
"Texts",
each
let
a = Text.ToList([Words]),
aa = Source[Words],
b = List.Transform(aa, each List.Sort(Text.ToList(_))),
c = List.Transform(b, each _ = List.Sort(a)),
d = Table.FromColumns({aa, c}),
e = Table.SelectRows(d, each [Column2] = true),
f = e[Column1],
g = if List.Count(f) > 1 then Text.Combine(f, ",") else null
in
g
)[[Texts]],
NoNull = Table.Distinct(Table.SelectRows(R, each ([Texts] <> null))),
Result = Table.SplitColumn(
NoNull,
"Texts",
Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv),
{"1", "2", "3", "4"}
)
in
ResultPower Query solution 8 for Group Words by Anagrams, proposed by Szabolcs Phraner:
let
Source = Excel.CurrentWorkbook(){[Name = "Words"]}[Content],
//Buffer the list of all words
WordList = List.Buffer(Table.Column(Source, "Words")),
Anagrams = Table.AddColumn(
Source,
"Anagram",
each
let
//create a sorted list of each word characters
word = [Words],
chars = List.Sort(Text.ToList(word)),
//Filter select words, from the total word list, where the sorted list of characters equals the word variable, but does not equal the word itself
Anagrams = List.Select(WordList, each List.Sort(Text.ToList(_)) = chars and _ <> word)
in
Text.Combine(Anagrams, ", ")
),
//Count the max amount Anagrams based on the occurrence of the delimiter ", "
MaxAnagram = List.Max(
Table.AddColumn(Anagrams, "count", each List.Count(Text.PositionOf([Anagram], ", ", 2)))[count]
)
+ 1,
//Create Column Names based on the max amount of Anagrams
ColNames = List.Transform({1 .. MaxAnagram}, each "Anagram no. " & Text.From(_)),
//Split the Anagram column, using the ColNames variable
Split_AnagramCol = Table.SplitColumn(
Anagrams,
"Anagram",
Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv),
ColNames
)
in
Split_AnagramColSolving the challenge of Group Words by Anagrams with Excel
Excel solution 1 for Group Words by Anagrams, proposed by Bo Rydobon 🇹🇭:
=LET(
z,
A2:A20,
y,
MAP(
z,
LAMBDA(
a,
CONCAT(
SORT(
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
)
)
)
)
),
DROP(
REDUCE(
"",
UNIQUE(
y
),
LAMBDA(
a,
v,
LET(
u,
FILTER(
z,
y=v
),
IF(
ROWS(
u
)>1,
IFNA(
VSTACK(
a,
TOROW(
u
)
),
""
),
a
)
)
)
),
1
)
)Excel solution 2 for Group Words by Anagrams, proposed by John V.:
=LET(w,A2:A20,b,MAP(w,LAMBDA(x,CONCAT(SORT(MID(x,ROW(1:9),1))))),u,UNIQUE(b),IFNA(DROP(REDUCE(0,SEQUENCE(ROWS(u)),LAMBDA(a,v,LET(c,b=INDEX(u,v),IF(SUM(--c)>1,VSTACK(a,TOROW(FILTER(w,c))),a)))),1),""))Excel solution 3 for Group Words by Anagrams, proposed by محمد حلمي:
=DROP(
REDUCE(
0,
A2:A20,
LAMBDA(
a,
d,
LET(
e,
A2:A20,
n,
ROW(
1:9
),
x,
FILTER(
e,
MAP(
e,
LAMBDA(
v,
AND(
SORT(
MID(
d,
n,
1
)
)=SORT(
MID(
v,
n,
1
)
)
)
)
)
),
IF(
ROWS(
x
)=1,
a,
UNIQUE(
IFNA(
VSTACK(
a,
TOROW(
x
)
),
""
)
)
)
)
)
),
1
)Excel solution 4 for Group Words by Anagrams, proposed by Kris Jaganah:
=LET(p,A2:A20,q,UNIQUE(MAP(p,LAMBDA(y,LET(a,MID(y,SEQUENCE(,LEN(y)),1),b,p,c,BYROW(UNIQUE(FIND(a,b),1),LAMBDA(x,CONCAT(x))),d,TEXTJOIN("-",1,IF(IFERROR(LEN(c)=LEN(b),0),b,"")),d)))),r,TOCOL(IFS(FIND("-",q)>0,q),3),TEXTSPLIT(TEXTJOIN("*",1,r),"-","*",,,""))Excel solution 5 for Group Words by Anagrams, proposed by Timothée BLIOT:
=LET(
A,
A2:A20,
B,
MAP(
A,
LAMBDA(
x,
CONCAT(
SORT(
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)
)
)
)
),
C,
IFERROR(
REDUCE(
0,
UNIQUE(
B
),
LAMBDA(
z,
v,
VSTACK(
z,
TOROW(
LET(
x,
FILTER(
A,
B=v
),
IF(
ROWS(
x
)<2,
0,
x
)
)
)
)
)
),
""
),
FILTER(
C,
NOT(
BYROW(
C,
LAMBDA(
x,
COUNT(
x
)
)
)
)
)
)Excel solution 6 for Group Words by Anagrams, proposed by Hussein SATOUR:
=LET(
a,
A2:A20,
b,
BYROW(
A2:A20,
LAMBDA(
x,
CONCAT(
SORT(
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)
)
)
)
),
c,
MAP(
UNIQUE(
b
),
LAMBDA(
y,
TEXTJOIN(
"/",
,
FILTER(
a,
b = y
)
)&"|"
)
),
TEXTSPLIT(
CONCAT(
FILTER(
c,
NOT(
ISERR(
FIND(
"/",
c
)
)
)
)
),
"/",
"|",
1,
,
""
)
)Excel solution 7 for Group Words by Anagrams, proposed by Oscar Mendez Roca Farell:
=DROP(REDUCE("",
A2:A20,
LAMBDA(i,
x,
LET(_d,
A2:A20,
_l,
SEQUENCE(
,
LEN(
x
)
),
_w,
MID(
x,
_l,
1
),
_e,
TOROW(FILTER(_d,
(BYROW(
FIND(
_w,
_d
),
LAMBDA(
r,
IFERROR(
CONCAT(
SORT(
TOCOL(
r,
2
)
)
),
""
)=CONCAT(
_l
)
)
)*(LEN(
_d
)=LEN(
x
))),
"")),
UNIQUE(
IFNA(
VSTACK(
i,
IF(
COLUMNS(
_e
)>1,
_e,
""
)
),
""
)
)))),
1)Excel solution 8 for Group Words by Anagrams, proposed by LEONARD OCHEA 🇷🇴:
=LET(
w,
A2:A20,
m,
BYROW(
MID(
w,
SEQUENCE(
,
10
),
1
),
LAMBDA(
a,
CONCAT(
SORT(
a,
,
,
1
)
)
)
),
u,
UNIQUE(
VSTACK(
UNIQUE(
m
),
UNIQUE(
m,
,
1
)
),
,
1
),
i,
INDEX(
w,
IF(
u=TOROW(
m
),
SEQUENCE(
,
ROWS(
w
)
),
""
)
),
TEXTSPLIT(
TEXTJOIN(
"/",
,
BYROW(
i,
LAMBDA(
a,
ARRAYTOTEXT(
TOROW(
a,
3
)
)
)
)
),
",",
"/",
,
,
""
)
)Excel solution 9 for Group Words by Anagrams, proposed by Charles Roldan:
=LET(
SORT, LAMBDA(x, SORT(x)),
A, LAMBDA(f, LAMBDA(x,
CONCAT(f(MID(x, SEQUENCE(LEN(x)), 1))))),
V, LAMBDA(f, LAMBDA(b, LAMBDA(a, IF(f(b), VSTACK(a, b), a)))),
R, LAMBDA(f, LAMBDA(x,
IFNA(DROP(REDUCE(FALSE, x, LAMBDA(a, b, f(b)(a))), 1), ""))),
Words, A2:A20,
Reps, MAP(Words, A(SORT)),
_MoreThanOne, LAMBDA(x, COUNTA(x) > 1),
_Class, LAMBDA(x,
V(_MoreThanOne)(TOROW(FILTER(Word&s, Reps = x)))),
R(_Class)(UNIQUE(Reps))
)Excel solution 10 for Group Words by Anagrams, proposed by Pieter de Bruijn:
=LET(x,
A2:A20,
m,
MAP(
x,
LAMBDA(
y,
CONCAT(
SORT(
MID(
y,
SEQUENCE(
LEN(
y
)
),
1
)
)
)
)
),
TOCOL(MAP(UNIQUE(
m
),
LAMBDA(y,
LET(z,
MMULT(
N(
y=m
),
SEQUENCE(
ROWS(
y
)
)
),
ARRAYTOTEXT(FILTER(x,
z/(SUM(
z
)>1)))))),
2))Excel solution 11 for Group Words by Anagrams, proposed by Ziad A.:
=LET(
a,
A2:A20,
S,
LAMBDA(
w,
MAP(
w,
LAMBDA(
w,
JOIN(
,
SORT(
MID(
w,
SEQUENCE(
LEN(
w
)
),
1
)
)
)
)
)
),
QUERY(
UNIQUE(
MAP(
a,
LAMBDA(
w,
TOROW(
FILTER(
a,
S(
w
)=S(
a
)
)
)
)
)
),
"where Col2<>''"
)
)Excel solution 12 for Group Words by Anagrams, proposed by Giorgi Goderdzishvili:
=LAMBDA(string1,
string2,
MAP(string1,
LAMBDA(q,
LET( input1,
LOWER(
SUBSTITUTE(
q,
" ",
""
)
),
input2,
LOWER(
SUBSTITUTE(
string2,
" ",
""
)
),
sequen1,
MID(
input1,
SEQUENCE(
,
LEN(
input1
)
),
1
),
sequen2,
MID(
input2,
SEQUENCE(
,
LEN(
input2
)
),
1
),
sorting1,
CONCAT(
SORT(
TRANSPOSE(
sequen1
),
1,
1
)
),
sorting2,
CONCAT(
SORT(
TRANSPOSE(
sequen2
),
1,
1
)
),
seqsort1,
MID(
sorting1,
SEQUENCE(
,
LEN(
sorting1
)
),
1
),
seqsort2,
MID(
sorting2,
SEQUENCE(
,
LEN(
sorting2
)
),
1
),
checking,
SUM(--(seqsort1=seqsort2)),
AND(
LEN(
input1
)=checking,
LEN(
input2
)=checking
) ))))
Solution:
=LET(
list,
A2:A20,
mp,
MAP(
list,
LAMBDA(
x,
TEXTJOIN(
" ",
,
IF(
IFERROR(
IsAnagram(
list,
x
),
FALSE
),
list,
""
)
)
)
),
wrds,
FILTER(
UNIQUE(
mp
),
ISNUMBER(
FIND(
" ",
UNIQUE(
mp
)
)
)
),
TEXTSPLIT(
TEXTJOIN(
",",
,
wrds
),
" ",
",",
,
,
""
)
)Excel solution 13 for Group Words by Anagrams, proposed by Daniel Garzia:
=LET(
d,
A2:A20,
s,
MAP(
d,
LAMBDA(
c,
CONCAT(
SORT(
MID(
c,
ROW(
1:9
),
1
)
)
)
)
),
r,
REDUCE(
0,
UNIQUE(
s
),
LAMBDA(
a,
b,
VSTACK(
a,
TOROW(
FILTER(
d,
s=b
)
)
)
)
),
IFNA(
FILTER(
r,
ISNA(
INDEX(
r,
,
2
)
)-1
),
""
)
)Excel solution 14 for Group Words by Anagrams, proposed by Andres Rojas Moncada:
=LET(_pal,
A2:A20,
_peso,
MAP(
_pal,
LAMBDA(
_p,
SUMA(
CODIGO(
EXTRAE(
_p,
SECUENCIA(
1,
LARGO(
_p
)
),
1
)
)
)
)
),
_val,
UNICOS(FILTRAR(_peso,
BYROW((_peso=ENFILA(
UNICOS(
_peso,
0,
1
)
))*1,
LAMBDA(
_fil,
SUMA(
_fil
)
))=0)),
DIVIDIRTEXTO(
REDUCE(
"",
_val,
LAMBDA(
_a,
_v,
_a&UNIRCADENAS(
"-",
1,
FILTRAR(
_pal,
_peso=_v
)
)&"*"
)
),
"-",
"*",
1,
1,
""
))Solving the challenge of Group Words by Anagrams with Python
Python solution 1 for Group Words by Anagrams, proposed by Mungunbayar Bat-Ochir:
"iceman", "heart", "scar", "bull", "ball", "eat", "hater", "arcs", "rathe", "hatred"]
dict = {}
dict[sorted_word].append(word)
else:
dict[sorted_word] = [word]
filtered_dict = {key: val for key, val in dict.items() if len(val) > 1}
for _ in filtered_dict.values():
print(_)
Solving the challenge of Group Words by Anagrams with Python in Excel
Python in Excel solution 1 for Group Words by Anagrams, proposed by Bo Rydobon 🇹🇭:
Python again
ws =xl("A2:A20")[0]
sw =[''.join(sorted(w)) for w in ws]
# pd.DataFrame for fillna
pd.DataFrame(p for u in pd.unique(sw) if len((p:=[w for w,s in zip(ws,sw) if s==u]))>1).fillna('')
Python in Excel solution 2 for Group Words by Anagrams, proposed by Bo Rydobon 🇹🇭:
from itertools import permutations
ws =xl("A2:A20")[0].tolist()
# create empty list
rs =[]
for w in ws:
ps = sorted(pd.unique([''.join(p) for p in permutations(w) if ''.join(p) in ws]),key=lambda x: ws.index(x))
if len(ps)>1: rs.append(ps)
ws = [w for w in ws if w not in ps]
# fillna with DataFrame
pd.DataFrame(rs).fillna('')
Python in Excel solution 3 for Group Words by Anagrams, proposed by John V.:
Hi everyone!
w = xl("A2:A20")[0]
s = [''.join(sorted(i)) for i in w]
r = []
for a in pd.unique(s):
l = [x for x, y in zip(w, s) if y==a]
if len(l) > 1: r.append(l)
pd.DataFrame(r).fillna('')
Blessings!
Solving the challenge of Group Words by Anagrams with R
R solution 1 for Group Words by Anagrams, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("Anagram Words.xlsx") %>% select(1)
result1 = input %>%
mutate(letters = map(.$Words, ~ list(sort(unlist(str_split(string = .x, pattern ="")))) ))
result2 = result1 %>%
group_by(letters) %>%
count() %>%
ungroup()
result = result1 %>%
left_join(result2, by = c("letters")) %>%
filter(n != 1) %>%
group_by(letters) %>%
summarise(anagrams = paste(Words, collapse = ", ")) %>%
ungroup() %>%
select(anagrams)
&&