Unique Letters in Words

List those strings whose words contain only unique letters. “Everything is great” – Everything has e repeated 2 times. Hence, this will not be listed. “A rose by any other name” – All words in this sentence contain unique letters only. Hence, this will be listed.

📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 137
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Unique Letters Per Word with Power Query

Power Query solution 1 for Unique Letters Per Word, proposed by Bo Rydobon 🇹🇭:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Fil = Table.SelectRows(
    Source, 
    each Text.Lower([String])
      = Text.Combine(
        List.Transform(
          Text.Split(Text.Lower([String]), " "), 
          each Text.Combine(List.Distinct(Text.ToList(_)))
        ), 
        " "
      )
  )
in
  Fil

Power Query solution 2 for Unique Letters Per Word, proposed by Zoran Milokanović:

letters w/ hashtag#powerquery. hashtag#bitanbit

let
 Source = Excel.CurrentWorkbook(){[Name="Strings"]}[Content],
 AddedWord = Table.AddColumn(Source, "Word", each Text.Split([String], " ")),
 ExpandedWord = Table.ExpandListColumn(AddedWord, "Word"),
 WordUniquenessCheck = Table.AddColumn(ExpandedWord, "LetterCheck", each let letters = Text.ToList(Text.Upper([Word])) in List.Count(letters) = List.Count(List.Distinct(letters))),
 StringUniquenessCheck = Table.Group(WordUniquenessCheck, {"String"}, {{"StringCheck", each List.MatchesAll(_[LetterCheck], each _ = true)}}),
 FilteredUniqueStrings = Table.SelectRows(StringUniquenessCheck, each ([StringCheck] = true))[[String]],
 ExpectedAnswer = Table.RenameColumns(FilteredUniqueStrings,{{"String", "Expected Answer"}})
in
 ExpectedAnswer

Power Query solution 3 for Unique Letters Per Word, proposed by Aditya Kumar Darak 🇮🇳:

let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Calc = Table.AddColumn(
    Source, 
    "Calc", 
    each [
      Splt = Text.Split(Text.Lower([String]), " "), 
      C    = List.Transform(Splt, (f) => List.IsDistinct(Text.ToList(f))), 
      R    = List.AllTrue(C)
    ][R]
  ), 
  Return = Table.SelectRows(Calc, each [Calc])[[String]]
in
  Return

Power Query solution 4 for Unique Letters Per Word, proposed by Luan Rodrigues:

let
  Fonte = Tabela1, 
  ind = Table.AddIndexColumn(Fonte, "Índice", 0, 1, Int64.Type), 
  tab = Table.AddColumn(ind, "Personalizar", each Text.Split([String], " ")), 
  exp = Table.ExpandListColumn(tab, "Personalizar"), 
  dist = Table.AddColumn(exp, "Personalizar.1", each List.IsDistinct(Text.ToList([Personalizar]))), 
  gp = Table.Group(
    dist, 
    {"Índice"}, 
    {
      {
        "Contagem", 
        each [
          a = _, 
          b = Table.RowCount(_), 
          c = Table.RowCount(Table.SelectRows(a, each [Personalizar.1] = true)), 
          d = if b = c then a else null, 
          e = try Text.Combine(List.Transform(d[Personalizar], Text.From), " ") otherwise null
        ][e]
      }
    }
  )[[Contagem]], 
  res = Table.SelectRows(gp, each ([Contagem] <> null))
in
  res

Power Query solution 5 for Unique Letters Per Word, proposed by Brian Julius:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Dupe = Table.DuplicateColumn(Source, "String", "String2"), 
  SplitBySpace = Table.DuplicateColumn(
    Table.ExpandListColumn(
      Table.TransformColumns(
        Dupe, 
        {
          {
            "String2", 
            Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), 
            let
              itemType = (type nullable text) meta [Serialized.Text = true]
            in
              type {itemType}
          }
        }
      ), 
      "String2"
    ), 
    "String2", 
    "String3"
  ), 
  AddTextToList = Table.AddColumn(SplitBySpace, "String3.1", each Text.ToList([String3])), 
  AddIsDistinct = Table.AddColumn(
    AddTextToList, 
    "AllUnique", 
    each if List.IsDistinct([String3.1]) then 0 else 1
  ), 
  Remove = Table.RemoveColumns(AddIsDistinct, {"String2", "String3", "String3.1"}), 
  Group = Table.RemoveColumns(
    Table.SelectRows(
      Table.Group(Remove, {"String"}, {{"DistinctSum", each List.Sum([AllUnique]), type number}}), 
      each [DistinctSum] = 0
    ), 
    "DistinctSum"
  )
in
  Group

Power Query solution 6 for Unique Letters Per Word, proposed by Rafael González B.:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  TypeToText = Table.TransformColumnTypes(Source, {{"String", type text}}), 
  RemoveDuplicateLetters = Table.AddColumn(
    TypeToText, 
    "TextDesc", 
    each Table.TransformColumns(
      Table.FromList(
        Text.Split([String], " "), 
        Splitter.SplitByNothing(), 
        null, 
        null, 
        ExtraValues.Error
      ), 
      {"Column1", each Text.Combine(List.Distinct(Text.ToList(_)))}
    )[Column1]
  ), 
  ExtractTextNew = Table.TransformColumns(
    RemoveDuplicateLetters, 
    {"TextDesc", each Text.Combine(List.Transform(_, Text.From), " "), type text}
  ), 
  CompareStrings = Table.AddColumn(ExtractTextNew, "Personalizado", each [String] = [TextDesc]), 
  FilterTrues = Table.SelectRows(CompareStrings, each ([Personalizado] = true)), 
  FinalResult = Table.SelectColumns(FilterTrues, {"String"})
in
  FinalResult

Power Query solution 7 for Unique Letters Per Word, proposed by Jan Willem Van Holst:

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "JY6xbsMwDER/5eC5Q5AlczO0CJCiS5fA9aBGVE0kIQOJluy/jywPBMHj3eH1ffcthKsTiBqS0RNW+Erd8NZ3PyNhEprdg4U87hwInJqzaLSxKpnlv3lPAZ/q4dm3P82crD2+nI1pjZXRGRad4FV+p91ufzDcREtzvQtoIQSN627SMVJmW9aoVZCk0x0aUHjrvTiPZZt2f1SiCovgssYt8hdd3rrOlBrDQ2MVhhc=", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [String = _t]
  ), 
  #"Added Custom" = Table.AddColumn(
    Source, 
    "Custom", 
    each 
      let
        split       = Text.Split([String], " "), 
        toList      = List.Transform(split, each Text.ToList(_)), 
        unique      = List.Transform(toList, each List.Distinct(_)), 
        zipped      = List.Zip({toList, unique}), 
        compare     = List.Transform(zipped, each _{0} = _{1}), 
        booleanList = not List.Contains(compare, false)
      in
        booleanList
  ), 
  #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = true)), 
  #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows", {"Custom"})
in
  #"Removed Columns"

Power Query solution 8 for Unique Letters Per Word, proposed by Sue Bayes:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Duplicate = Table.DuplicateColumn(Source, "String", "StringCopy"), 
  Split = Table.ExpandListColumn(
    Table.TransformColumns(
      Duplicate, 
      {
        {
          "StringCopy", 
          Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), 
          let
            itemType = (type nullable text) meta [Serialized.Text = true]
          in
            type {itemType}
        }
      }
    ), 
    "StringCopy"
  ), 
  Trim = Table.TransformColumns(Split, {{"StringCopy", Text.Trim, type text}}), 
  IsDistinct = Table.AddColumn(Trim, "Custom", each List.IsDistinct(Text.ToList([StringCopy]))), 
  Regroup = Table.Group(IsDistinct, {"String"}, {"All", each [Custom]}), 
  AllTrue = Table.AddColumn(Regroup, "Filter", each List.AllTrue([All])), 
  Filter = Table.FromList(Table.SelectRows(AllTrue, each ([Filter] = true))[String])
in
  Filter

Solving the challenge of Unique Letters Per Word with Excel

Excel solution 1 for Unique Letters Per Word, proposed by Bo Rydobon 🇹🇭:

=FILTER(A2:A10,MAP(A2:A10,LAMBDA(a,a=TEXTJOIN(" ",,MAP(TEXTSPLIT(a," "),LAMBDA(b,CONCAT(UNIQUE(MID(b,SEQUENCE(20),1)))))))))

Excel solution 2 for Unique Letters Per Word, proposed by Rick Rothstein:

=LET(r,A2:A10,t,MAP(r,LAMBDA(a,SUM(MAP(TEXTSPLIT(a," "),LAMBDA(x,COUNTA(UNIQUE(MID(x,SEQUENCE(LEN(x)),1))))))=LEN(SUBSTITUTE(a," ","")))),FILTER(r,t))

Excel solution 3 for Unique Letters Per Word, proposed by John V.:

=FILTER(A2:A10,MAP(A2:A10,LAMBDA(s,LET(w,TEXTSPLIT(s," "),AND(w=MAP(w,LAMBDA(x,CONCAT(UNIQUE(MID(x,ROW(1:30),1))))))))))

Excel solution 4 for Unique Letters Per Word, proposed by John V.:

=FILTER(A2:A10,MAP(A2:A10,LAMBDA(s,AND(MAP(TEXTSPLIT(s," "),LAMBDA(x,LET(c,MID(x,SEQUENCE(LEN(x)),1),AND(ROWS(c)=ROWS(UNIQUE(c))))))))))

Excel solution 5 for Unique Letters Per Word, proposed by John V.:

=FILTER(A2:A10,MAP(A2:A10,LAMBDA(s,AND(MAP(TEXTSPLIT(s," "),LAMBDA(x,LET(c,MID(x,SEQUENCE(LEN(x)),1),m,XMATCH(c,c),SUM(--(FREQUENCY(m,m)=0))=1)))))))

Excel solution 6 for Unique Letters Per Word, proposed by محمد حلمي:

=FILTER(A2:A10,
MAP(A2:A10,LAMBDA(c,AND(
MAP(TEXTSPLIT(c," "),LAMBDA(a,
COUNT(SEARCH(VSTACK(CHAR(ROW(65:90)),"'"),a))=
LEN(a)))))))

Excel solution 7 for Unique Letters Per Word, proposed by محمد حلمي:

=FILTER(A2:A10,MAP(A2:A10,LAMBDA(c,
AND(MAP(TEXTSPLIT(c,," "),LAMBDA(a,LET(
r,MID(a,SEQUENCE(LEN(a)),1),
ROWS(r)=ROWS(UNIQUE(r)))))))))

Excel solution 8 for Unique Letters Per Word, proposed by Kris Jaganah:

=FILTER(A2:A10,MAP(A2:A10,LAMBDA(p,MIN(MAP(TEXTSPLIT(p,," "),LAMBDA(x,LET(a,MID(x,SEQUENCE(,LEN(x)),1),IF(LEN(x)=SUM(IFNA(--(UNIQUE(a,1)=a),0)),1,0)))))))=1)

Excel solution 9 for Unique Letters Per Word, proposed by Julian Poeltl:

=FILTER(A2:A10,MAP(A2:A10,LAMBDA(S,LET(SP,TEXTSPLIT(S," "),SUM(--MAP(SP,LAMBDA(A,ROWS(UNIQUE(MID(A,SEQUENCE(LEN(A)),1)))<>LEN(A))))=0))))

Excel solution 10 for Unique Letters Per Word, proposed by Aditya Kumar Darak 🇮🇳:

=LET(
 _d, A2:A10,
 _e, LAMBDA(a,
 AND(
 MAP(
 TEXTSPLIT(a, " "),
 LAMBDA(x, x = CONCAT(UNIQUE(MID(x, SEQUENCE(LEN(x)), 1))))
 )
 )
 ),
 _c, MAP(_d, _e),
 _r, FILTER(_d, _c),
 _r
)

Excel solution 11 for Unique Letters Per Word, proposed by Timothée BLIOT:

=LET(A,A2:A10, B,TEXTSPLIT(TEXTJOIN("/",,A)," ","/"),
C, MAP(B, LAMBDA(x, LET(L, MID(x,SEQUENCE(LEN(x)),1), IFERROR(--(CONCAT(L)=CONCAT(UNIQUE(L))),1) ) )),
FILTER(A,BYROW(C, LAMBDA(x, SUM(x)=COLUMNS(C)))))

Excel solution 12 for Unique Letters Per Word, proposed by Hussein SATOUR:

=LET(a,A2:A10,FILTER(a,MAP(a,LAMBDA(x,PRODUCT(MAP(TEXTSPLIT(x," "),LAMBDA(y,CONCAT(UNIQUE(MID(y,SEQUENCE(LEN(y)),1))) =y))*1)))=1))

Excel solution 13 for Unique Letters Per Word, proposed by Sunny Baggu:

=FILTER(A2:A10,BYROW(A2:A10,LAMBDA(a,AND(DROP(REDUCE("",TEXTSPLIT(a,," "),LAMBDA(a,v,VSTACK(a,LET(_a,MID(v,SEQUENCE(LEN(v)),1),_b,SEARCH(_a,CONCAT(CHAR(SEQUENCE(26)+96))),COUNTA(_b)=COUNTA(UNIQUE(_b)))))),1)))))

Excel solution 14 for Unique Letters Per Word, proposed by Sunny Baggu:

=FILTER(A2:A10,
MAP(A2:A10,LAMBDA(s,
LET(_a,TEXTSPLIT(LOWER(s),," "), _e1,LAMBDA(x,MID(x,SEQUENCE(LEN(x)),1)),
AND(MAP(_a,LAMBDA(c,ROWS(_e1(c))=ROWS(UNIQUE(_e1(c))))))
))))

Excel solution 15 for Unique Letters Per Word, proposed by Bhavya Gupta:

=FILTER(A2:A10,MAP(A2:A10,LAMBDA(s,TEXTJOIN(" ",,BYROW(DROP(TEXTSPLIT("|"&CONCAT(MID(s,SEQUENCE(LEN(s)),1)&"|"),"|"," ",,,""),,1),LAMBDA(x,CONCAT(UNIQUE(x,1)))))=s)))

Excel solution 16 for Unique Letters Per Word, proposed by Md. Zohurul Islam:

=LET(w,A2:A10,u,MAP(w,LAMBDA(x,LET(a,TEXTSPLIT(x,," "),b,MAP(a,LAMBDA(y,LET(a,CONCAT(UNIQUE(MID(y,SEQUENCE(LEN(y)),1))),b,IF(a=y,1,0),b))),d,IF(COUNTA(a)=SUM(b),1,0),d))),v,FILTER(w,u>0),v)

Excel solution 17 for Unique Letters Per Word, proposed by Jaroslaw Kujawa:

=XMATCH(FALSE,MAP(TEXTSPLIT(A2,," "),LAMBDA(a,LET(b,MID(a,SEQUENCE(,LEN(a)),1),LEN(a)=SUM(LEN(a)-LEN(SUBSTITUTE(a,b,"")))))))copy down then FILTER(A2:A10,ISNA(C2:C10))

Excel solution 18 for Unique Letters Per Word, proposed by Stefan Olsson:

=LET(
s, A2:A10, 
f, BYROW(s, 
 LAMBDA(_s, 
 AND(
 MAP(SPLIT(LOWER(_s), " "), 
 LAMBDA(w, 
 w=JOIN("", UNIQUE(REGEXEXTRACT(w, REPT("(.)", LEN(w))),1)) 
 )))
 )),
FILTER(s, f)
)

Excel solution 19 for Unique Letters Per Word, proposed by Guillermo Arroyo:

=LET(s,A2:A10,FILTER(s,MAP(s,LAMBDA(a,REDUCE(1,TEXTSPLIT(a," "),LAMBDA(b,w,LET(l,LEN(w),AND(COUNTA(UNIQUE(MID(w,SEQUENCE(l),1)))=l,b)))))),""))

&&&

Solving the challenge of Unique Letters Per Word with Power Query

Solving the challenge of Unique Letters Per Word with Excel

Leave a Reply Cancel reply