Find Missing Alphabets

Find the missing English Alphabets from First Ladies column.

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

Solving the challenge of Find Missing Alphabets with Power Query

Power Query solution 1 for Find Missing Alphabets, proposed by Bo Rydobon 🇹🇭:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Ans = List.Select(
    {"a" .. "z"}, 
    each not List.Contains(Text.ToList(Text.Lower(Text.Combine(Source[First Ladies]))), _)
  )
in
  Ans

Power Query solution 2 for Find Missing Alphabets, proposed by Zoran Milokanović:

let
  S = List.Accumulate(
    Excel.CurrentWorkbook(){[Name = "Input"]}[Content][First Ladies], 
    {"a" .. "z"}, 
    (s, d) => List.RemoveItems(s, Text.ToList(Text.Lower(d)))
  )
in
  S

Power Query solution 3 for Find Missing Alphabets, proposed by Zoran Milokanović:

let
  Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content], 
  Solution = List.RemoveMatchingItems(
    {"a" .. "z"}, 
    List.Combine(List.Transform(Source[First Ladies], each Text.ToList(_))), 
    Comparer.OrdinalIgnoreCase
  )
in
  Solution

Power Query solution 4 for Find Missing Alphabets, proposed by 🇰🇷 Taeyong Shin:

let
  Source = Excel.CurrentWorkbook(){[Name = "tblData"]}[Content], 
  Result = Table.FromList(
    List.Select(
      {"a" .. "z"}, 
      each List.IsEmpty(List.FindText(List.Transform(Source[First Ladies], Text.Lower), _))
    ), 
    Splitter.SplitByNothing(), 
    type table [Answer = text]
  )
in
  Result

Power Query solution 5 for Find Missing Alphabets, proposed by Aditya Kumar Darak 🇮🇳:

let
  Source    = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Transform = List.Combine(Table.TransformRows(Source, each Text.ToList([First Ladies]))), 
  Return    = List.Difference({"a" .. "z"}, Transform, Comparer.OrdinalIgnoreCase)
in
  Return

Power Query solution 6 for Find Missing Alphabets, proposed by Aditya Kumar Darak 🇮🇳:

let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Transform = Table.TransformColumns(
    Source, 
    {"First Ladies", each Text.ToList(Text.Select(_, {"A" .. "Z", "a" .. "z"}))}
  )[First Ladies], 
  Return = List.Select(
    {"a" .. "z"}, 
    each not List.Contains(List.Combine(Transform), _, Comparer.OrdinalIgnoreCase)
  )
in
  Return

Power Query solution 7 for Find Missing Alphabets, proposed by Alejandro Simón 🇵🇦 🇪🇸:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Sol = List.RemoveMatchingItems(
    {"a" .. "z"}, 
    List.Distinct(
      List.Combine(
        Table.AddColumn(
          Source, 
          "Custom", 
          each List.Transform(Text.ToList([First Ladies]), Text.Lower)
        )[Custom]
      )
    )
  )
in
  Sol

Power Query solution 8 for Find Missing Alphabets, proposed by Luan Rodrigues:

let
  Fonte = Tabela1, 
  res = [
    a = List.Distinct(
      List.Combine(
        Table.AddColumn(Fonte, "Personalizar", each Text.ToList(Text.Lower([First Ladies])))[
          Personalizar
        ]
      )
    ), 
    b = {"a" .. "z"}, 
    c = List.Difference(b, a)
  ][c]
in
  res

Power Query solution 9 for Find Missing Alphabets, proposed by Alexis Olson:

List.RemoveItems(
⠀⠀{"a" .. "z"}, 
⠀⠀List.Distinct(
⠀⠀⠀⠀List.Union(
⠀⠀⠀⠀⠀⠀List.Transform(
⠀⠀⠀⠀⠀⠀⠀⠀each Text.ToList(Text.Lower(_))
⠀⠀⠀⠀⠀⠀)
⠀⠀⠀⠀)
⠀⠀)
)

Power Query solution 10 for Find Missing Alphabets, proposed by Brian Julius:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  ToList = Table.AddColumn(Source, "Letters", each Text.ToList([First Ladies])), 
  Expand = Table.ExpandListColumn(ToList, "Letters"), 
  Lowercase = Table.TransformColumns(Expand, {{"Letters", Text.Lower, type text}}), 
  DeDupe = Table.Distinct(Lowercase, {"Letters"}), 
  RemoveMatching = List.RemoveMatchingItems({"a" .. "z"}, DeDupe[Letters]), 
  ConvertToTable = Table.FromList(
    RemoveMatching, 
    Splitter.SplitByNothing(), 
    {"Answer"}, 
    null, 
    ExtraValues.Error
  )
in
  ConvertToTable

Power Query solution 11 for Find Missing Alphabets, proposed by Krzysztof Kominiak:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Ls     = Table.AddColumn(Source, "Ls", each Text.ToList(Text.Lower([First Ladies])))[Ls], 
  Result = List.Difference({"a" .. "z"}, List.Distinct(List.Combine(Ls)))
in
  Result

Power Query solution 12 for Find Missing Alphabets, proposed by Guillermo Arroyo:

let
  Origen = Excel.CurrentWorkbook(){[Name = "Tabla1"]}[Content], 
  a = List.RemoveItems(
    {"a" .. "z"}, 
    List.Union(List.Transform(Origen[First Ladies], each Text.ToList(Text.Lower(_))))
  )
in
  a

Power Query solution 14 for Find Missing Alphabets, proposed by Mahmoud Bani Asadi:

let
  Source = Excel.CurrentWorkbook(){[Name = "tblFladies"]}[Content], 
  SplitColumnbyPosition = Table.ExpandListColumn(
    Table.TransformColumns(
      Source, 
      {
        {
          "First Ladies", 
          Splitter.SplitTextByRepeatedLengths(1), 
          let
            itemType = (type nullable text) meta [Serialized.Text = true]
          in
            type {itemType}
        }
      }
    ), 
    "First Ladies"
  ), 
  LowercasedText = Table.TransformColumns(
    SplitColumnbyPosition, 
    {{"First Ladies", Text.Lower, type text}}
  ), 
  RemovedDuplicates = Table.Distinct(LowercasedText), 
  Custom = List.Difference({"a" .. "z"}, RemovedDuplicates[First Ladies])
in
  Custom

Solving the challenge of Find Missing Alphabets with Excel

Excel solution 1 for Find Missing Alphabets, proposed by Bo Rydobon 🇹🇭:

=LET(c,CHAR(SEQUENCE(26)+96),FILTER(c,COUNTIF(A2:A48,"*"&c&"*")=0))

=LET(c,CHAR(SEQUENCE(26)+96),FILTER(c,ISERR(SEARCH(c,CONCAT(A2:A48)))))

Excel solution 2 for Find Missing Alphabets, proposed by Rick Rothstein:

=LET(c,CHAR(ROW(97:122)),FILTER(c,COUNTIF(A2:A48,"*"&c&"*")=0))

Excel solution 3 for Find Missing Alphabets, proposed by John V.:

=UNIQUE(MID(CONCAT(A2:A48,CHAR(ROW(97:122))),ROW(1:999),1),,1)

Excel solution 4 for Find Missing Alphabets, proposed by Kris Jaganah:

=LET(a,CONCAT(A2:A48),b,CHAR(SEQUENCE(26,,CODE("a"))),FILTER(b,IFERROR(SEARCH(b,a),0)=0))

Excel solution 5 for Find Missing Alphabets, proposed by Julian Poeltl:

=LET(A,CHAR(SEQUENCE(26)+96),T,LOWER(CONCAT(A2:A48)),FILTER(A,ISERR(SEARCH(A,T))))

Excel solution 6 for Find Missing Alphabets, proposed by Julian Poeltl:

=LET(A,CHAR(SEQUENCE(26)+96),T,LOWER(CONCAT(A2:A48)),FILTER(A,LEN(T)=LEN(SUBSTITUTE(T,A,""))))

Excel solution 7 for Find Missing Alphabets, proposed by Aditya Kumar Darak 🇮🇳:

=LET(
 _d, A2:A48,
 _chr, CHAR(SEQUENCE(26, , 97)),
 _tf, ISNA(XMATCH("*" & _chr & "*", _d, 2)),
 _r, FILTER(_chr, _tf),
 _r
)


=LET(
 _d, A2:A48,
 _chr, CHAR(SEQUENCE(26, , 97)),
 _tf, COUNTIFS(_d, "*" & _chr & "*") = 0,
 _r, FILTER(_chr, _tf),
 _r
)

Excel solution 8 for Find Missing Alphabets, proposed by Timothée BLIOT:

=LET(A,UNIQUE(LOWER(MID(CONCAT(A2:A48),SEQUENCE(LEN(CONCAT(A2:A48))),1))), B,CHAR(SEQUENCE(26,,97)),
FILTER(B,MAP(B,LAMBDA(b,NOT(ISNUMBER(XMATCH(b,A)))))))

Excel solution 9 for Find Missing Alphabets, proposed by Hussein SATOUR:

=LET(a,CHAR(SEQUENCE(26,,97)), FILTER(a, ISERR(FIND(a,LOWER(CONCAT(A2:A48))))))

Excel solution 10 for Find Missing Alphabets, proposed by Oscar Mendez Roca Farell:

=LET(_s, CHAR( SEQUENCE(26, ,97)), FILTER(_s, ISNA(SEARCH(_s&"*", ARRAYTOTEXT(A2:A48)))))

Excel solution 11 for Find Missing Alphabets, proposed by Sunny Baggu:

=LET(_alpha,CHAR(SEQUENCE(26,,CODE("a"))),
_cond,MAP(_alpha,LAMBDA(a,SEARCH(a,LOWER(CONCAT(A2:A48))))),
FILTER(_alpha,ISERR(_cond)))

Excel solution 12 for Find Missing Alphabets, proposed by Sunny Baggu:

=LET(_alpha,CHAR(SEQUENCE(26,,CODE("a"))),
_let,LOWER(CONCAT(A2:A48)),
_m,MID(_let,SEQUENCE(LEN(_let)),1),
TOCOL(IFS(MAP(_alpha,LAMBDA(a,AND(ISNUMBER(TOCOL(SEARCH(a,_m),3)))))-1,_alpha),3))

Excel solution 13 for Find Missing Alphabets, proposed by Md. Zohurul Islam:

=LET(u,CONCAT(A2:A48),
v,UNIQUE(MID(u,SEQUENCE(LEN(u)),1)),
w,FILTER(v,BYROW(ABS(v={" ","."}),SUM)=0),
a,CHAR(SEQUENCE(26,,97)),
z,FILTER(a,ISERROR(XMATCH(a,w))),
z)

Excel solution 14 for Find Missing Alphabets, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:

=LET(i;TEXTJOIN(;;A2:A48);MID(i;SEQUENCE(LEN(i));1))
cell d3= LET(q;CHAR(ROW(A65:A90));LOWER(LET(a;IFERROR(INDEX(q;IF(COUNTIF(C3#;q&"*")=0;SEQUENCE(SUM(LEN(q)));""));"");FILTER(a;a<>""))))

Excel solution 15 for Find Missing Alphabets, proposed by Stefan Olsson:

=QUERY(
INDEX(LET(_c, CHAR(SEQUENCE(26, 1, 97)), IF(REGEXMATCH(TEXTJOIN("", 1, A2:A48), "(?i)"&_c),, _c))),
"Where Col1 is not null", 0)

Excel solution 16 for Find Missing Alphabets, proposed by Peter Bartholomew:

= LET(
 alphabet, CHAR(SEQUENCE(26,,97)),
 distinct, UNIQUE(TOROW(
 MID(names, SEQUENCE(1,22), 1)
 )),
 missing, ISERROR(
 XMATCH(alphabet, distinct)),
 FILTER(alphabet, missing)
 )

Excel solution 17 for Find Missing Alphabets, proposed by Nicolas Micot:

=LET(_lettres;CAR(SEQUENCE(26;1;97));_textes;MINUSCULE(A1:A47);_compte;BYROW(_lettres;LAMBDA(a;SOMME(SI(SIERREUR(CHERCHE(a;_textes);0)>0;1;0))));FILTRE(_lettres;_compte=0))

Excel solution 18 for Find Missing Alphabets, proposed by Giorgi Goderdzishvili:

=list,1,0))>=1,1,0))),
FILTER(CHAR(96+SEQUENCE(26)),filtering-1))

Excel solution 19 for Find Missing Alphabets, proposed by Guillermo Arroyo:

=TEXTSPLIT(CONCAT(CHAR(SEQUENCE(26,,97))),,MID(LOWER(CONCAT(A2:A48)),SEQUENCE(SUM(LEN(A2:A48))),1),1)

Excel solution 20 for Find Missing Alphabets, proposed by Mohamed Helmy:

=LET(
v,CHAR(ROW(65:90)),
FILTER(v,ISERR( SEARCH(v,CONCAT(A2:A48)))))

Excel solution 21 for Find Missing Alphabets, proposed by Gabriel Raigosa:

=LET(az,CHAR(SEQUENCE(26,,97)),FILTER(az,ISERROR(SEARCH(az,CONCAT(A2:A48))))) 

 =LET(rng,CONCAT(A2:A48),az,CHAR(SEQUENCE(26,,97)),FILTER(az,ISERROR(XMATCH(az,UNIQUE(MID(rng, SEQUENCE(LEN(rng)), 1 )))))) 

▶️ES:
 =LET(az,CARACTER(SECUENCIA(26,,97)),FILTRAR(az,ESERROR(HALLAR(az,CONCAT(A2:A48))))) 

=LET(rng,CONCAT(A2:A48),az,CARACTER(SECUENCIA(26,,97)),FILTRAR(az,ESERROR(COINCIDIRX(az,UNICOS(EXTRAE(rng, SECUENCIA(LARGO(rng)), 1 ))))))

Excel solution 22 for Find Missing Alphabets, proposed by roberto mensa:

=LET(a,CHAR(SEQUENCE(26,,97)),t,CONCAT(A1:A47,a),FILTER(a,LEN(t)-SEARCH(a,t)<26))

Excel solution 23 for Find Missing Alphabets, proposed by Daniel Madhadha:

=FILTER(CHAR(96+SEQUENCE(26)),IFNA(VLOOKUP(CHAR(96+SEQUENCE(26)),UNIQUE(LOWER(MID(CONCAT(A2:A48),SEQUENCE(LEN(CONCAT(A2:A48))),1))),1,FALSE),"")="")

Excel solution 24 for Find Missing Alphabets, proposed by David Wasserman:

=LET(alpha,CHAR(96+SEQUENCE(26)),allnames,LOWER(TEXTJOIN("",TRUE,A2:A48)),FILTER(alpha,ISERROR(FIND(alpha,allnames))))

Excel solution 25 for Find Missing Alphabets, proposed by Britt Deaton, FSA:

=LET(names,A2:A48,strng,CONCAT(names),letters,UPPER(SORT(UNIQUE(MID(strng,SEQUENCE(LEN(strng)),1)))),alphabet,CHAR(SEQUENCE(26,1,65)),matches,ISNUMBER(MATCH(alphabet,letters,0)),FILTER(alphabet,NOT(matches)))

Excel solution 26 for Find Missing Alphabets, proposed by Ben Gutscher:

=LET(letter,CHAR(SEQUENCE(26,,97)),ltrct,COUNTIF(A2:A48,"*"&letter&"*"),TAKE(FILTER(HSTACK(letter,ltrct),ltrct=0),,1))

Solving the challenge of Find Missing Alphabets with Power Query

Solving the challenge of Find Missing Alphabets with Excel

Leave a Reply Cancel reply