Excel BI Challenge Workout 187

Workouts Excel BI Challenge
#1

— 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.

Post answers in Comment.

(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)

Download Practice File - https://lnkd.in/dNU2Qvsv

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

image
image800×457 89.1 KB

Excel BI’s LinkedIn Post

#2

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)

#3

LinkedIn Post by:
Taeyong Shin
 

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
#4

LinkedIn post by: Alejandro Simón

adjunto mi query…

let
  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
  Sol = Table.SelectRows(Table.AddColumn(Source, "Custom", each 
    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
#5

LinkedIn post by:
Quadri Olayinka Atharu
 

=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.

________________________________________________________________
#6

LinkedIn Post by:
Zoran Milokanović

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
#7

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))))

#8

LinkedIn Post by:
Julien Lacaze
 

=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))
#9

LinkedIn Post by:
Stevenson Yu

Probably extremely inefficient, but what the heck. It’s the best I can do :sweat_smile:

=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))
#10

LinkedIn Post by:
Fatemeh Heydari

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"
#11

LinkedIn Post by:
Omid Motamedisedeh

= [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]

#12

LinkedIn Post by:
Sunny Baggu
 

=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)
 )
 )
 )
)
#13

LinkedIn Post by:
Amardeep Singh
 

=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))))
#14

LinkedIn Post by:
Brian Julius

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
#15

LinkedIn post by:
Sunny Baggu
 

=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)
 )
 )
 )
)
#16

LinkedInPost by:
Bo Rydobon

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
#17

LinkedIn Post by:
Rick Rothstein

=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)))))),))

#18

LinkedIn Post by:
John Jairo V.

Hi to all!

One option could be:
:white_check_mark:

=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!

#19

LinkedIn post by
Bo Rydobon
 

=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)))))))
#20

LinkedIn Post by:
Luan Rodrigues
 

let
 Fonte = Tabela1,
 res = Table.SelectRows(Fonte, each 
List.IsDistinct(List.Transform(Text.Split([Words]," "), (x)=> Text.Combine(List.Sort(List.Reverse( Text.ToList( Text.Lower(Text.From(x)))))))) = false
)
in
 res