Home » Vowel End and Start Words

Vowel End and Start Words

List those sentences which contain any pair of words where first word ends with vowel and next word starts with vowel. Examples – 1. “The orchestra is playing” “The” finishes with “e” and “orchestra” starts with “o”. 2. “Kangaroo on a vacation” “Kangaroo” finishes with “o” and “on” starts with “o”.

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

Solving the challenge of Vowel End and Start Words with Power Query

Power Query solution 1 for Vowel End and Start Words, proposed by Bo Rydobon 🇹🇭: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Sel = Table.SelectRows(
    Source, 
    each 
      let
        v = {"A", "E", "I", "O", "U"}, 
        b = Text.Split(Text.Upper([Sentence]), " ")
      in
        List.AnyTrue(
          List.Transform(
            {0 .. List.Count(b) - 2}, 
            each Text.Select(Text.End(b{_}, 1), v)
              > "" and Text.Select(Text.Start(b{_ + 1}, 1), v)
              > ""
          )
        )
  )
in
  Sel
Power Query solution 2 for Vowel End and Start Words, proposed by Zoran Milokanović: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Data"]}[Content], 
  AddedVowelConsonant = Table.AddColumn(
    Source, 
    "VowelConsonant", 
    each List.Accumulate(
      Text.Split([Sentence], " "), 
      "", 
      (s, d) =>
        s
          & "+"
          & (
            if List.Contains({"a", "A", "e", "E", "i", "I", "o", "O", "u", "U"}, Text.Start(d, 1)) then
              "V"
            else
              "C"
          )
          & "-"
          & (
            if List.Contains({"a", "A", "e", "E", "i", "I", "o", "O", "u", "U"}, Text.End(d, 1)) then
              "V"
            else
              "C"
          )
    )
  ), 
  FilteredVowelVowel = Table.SelectRows(
    AddedVowelConsonant, 
    each Text.Contains([VowelConsonant], "V+V")
  )[[Sentence]], 
  Solution = Table.RenameColumns(FilteredVowelVowel, {{"Sentence", "Answer Expected"}})
in
  Solution
Power Query solution 3 for Vowel End and Start Words, proposed by Aditya Kumar Darak 🇮🇳: 
let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Vowels = {"a", "e", "i", "o", "u"}, 
  Calc = Table.AddColumn(
    Source, 
    "Calc", 
    each [
      S = Text.Split([Sentence], " "), 
      C = {0 .. List.Count(S) - 2}, 
      F = List.Transform(
        C, 
        (f) =>
          List.Contains(Vowels, Text.End(S{f}, 1))
            and List.Contains(Vowels, Text.Start(S{f + 1}, 1))
      ), 
      R = List.AnyTrue(F)
    ][R]
  ), 
  Return = Table.SelectRows(Calc, each [Calc])[[Sentence]]
in
  Return
Power Query solution 4 for Vowel End and Start Words, proposed by Brian Julius: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Dupe = Table.DuplicateColumn(Source, "Sentence", "Words"), 
  SplitToRowa = Table.ExpandListColumn(
    Table.TransformColumns(
      Dupe, 
      {
        {
          "Words", 
          Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), 
          let
            itemType = (type nullable text) meta [Serialized.Text = true]
          in
            type {itemType}
        }
      }
    ), 
    "Words"
  ), 
  Vowels = {"a", "e", "i", "o", "u"}, 
  AddIndex = Table.AddIndexColumn(SplitToRowa, "Index", 0, 1), 
  TestLastLetter = Table.AddColumn(
    AddIndex, 
    "EndsWith", 
    each List.Contains(Vowels, Text.End([Words], 1))
  ), 
  TestFirstLetterNext = Table.AddColumn(
    TestLastLetter, 
    "NextStartsWith", 
    each try List.Contains(Vowels, Text.Start(TestLastLetter[Words]{[Index] + 1}, 1)) otherwise null
  ), 
  AddBothTrue = Table.AddColumn(
    TestFirstLetterNext, 
    "BothTrue", 
    each if List.AllTrue({[EndsWith], [NextStartsWith]}) then 1 else 0
  ), 
  Regroup = Table.RemoveColumns(
    Table.SelectRows(
      Table.Group(AddBothTrue, {"Sentence"}, {{"AnyPairs", each List.Sum([BothTrue]), type number}}), 
      each [AnyPairs] = 1
    ), 
    "AnyPairs"
  )
in
  Regroup
Power Query solution 5 for Vowel End and Start Words, proposed by Bhavya Gupta: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  myFn = (sent as text) =>
    List.AnyTrue(
      List.Transform(
        List.Split(
          List.RemoveLastN(
            List.Skip(
              List.TransformMany(
                Text.Split(sent, " "), 
                (x) => [l = Text.ToList(x), s = {l{0}} & {List.Last(l)}][s], 
                (a, b) => b
              )
            )
          ), 
          2
        ), 
        each List.ContainsAll({"a", "e", "i", "o", "u"}, _, Comparer.OrdinalIgnoreCase)
      )
    ), 
  Final = Table.SelectRows(Source, each myFn([Sentence]))
in
  Final
Power Query solution 6 for Vowel End and Start Words, proposed by Victor Wang: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Result = List.Select(
    Source[Sentence], 
    (a) =>
      List.AnyTrue(
        List.Transform(
          Text.PositionOfAny(a, {" "}, Occurrence.All), 
          (b) =>
            List.ContainsAll(
              {"a", "e", "i", "o", "u"}, 
              {Text.At(a, b - 1), Text.At(a, b + 1)}, 
              Comparer.OrdinalIgnoreCase
            )
        )
      )
  )
in
  Result
Power Query solution 7 for Vowel End and Start Words, proposed by Krzysztof Kominiak: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  ListAccum = List.Accumulate(
    {"a", "e", "i", "o", "u"}, 
    Table.AddColumn(Source, "Test", each Text.Lower([Sentence])), 
    (s, c) => Table.ReplaceValue(s, c, "*", Replacer.ReplaceText, {"Test"})
  ), 
  Result = Table.SelectRows(ListAccum, each Text.Contains([Test], "* *"))[[Sentence]]
in
  Result

Solving the challenge of Vowel End and Start Words with Excel

Excel solution 1 for Vowel End and Start Words, proposed by Bo Rydobon 🇹🇭: 
=FILTER(A2:A10,MAP(A2:A10,LAMBDA(a,LET(v,"AEIOU",b,TEXTSPLIT(a,," "),COUNT(SEARCH(RIGHT(b),v)+DROP(SEARCH(LEFT(b),v),1))))))
Excel solution 2 for Vowel End and Start Words, proposed by Rick Rothstein: 
=FILTER(A2:A10,MAP(A2:A10,LAMBDA(a,ISNUMBER(FIND("11",CONCAT(IF(ISERR(SEARCH(MID(a,SEQUENCE(,LEN(a)),2)," a e i o u "))," ",1)))))))
Excel solution 3 for Vowel End and Start Words, proposed by John V.: 
=TOCOL(MAP(A2:A10,LAMBDA(s,LET(v,{"a";"e";"i";"o";"u"},IF(COUNT(FIND(v&" "&TOROW(v),s)),s,e)))),2)
✅ =FILTER(A2:A10,MAP(A2:A10,LAMBDA(s,LET(v,{"a";"e";"i";"o";"u"},COUNT(FIND(v&" "&TOROW(v),s))))))
Excel solution 4 for Vowel End and Start Words, proposed by محمد حلمي: 
=FILTER(A2:A10,MAP(A2:A10,LAMBDA(a,LET(
r,{"a";"o";"u";"i";"e"},
c,TEXTSPLIT(a," "),IFERROR(SUM(-(TOROW(IF(RIGHT(DROP(c,,-1))=r,
LEFT(DROP(c,,1)),1/0),2)=r)),)))))
Excel solution 5 for Vowel End and Start Words, proposed by 🇰🇷 Taeyong Shin: 
=LET(t, {"a";"e";"i";"o";"u"}, FILTER(A2:A10, MAP(A2:A10, LAMBDA(m, SUM(COUNTIF(m, "*" & t & " " & TOROW(t) & "*"))))))
Excel solution 6 for Vowel End and Start Words, proposed by Kris Jaganah: 
=FILTER(A2:A10,MAP(A2:A10,LAMBDA(x,LET(b,TEXTSPLIT(x," "),c,RIGHT(b),d,HSTACK(DROP(LEFT(b),,1),""),e,{"a";"e";"i";"o";"u"},f,SEQUENCE(5,,,0),SUM((XLOOKUP(c,e,f,0)*XLOOKUP(d,e,f,0)))))))
Excel solution 7 for Vowel End and Start Words, proposed by Julian Poeltl: 
=FILTER(A2:A10,MAP(A2:A10,LAMBDA(S,LET(V,{"a","e","i","o","u"},SP,TEXTSPLIT(S," "),C,CONCAT(LEFT(SP,1)&RIGHT(SP,1)),L,LEN(C),K,LEFT(RIGHT(C,L-1),L-2),CO,TOCOL(V&TOROW(V)),SUM(--ISNUMBER(SEARCH(CO,K)))))))
Excel solution 8 for Vowel End and Start Words, proposed by Aditya Kumar Darak 🇮🇳: 
=LET(
 _d, A2:A10,
 _v, {"a", "e", "i", "o", "u"},
 _sqv, SEQUENCE(5, , 1, 0),
 _e, LAMBDA(x,
 LET(
 s, TEXTSPLIT(x, , " "),
 sq, SEQUENCE(ROWS(s) - 1),
 fw, INDEX(s, sq),
 sw, INDEX(s, sq + 1),
 c, OR(MMULT((RIGHT(fw) = _v) + (LEFT(sw) = _v), _sqv) = 2),
 c
 )
 ),
 _c, MAP(_d, _e),
 _r, FILTER(_d, _c),
 _r
)
Excel solution 9 for Vowel End and Start Words, proposed by Timothée BLIOT: 
=FILTER(A2:A10,MAP(A2:A10,LAMBDA(z,LET(A,LOWER(TEXTSPLIT(z,"")),B,{"a","e","i","o","u"},D,LAMBDA(x,--ISNUMBER(XMATCH(x,B))),E,MAP(LEFT(A),LAMBDA(x,D(x))),F,MAP(RIGHT(A),LAMBDA(x,D(x))),SUM(MAP(SEQUENCE(COLUMNS(A)-1),LAMBDA(x,INDEX(E,,x+1)*INDEX(F,,x))))>=1))))
Excel solution 10 for Vowel End and Start Words, proposed by Sunny Baggu: 
=FILTER(A2:A10,
MAP(A2:A10,LAMBDA(a, LET(_v,{"a";"e";"i";"o";"u"},
_n,DROP(UNIQUE(SEARCH(" ",a,SEQUENCE(LEN(a)))),-1),
_L,MID(a,_n-1,1),
_R,MID(a,_n+1,1),
ISNUMBER(TOCOL((XMATCH(_L,_v)>0)*(XMATCH(_R,_v)>0),3))))))
Excel solution 11 for Vowel End and Start Words, proposed by Bhavya Gupta: 
=FILTER(A2:A10,ISNUMBER(FIND("* *",REDUCE(A2:A10,{"a","e","i","o","u"},LAMBDA(a,b,SUBSTITUTE(a,b,"*"))))))
Excel solution 12 for Vowel End and Start Words, proposed by Md. Zohurul Islam: 
=LET(
    z,
    A2:A10,
    u,
    MAP(
        z,
        LAMBDA(
            p,
            LET(
                a,
                TEXTSPLIT(
                    p,
                    ,
                    " "
                ),
                b,
                TOCOL(
                    HSTACK(
                        DROP(
                            RIGHT(
                                a
                            ),
                            -1
                        ),
                        DROP(
                            LEFT(
                                a
                            ),
                            1
                        )
                    )
                ),
                c,
                VSTACK(
                    "a",
                    "e",
                    "i",
                    "o",
                    "u"
                ),
                d,
                ABS(
                    ISNUMBER(
                        XMATCH(
                            b,
                            c
                        )
                    )
                ),
                e,
                SCAN(
                    0,
                    d,
                    LAMBDA(
                        x,
                        y,
                        IF(
                            y>0,
                            x+y,
                            y
                        )
                    )
                ),
                f,
                IF(
                    MAX(
                        e
                    )>=2,
                    1,
                    0
                ),
                f
            )
        )
    ),
    v,
    FILTER(
        z,
        u>0
    ),
    v
)
Excel solution 13 for Vowel End and Start Words, proposed by Charles Roldan: 
=FILTER(A2:A10,BYROW(ISNUMBER(SEARCH(
TOROW({"a","e","i","o","u"}&" "&{"a";"e";"i";"o";"u"}),
A2:A10)),LAMBDA(x,OR(x))))
Excel solution 14 for Vowel End and Start Words, proposed by Jaroslaw Kujawa: 
=LET(y, aeiouy,         z, MAP(A2:A10,         LAMBDA(a,                  LET(b, SEQUENCE(, LEN(a)), c, MID(a, b, 1)= ,                  d, FILTER(b*c, b*c0),                  e, FIND(MID(a, d+{-1, 1}, 1), y),                  IF(SUM(1*ISNUMBER(BYCOL(e, LAMBDA(x, SUM(x))))), a, )))),                  FILTER(z, z))
Excel solution 15 for Vowel End and Start Words, proposed by Guillermo Arroyo: 
=LET(a,A2:A10,FILTER(a,MAP(a,LAMBDA(x,LET(s,SEQUENCE(99),v,{"a";"e";"i";"o";"u"},w,--(MID(x,s,1)=" ")*s,g,LAMBDA(i,j,OR(LOWER(MID(i,j,1))=v)),REDUCE(FALSE,TOCOL((w*w/w),3),LAMBDA(b,c,OR(b,AND(g(x,c-1),g(x,c+1)))))))),""))

&&&

Leave a Reply