# Excel BI Challenge Workout 187

— CAN YOU SOLVE THIS - EXCEL CHALLENGE 187 —
(Solutions in any language are also welcome for Excel Challenges)

An Anagram is a word which is made by rearranging the letters of another word.
Ex. brag = grab
Find the pair of words where words are Anagrams of each other.

(Your formula need not be a single formula. You can write multiple formulas to arrive at a solution. Also your formula need not be different from others as long as you have worked out your formula independently)

#excel, #excelchallenge, #powerquerychallenge, #daxchallenge, #m, #dax, #powerbi, #powerquery, #powerpivot, #sheets, #googlesheets, #data, #analytics, #businessintelligence, #finance, #office365, #python, #r

## LinkedIn Post by:Amr Tawfik, CMA P1

Thanks Excel Bi, Here is what come to my mind

`=FILTER(A2:A9,MAP(A2:A9,LAMBDA(x,IFNA(IF(COUNTA(UNIQUE(SORT(TOCOL(MID(x,SEQUENCE(,LEN(x),1,1),1)),1,,FALSE)))-1=LEN(TEXTAFTER(x," ")),TRUE,FALSE),"")))=TRUE)`

``````let
Source = Excel.CurrentWorkbook(){[Name = "tblData"]}[Content],
Result = Table.SelectRows( Source,
each
let
Split = Text.Split([Words], " "),
Count = List.Count(Text.PositionOfAny(Split{1}, Text.ToList(Split{0}), Occurrence.All))
in
try Count = Text.Length(Split{1}) - 1 otherwise false
)
in
Result
``````

## LinkedIn post by: Alejandro Simón

``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
let
a = Text.Split(Text.Lower([Words]), " "),
b = List.Transform(a, each Text.ToList(_)),
c = List.Transform({0,1}, each List.Sort(b{_}))
in try c{0} = c{1} otherwise null), each [Custom] = true)[[Words]]
in
Sol
``````

``````=LET(w,A2:A9,
_col1,TEXTBEFORE(w," ",,,,""),
_col2,TEXTAFTER(w," ",,,,""),
_s,LAMBDA(x,CONCAT(SORT(MID(x,SEQUENCE(LEN(x)),1)))),
r,IFERROR(MAP(_col1,_col2,LAMBDA(x,y,_s(x)=_s(y))),0),
FILTER(w,r))

Where
_________________________________________________________________
w - words
_col1 - First words before spaces
_col2 - Second words before spaces
_s - alphabetical re-ordering of the words in a LAMBDA function
r - test of anagram by checking if the reordered words in _col1 is the same as that in _col2.

________________________________________________________________
``````

An anagram w/ #powerquery. #bitanbit #powerbi

``````let
Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
Solution = [List.Select](http://list.select/)(Source[Words], (w) =>
let
c = List.Transform(Text.Split(w, " "), each List.Sort(Text.ToList(Text.Lower(_))))
in
c{0} = c{1}? ?? {}
)
in
Solution
``````

## LinkedIn Post by:Hussain Ali Nasser

`=FILTER(A2:A9,MAP(A2:A9,LAMBDA(_range,LET(_rangelen,LEN(_range),_splitnum,(_rangelen-1)/2,_splitrange,MID(_range,SEQUENCE(_rangelen),1),_leftrange,SORT(TAKE(_splitrange,_splitnum)),_rightrange,SORT(TAKE(_splitrange,-_splitnum)),_checklist,IFERROR(AND(_leftrange=_rightrange),0),_checklist))))`

``````=LET(values,A2:A9,
cleanDatas,LOWER(HSTACK(TEXTBEFORE(values," "),TEXTAFTER(values," "))),
lengths,LEN(cleanDatas),
reordered,MAKEARRAY(ROWS(values),2,LAMBDA(r,c,TEXTJOIN("",TRUE,SORT(MID(INDEX(cleanDatas,r,c),SEQUENCE(INDEX(lengths,r,c)),1))))),
flag,IFERROR(MAKEARRAY(ROWS(values),1,LAMBDA(r,c,--(INDEX(reordered,r,c)=INDEX(reordered,r,c+1)))),0),
FILTER(values,flag))
``````

Probably extremely inefficient, but what the heck. It’s the best I can do ``````=LET(Q, A2:A9, W, MAP(Q, LAMBDA(X,
LET(A, X, B, MID(LOWER(A), SEQUENCE(,LEN(A)),1),
C, WRAPROWS(B, FIND(" ",A),""),
D, TRIM(CONCAT(SORT(INDEX(C,1,0),,,1))),
E, TRIM(CONCAT(SORT(INDEX(C,2,0),,,1))),
IFERROR(D=E, FALSE)))), FILTER(Q, W))
``````

An alternative approach to SEQUENCE/WRAPROWS but probably way more volatile:

``````=LET(X, A2:A9,
Y, MAP(X, LAMBDA(X,
LET(A, X, B, TEXTSPLIT(A," "),
C, LOWER(MID(B, ROW(INDIRECT("1:"&MAX(LEN(B)))), 1)),
IFERROR(CONCAT(SORT(INDEX(C,0,1)))=CONCAT(SORT(INDEX(C,0,2))),)))),
FILTER(X,Y))
``````

I wanted to thank you for sharing the challenge. I found it to be a great opportunity for growth and learning. To address it, I applied the following solution:

``````let
Source = Excel.CurrentWorkbook(){[Name="Table1_3"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Words", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each
let
A = try List.Sort(try Text.ToList(Text.Trim([Words]{0}), ""), ""),
B = try List.Sort(try Text.ToList(Text.Trim([Words]{1}), ""), "")
in
A = B
)
in
#"Filtered Rows"
``````

`= [X=Table.SelectRows(Source, each Text.Contains(_[Words]," ")),A=Table.SplitColumn(X,"Words",Splitter.SplitTextByAnyDelimiter({" "}),2),B=(X)=>List.Sort(List.Transform({1..Text.Length(X)}, each Text.Range(Text.Lower(X),Text.Length(X)-_,1))),C=Table.SelectRows(A, each B(_[Words.1])=B(_[Words.2])),D=Table.ToList(C)][D]`

``````=FILTER(
A2:A9,
MAP(
A2:A9,
LAMBDA(a,
LET(
_tb, TEXTBEFORE(a, " "),
_ta, TEXTAFTER(a, " "),
_e1, LAMBDA(x, SORT(MID(x, SEQUENCE(LEN(x)), 1))),
_cri, AND(_e1(_ta) = _e1(_tb)),
IFERROR(N(_cri) = 1, 0)
)
)
)
)
``````

``````=FILTER(A2:A9,BYROW(A2:A9,LAMBDA(r,
LET(f,TEXTBEFORE(r," "),s,TEXTAFTER(r," "),fs,MID(f,SEQUENCE(LEN(f)),1),ss,MID(s,SEQUENCE(LEN(s)),1),SUM(--ISNA(XMATCH(fs,ss)))=0))))
``````

My hashtag#powerquery solution:

``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Dupe = Table.DuplicateColumn(Source, "Words", "Word"),
SplitBySpace = Table.SelectRows( Table.SplitColumn(Dupe, "Word", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Word1", "Word2"}), each [Word2] <> null),
Compare = Table.AddColumn(SplitBySpace, "Compare", each [
a = Text.Combine( List.Sort(Text.ToList(Text.Lower([Word1])), Order.Ascending), ""),
b = Text.Combine(List.Sort(Text.ToList(Text.Lower([Word2])), Order.Ascending), ""),
c = if a = b then 1 else 0
][c]),
FilterNClean = Table.SelectColumns( Table.SelectRows(Compare, each ([Compare] = 1)), "Words")
in
FilterNClean
``````

``````=FILTER(
A2:A9,
MAP(
A2:A9,
LAMBDA(a,
LET(
_ts, MID(a, SEQUENCE(LEN(a)), 1),
_num, (LEN(a) - 1) / 2,
_left, SORT(TAKE(_ts, _num)),
_right, SORT(TAKE(_ts, -_num)),
IFERROR(AND(_left = _right), 0)
)
)
)
)
``````

M-Code

``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Ans = Table.SelectRows(Source,each let t = Text.Split(Text.Lower([Words]&" ")," ") in Text.Combine(List.Sort(Text.ToList(t{0})))=Text.Combine(List.Sort(Text.ToList(t{1}))))
in
Ans
``````

`=FILTER(A2:A9,IFNA(MAP(A2:A9,LAMBDA(x,LET(s,SEQUENCE(99),a,TEXTAFTER(x," "),b,TEXTBEFORE(x," "),AND(SORT(MID(b,s,1))=SORT(MID(a,s,1)))))),))`

## LinkedIn Post by:John Jairo V.

Hi to all!

One option could be: ` =FILTER(A2:A9,MAP(A2:A9&" ",LAMBDA(x,LET(f,LAMBDA(a,SORT(MID(INDEX(TEXTSPLIT(x," "),a),ROW(1:15),1))),AND(f(1)=f(2))))))`

Blessings!

``````=FILTER(A2:A9,MAP(A2:A9,LAMBDA(a,LET(m,MID(TEXTSPLIT(a&" "," "),SEQUENCE(20),1),AND(SORT(TAKE(m,,1))=SORT(INDEX(m,,2)))))))
``````

``````let