Find Pangram Sentences

A Pangram (also known as Holoalphabetic) sentence is that which contains all 26 letters of English alphabets at least once. List those sentences from column A which are Pangrams.

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

Solving the challenge of List All Pangram Sentences with Power Query

Power Query solution 1 for List All Pangram Sentences, proposed by Bo Rydobon 🇹🇭:

let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 Fil = Table.SelectRows(Source, each Text.Length(Text.Select( Text.Combine({"A".."Z"}), Text.ToList(Text.Upper([Sentences]))))=26)
in
 Fil


let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 Fil = Table.SelectRows(Source, each List.Count(List.Intersect({{"A".."Z"},Text.ToList(Text.Upper([Sentences]))}))=26)
in
 Fil

Power Query solution 2 for List All Pangram Sentences, proposed by Zoran Milokanović:

let
  Source = Excel.CurrentWorkbook(){[Name = "Pangram"]}[Content], 
  FilteredPangrams = Table.SelectRows(
    Source, 
    each List.Count(
      List.Select(
        List.Distinct(Text.ToList(Text.Upper([Sentences]))), 
        each List.Contains({"A" .. "Z"}, _)
      )
    )
      = List.Count({"A" .. "Z"})
  ), 
  RenamedSentences = Table.RenameColumns(FilteredPangrams, {{"Sentences", "Expected Answer"}})
in
  RenamedSentences

Power Query solution 3 for List All Pangram Sentences, proposed by Aditya Kumar Darak 🇮🇳:

let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Return = Table.SelectRows(
    Source, 
    each [
      Lower = Text.Lower([Sentences]), 
      TF    = List.Transform({"a" .. "z"}, (f) => Text.Contains(Lower, f)), 
      All   = List.AllTrue(TF)
    ][All]
  )
in
  Return

Power Query solution 4 for List All Pangram Sentences, proposed by Alejandro Simón 🇵🇦 🇪🇸:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Helper = Table.AddColumn(
    Source, 
    "Custom", 
    each List.ContainsAll(Text.ToList(Text.Lower([Sentences])), {"a" .. "z"})
  ), 
  Sol = Table.SelectRows(Helper, each ([Custom] = true))[[Sentences]]
in
  Sol

Power Query solution 5 for List All Pangram Sentences, proposed by Luan Rodrigues:

let
  Fonte = Tabela1, 
  tab = Table.AddColumn(
    Fonte, 
    "Personalizar", 
    each List.Count(
      List.Select(
        List.Distinct(Text.ToList(Text.Lower([Sentences]))), 
        each List.ContainsAll({"a" .. "z"}, {_}) = true
      )
    )
  ), 
  res = Table.SelectRows(tab, each ([Personalizar] = 26))[[Sentences]]
in
  res

Power Query solution 6 for List All Pangram Sentences, proposed by Brian Julius:

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "PZDLboUgEIZfZera9AW6apddtTlNujAuUBBGETxcRHn6jmC6I5nvv9F1zWNTaA6wEySFQcAzMhdyC3PkUsB6wm5T07dd86OuI44LDM4mA5M9CFo3D3YXDgKdNcsncCtf/wUT7gJ4FFqjkZAwM8d9kVUvfVb2i5EvhQ1kmjCoW2izMKDxGa0jkfQVfpzrxoLCXDxt1JzwA74jWwTYYRZjIPUNf9KTl7KGZAqkY4O/hySmQy7UO3V3tHditOyqR5q7YJlXoA+HUgXY8RCmjni7Kp7knvT1ceNSM38v3xYGxmtoS4S7M2eUHnZxvDR9/wc=", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Sentences = _t]
  ), 
  Dupe = Table.DuplicateColumn(Source, "Sentences", "Letters"), 
  Lower = Table.TransformColumns(Dupe, {{"Letters", Text.Lower, type text}}), 
  FiltList = Table.AddColumn(Lower, "LetterList", each Text.ToList([Letters])), 
  Expand = Table.SelectRows(
    Table.ExpandListColumn(FiltList, "LetterList"), 
    each List.ContainsAny({[LetterList]}, {"a" .. "z"})
  ), 
  #"Grouped Rows" = Table.RemoveColumns(
    Table.SelectRows(
      Table.Group(
        Expand, 
        {"Sentences"}, 
        {{"Count", each Table.RowCount(Table.Distinct(_)), Int64.Type}}
      ), 
      each [Count] = 26
    ), 
    "Count"
  )
in
  #"Grouped Rows"

Power Query solution 7 for List All Pangram Sentences, proposed by Bhavya Gupta:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Final = Table.SelectRows(
    Source, 
    each List.Accumulate(
      {"a" .. "z"}, 
      true, 
      (s, c) => List.AllTrue({s, Text.Contains(Text.Lower([Sentences]), c)})
    )
  )
in
  Final

Power Query solution 8 for List All Pangram Sentences, proposed by Rafael González B.:

let
  Source = Excel.CurrentWorkbook(){[Name = "Sentences"]}[Content], 
  ConvertToLower = Table.AddColumn(
    Source, 
    "Check", 
    each Text.Select(Text.Lower([Sentences]), {"a" .. "z"})
  ), 
  Results = Table.RemoveColumns(
    Table.SelectRows(
      Table.AddColumn(
        ConvertToLower, 
        "Quantity", 
        each List.Count(List.Distinct(Text.ToList([Check])))
      ), 
      each [Quantity] >= 26
    ), 
    {"Quantity", "Check"}
  )
in
  Results

Power Query solution 9 for List All Pangram Sentences, proposed by Krzysztof Kominiak:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Result = Table.SelectRows(
    Source, 
    each List.Count(List.Intersect({Text.ToList(Text.Upper([Sentences])), {"A" .. "Z"}})) = 26
  )
in
  Result

Power Query solution 10 for List All Pangram Sentences, proposed by Jan Willem Van Holst:

let
  Source = Table.FromRows(
    Json.Document(
      Binary.Decompress(
        Binary.FromText(
          "PZDLboUgEIZfZera9AW6apddtTlNujAuUBBGETxcRHn6jmC6I5nvv9F1zWNTaA6wEySFQcAzMhdyC3PkUsB6wm5T07dd86OuI44LDM4mA5M9CFo3D3YXDgKdNcsncCtf/wUT7gJ4FFqjkZAwM8d9kVUvfVb2i5EvhQ1kmjCoW2izMKDxGa0jkfQVfpzrxoLCXDxt1JzwA74jWwTYYRZjIPUNf9KTl7KGZAqkY4O/hySmQy7UO3V3tHditOyqR5q7YJlXoA+HUgXY8RCmjni7Kp7knvT1ceNSM38v3xYGxmtoS4S7M2eUHnZxvDR9/wc=", 
          BinaryEncoding.Base64
        ), 
        Compression.Deflate
      )
    ), 
    let
      _t = ((type nullable text) meta [Serialized.Text = true])
    in
      type table [Sentences = _t]
  ), 
  #"Added Custom" = Table.AddColumn(
    Source, 
    "Custom", 
    each List.ContainsAll(Text.ToList(Text.Lower([Sentences])), {"a" .. "z"})
  ), 
  #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = true))[Sentences]
in
  #"Filtered Rows"

Power Query solution 11 for List All Pangram Sentences, proposed by Udit Chatterjee:

let
  fxPangramCheck = (sentence as text) =>
    let
      uniqueCharList    = List.Distinct(Text.ToList(Text.Lower(sentence))), 
      vocabContainCheck = List.ContainsAll(uniqueCharList, {"a" .. "z"})
    in
      vocabContainCheck, 
  Source = Challenge146, 
  customFunctionInvoke = Table.AddColumn(
    Source, 
    "Pangram Check", 
    each fxPangramCheck([Sentences]), 
    type text
  ), 
  filteredRows = Table.SelectRows(customFunctionInvoke, each ([Pangram Check] = true)), 
  keepValidSentences = Table.SelectColumns(filteredRows, {"Sentences"})
in
  keepValidSentences

Power Query solution 12 for List All Pangram Sentences, proposed by Sue Bayes:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Custom = Table.AddColumn(
    Source, 
    "Copy", 
    each Text.Remove(Text.Lower([Sentences]), {Character.FromNumber(0) .. Character.FromNumber(96)})
  ), 
  TextToList = Table.AddColumn(Custom, "Custom", each Text.ToList([Copy])), 
  Expand = Table.ExpandListColumn(TextToList, "Custom"), 
  RemoveDup = Table.Distinct(Expand), 
  Group = Table.Group(RemoveDup, {"Sentences"}, {{"Count", each Table.RowCount(_), Int64.Type}}), 
  Filter = Table.SelectRows(Group, each ([Count] = 26))[[Sentences]]
in
  Filter

Solving the challenge of List All Pangram Sentences with Excel

Excel solution 1 for List All Pangram Sentences, proposed by Bo Rydobon 🇹🇭:

=TOCOL(MAP(A2:A10,LAMBDA(a,IF(AND(SEARCH(CHAR(SEQUENCE(26,,65)),a)),a))),3)

Excel solution 2 for List All Pangram Sentences, proposed by Rick Rothstein:

=FILTER(A2:A10,MAP(A2:A10,LAMBDA(x,1-(LEN(REDUCE(CONCAT(CHAR(SEQUENCE(26,,65))),MID(UPPER(x),SEQUENCE(LEN(x)),1),LAMBDA(a,c,SUBSTITUTE(a,c,""))))>0))))

Excel solution 3 for List All Pangram Sentences, proposed by John V.:

=FILTER(A2:A10,MAP(A2:A10,LAMBDA(x,1-OR(ISERR(SEARCH(CHAR(ROW(65:90)),x))))))

✅ =FILTER(A2:A10,MAP(A2:A10,LAMBDA(x,COUNT(SEARCH(CHAR(ROW(65:90)),x))=26)))

Excel solution 4 for List All Pangram Sentences, proposed by محمد حلمي:

=FILTER(A2:A10,MAP(A2:A10,LAMBDA(A, COUNT(SEARCH(CHAR(ROW(65:90)),A))=26)))

Excel solution 5 for List All Pangram Sentences, proposed by 🇰🇷 Taeyong Shin:

=TOCOL(IFNA(MAP(A2:A10, LAMBDA(m, XMATCH(0, COUNTIF(m, "*" & CHAR(SEQUENCE(26) + 64) & "*")) )) & x, A2:A10), 2)

=FILTER(A2:A10, MAP(A2:A10, LAMBDA(m, AND(ISNUMBER(SEARCH(CHAR(SEQUENCE(26) + 64), m)))
 )) )

Excel solution 6 for List All Pangram Sentences, proposed by Kris Jaganah:

=FILTER(A2:A10,MAP(A2:A10,LAMBDA(x,LET(a,MID(LOWER(x),SEQUENCE(LEN(x)),1),SUM(XLOOKUP(CHAR(SEQUENCE(26,,97)),a,a,1)))))=0)

Excel solution 7 for List All Pangram Sentences, proposed by Julian Poeltl:

=FILTER(A2:A10,MAP(A2:A10,LAMBDA(S,LET(A,CHAR(64+SEQUENCE(26)),SP,MID(S,SEQUENCE(LEN(S)),1),SUM(--ISNUMBER(XMATCH(A,SP)))=26))))

Excel solution 8 for List All Pangram Sentences, proposed by Aditya Kumar Darak 🇮🇳:

=LET(
 _d, A2:A10,
 _chr, CHAR(SEQUENCE(1, 26, 65)),
 _c1, --ISNUMBER(SEARCH(_chr, _d)),
 _c2, MMULT(_c1, SEQUENCE(26, , 1, 0)),
 _r, FILTER(_d, _c2 = 26),
 _r
)

Excel solution 9 for List All Pangram Sentences, proposed by Aditya Kumar Darak 🇮🇳:

=LET(
 _d, A2:A10,
 _chr, CHAR(SEQUENCE(1, 26, 65)),
 _c1, COUNTIFS(_d, _d, _d, "*" & _chr & "*"),
 _c2, MMULT(_c1, SEQUENCE(26, , 1, 0)),
 _r, FILTER(_d, _c2 = 26),
 _r
)

Excel solution 10 for List All Pangram Sentences, proposed by Timothée BLIOT:

=FILTER(A2:A10,MAP(LOWER(A2:A10), LAMBDA(x, RIGHT(CONCAT(SORT(UNIQUE(MID(x,SEQUENCE(LEN(x)),1)))),26)=CONCAT(CHAR(SEQUENCE(26,,97))) )))

Excel solution 11 for List All Pangram Sentences, proposed by Sunny Baggu:

=FILTER(A2:A10,MAP(A2:A10,LAMBDA(x,
LET(_m,CODE(UPPER(UNIQUE(MID(x,SEQUENCE(LEN(x)),1)))),
AND(ISNUMBER(XMATCH(SEQUENCE(26)+64,_m)))))))

Excel solution 12 for List All Pangram Sentences, proposed by Sunny Baggu:

=FILTER(A2:A10,
MAP(A2:A10,LAMBDA(x,
LET(_m,CODE(UPPER(UNIQUE(MID(x,SEQUENCE(LEN(x)),1)))),
SUM(FILTER(_m,(_m>=65)*(_m<=90)))=SUM(SEQUENCE(26)+64)))))

Excel solution 13 for List All Pangram Sentences, proposed by Sunny Baggu:

=FILTER(A2:A10,MAKEARRAY(ROWS(A2:A10),1,LAMBDA(r,c,
LET(_r,INDEX(A2:A10,r,1),_a2z,CONCAT(CHAR(SEQUENCE(26,,65))),
REDUCE(_a2z,UPPER(UNIQUE(MID(_r,SEQUENCE(LEN(_r)),1))),LAMBDA(a,v,SUBSTITUTE(a,v,"")))=""))))

Excel solution 14 for List All Pangram Sentences, proposed by Md. Zohurul Islam:

=FILTER(A2:A10,MAP(A2:A10,LAMBDA(x,LET(a,LOWER(x),b,MID(a,SEQUENCE(LEN(a)),1),c,CHAR(SEQUENCE(26,,97)),d,XMATCH(c,b),
e,COUNT(FILTER(d,ISNUMBER(d))),e)))=26)

Excel solution 15 for List All Pangram Sentences, proposed by Charles Roldan:

=LAMBDA(x,f, FILTER(x, MAP(x, f)))(A2:A10, LAMBDA(x, AND(ISNUMBER(SEARCH(CHAR(SEQUENCE(26, , CODE("a"))), x)))))

Excel solution 16 for List All Pangram Sentences, proposed by Jaroslaw Kujawa:

=LET( y ,MAP( DB2:DB10 ,LAMBDA( x , LET(a , UNIQUE(MID(x , SEQUENCE( , LEN(x)) , 1) , 1) ,b , SORT(UPPER(a) ,  , 1 , 1) ,c , 26=SUM(1*(CHAR(SEQUENCE(26 ,  , 65))=FILTER(b , ISERROR(XMATCH(b , {"." , " " , "," , "!" , ";"}))))) ,d , IFERROR(IF(c , x) , "") , d))),FILTER(y , y<>FALSE))

Excel solution 17 for List All Pangram Sentences, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:

=LET(p;MAP(A2:A10;LAMBDA(x;IF(ISNUMBER(VALUE(IFERROR(TEXTJOIN(;;FIND({"a";"b";"c";"d";"e";"f";"g";"h";"i";"j";"k";"l";"m";"n";"o";"p";"q";"r";"s";"t";"u";"x";"w";"v";"y"};LOWER(x);1));"")));x;"")));FILTER(p;p<>""))

Excel solution 18 for List All Pangram Sentences, proposed by Stefan Olsson:

=FILTER(A2:A10, 
BYROW(A2:A10, 
LAMBDA(br, 
AND(REGEXMATCH(UPPER(br), CHAR(SEQUENCE(26,1,65))))
)))

Excel solution 19 for List All Pangram Sentences, proposed by Abhishek Kumar Jain:

=FILTER(A2:A10,MAP(A2:A10,LAMBDA(x,LET(a,UNIQUE(UPPER(MID(x,SEQUENCE(LEN(x)),1))),COUNTA(FILTER(a,(CODE(a)>64)*(CODE(a)<91)))=26))))

Excel solution 20 for List All Pangram Sentences, proposed by Guillermo Arroyo:

=FILTER(A2:A10,MAP(A2:A10,LAMBDA(b,LET(a,CODE(UNIQUE(MID(UPPER(b),SEQUENCE(LEN(b)),1))),SUM((a<=90)*(a>=65))=26))))
=FILTER(A2:A10,MAP(A2:A10,LAMBDA(b,AND(ISNUMBER(SEARCH(CHAR(SEQUENCE(26)+64),UPPER(b)))))))
=FILTER(A2:A10,BYROW(ISNUMBER(SEARCH(CHAR(SEQUENCE(,26)+64),UPPER(A2:A10))),LAMBDA(a, AND(a))))
=IFERROR(INDEX(A2:A10,AGGREGATE(15,3,1/(MMULT(--ISNUMBER(SEARCH(CHAR(COLUMN(A:Z)+64),UPPER(A2:A10))),ROW(1:26)/ROW(1:26))=26)*ROW(1:99),ROW(1:10)),1),"")

Excel solution 21 for List All Pangram Sentences, proposed by Anup Kumar:

=FILTER(A2:A10,
BYROW(A2:A10,LAMBDA(a,
LET(
txt, a,
chrSet, CHAR(SEQUENCE(26,,65,1)),
txtArr, UPPER(MID(txt,SEQUENCE(LEN(txt),,1),1)),
IFNA(SUM(XMATCH(chrSet,txtArr)),FALSE)
)
))
)

Excel solution 22 for List All Pangram Sentences, proposed by Paolo Pozzoli:

=FILTRO(A2:A10;
 MAP(A2:A10;LAMBDA(snt;
 LET(s;snt;
 letters;UNICI(MINUSC(STRINGA.ESTRAI(s;SEQUENZA(LUNGHEZZA(s));1)));
 out;CONTA.NUMERI(FILTRO(CODICE(letters);CODICE(letters)>96))=26;
 out))
 )
)

Excel solution 23 for List All Pangram Sentences, proposed by Rayan S.:

=LET(
 x, MAP(
 A2:A10,
 LAMBDA(x,
 LET(
 a, CODE(UNIQUE(MID(x, SEQUENCE(LEN(x)), 1))),
 b, COUNTA(FILTER(a, (a > 64))),
 IF(b = 26, x, 0)
 )
 )
 ),
 FILTER(x, x > 0)
)

Solving the challenge of List All Pangram Sentences with Power Query

Solving the challenge of List All Pangram Sentences with Excel

Leave a Reply Cancel reply