Generate the list of words which contain at least 3 different vowels. Ex. Opportunity => contains o, u and i.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 622
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of List Words with Three Vowels with Power Query
Power Query solution 1 for List Words with Three Vowels, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
ToCol = Table.ToColumns(Source),
Transform = List.Transform(
ToCol,
each List.Select(
_,
(f) => List.Count(List.Intersect({Text.ToList(f ?? ""), {"a", "e", "i", "o", "u"}})) > 2
)
),
Return = Table.FromColumns(Transform, Table.ColumnNames(Source))
in
ReturnPower Query solution 2 for List Words with Three Vowels, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Lista = List.Transform(
Table.ToColumns(Source),
each
let
a = _,
b = List.RemoveNulls(a),
c = List.Transform(
b,
each
let
d = Text.Lower(_),
e = Text.Select(d, {"a", "e", "i", "o", "u"}),
f = List.Count(List.Distinct(Text.ToList(e)))
in
{_, f}
),
g = List.Select(c, each _{1} > 2),
h = List.Transform(g, each _{0})
in
h
),
Sol = Table.FromColumns(Lista, Table.ColumnNames(Source))
in
SolPower Query solution 3 for List Words with Three Vowels, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
f = (txt) =>
List.Sum(
List.Transform({"a", "e", "i", "o", "u"}, each Byte.From(Text.Contains(Text.Lower(txt), _)))
)
> 2,
Transform = List.Transform(Table.ToColumns(Source), each List.Select(_, f)),
Result = Table.FromColumns(Transform, Table.ColumnNames(Source))
in
ResultPower Query solution 4 for List Words with Three Vowels, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
LT = List.Transform,
a = LT(Table.ToColumns(S), each List.RemoveNulls(LT(_, Text.Lower))),
Fx = (x) =>
let
b = LT(
x,
each {_}
& {Text.Combine(List.Distinct(Text.ToList(Text.Select(_, {"a", "e", "i", "o", "u"}))))}
),
c = List.Select(b, each Text.Length(_{1}) >= 3),
d = LT(c, each Text.Proper(_{0}))
in
d,
Sol = Table.FromColumns(LT(a, each Fx(_)), Table.ColumnNames(S))
in
SolPower Query solution 5 for List Words with Three Vowels, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Vowels = {"a", "e", "i", "o", "u"},
Cols = List.Transform(Table.ToColumns(Source), each List.Select(_, F)),
F = each [
A = List.Distinct(Text.ToList(_)),
B = List.Intersect({A, Vowels}, Comparer.OrdinalIgnoreCase),
C = try List.Count(B) > 2 otherwise false
][C],
Res = Table.FromColumns(Cols, Table.ColumnNames(Source))
in
ResPower Query solution 6 for List Words with Three Vowels, proposed by Meganathan Elumalai:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Result = Table.FromColumns(
List.Transform(
Table.ColumnNames(Source),
(f) =>
List.Select(
List.RemoveNulls(Table.Column(Source, f)),
(x) =>
List.Count(
List.Intersect(
{Text.ToList(x), {"a", "e", "i", "o", "u"}},
Comparer.OrdinalIgnoreCase
)
)
> 2
)
),
Table.ColumnNames(Source)
)
in
ResultPower Query solution 7 for List Words with Three Vowels, proposed by Rafael González B.:
let
Source = Question_Table,
Fx_Vowels = (tx) =>
let
TS = Text.Select(tx, {"a", "e","i","o","u","A","E","I","O","U"}),
TTL = List.Distinct(Text.ToList(TS), Comparer.OrdinalIgnoreCase)
in
List.Count(TTL) >= 3,
Result =
let
a = Table.ToColumns(Source),
b = List.Transform(a, each List.RemoveNulls(_)),
c = List.Transform(b, each List.Select(_, each Fx_Vowels(_))),
d = Table.FromColumns(c, Table.ColumnNames(Source))
in
d
in
Result
🧙 🧙 🧙
Power Query solution 8 for List Words with Three Vowels, proposed by CA Raghunath Gundi:
let
Vowels = {"a", "e", "i", "o", "u"},
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Unpivot = Table.Unpivot(Source, {"List1", "List2", "List3", "List4"}, "List", "Name"),
ToList = Table.AddColumn(Unpivot, "Custom", each Text.ToList([Name])),
Count = Table.AddColumn(ToList, "A", each List.Count(List.Intersect({[Custom], Vowels}))),
Custom1 = Table.SelectRows(Count, each Number.From([A]) > 2)[[List], [Name]],
#"Pivoted Column" = Table.Pivot(
Custom1,
List.Distinct(Custom1[List]),
"List",
"Name",
each List.Transform(_, Text.From)
),
#"Added Custom" = Table.AddColumn(
#"Pivoted Column",
"Answer",
each Table.FromColumns({[List1], [List2], [List3], [List4]}, Table.ColumnNames(Source))
)[Answer]{0}
in
#"Added Custom"Power Query solution 10 for List Words with Three Vowels, proposed by Alexandre Garcia:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = List.Transform,
C = (x) =>
List.Select(
x,
(x) => List.Sum(B(Text.ToList("aeiou"), each Byte.From(Text.Contains(Text.Lower(x), _)))) > 2
),
D = Table.FromColumns(B(Table.ToColumns(A), C), Value.Type(A))
in
DPower Query solution 11 for List Words with Three Vowels, proposed by Mihai Radu O:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
s = [
v = {"a", "e", "i", "o", "u"},
a = Table.ToColumns(Source),
b = List.Transform(
a,
(x) =>
List.Select(
List.RemoveNulls(x),
(y) => List.Count(List.Distinct(Text.ToList(Text.Select(Text.Lower(y), v)))) >= 3
)
),
c = Table.FromColumns(b, Table.ColumnNames(Source))
][c]
in
sSolving the challenge of List Words with Three Vowels with Excel
Excel solution 1 for List Words with Three Vowels, proposed by Bo Rydobon 🇹🇭:
=DROP(
REDUCE(
0,
A3:D3,
LAMBDA(
a,
v,
LET(
w,
TAKE(
D16:v,
,
1
),
IFNA(
HSTACK(
a,
FILTER(
w,
BYROW(
SEARCH(
{"a",
"e",
"i",
"o",
"u"},
w
),
COUNT
)>2,
""
)
),
""
)
)
)
),
,
1
)Excel solution 2 for List Words with Three Vowels, proposed by Rick Rothstein:
=IFNA(
DROP(
REDUCE(
"",
SEQUENCE(
4
),
LAMBDA(
a,
c,
HSTACK(
a,
TOCOL(
CHOOSECOLS(
IF(
MAP(
A3:D16,
LAMBDA(
x,
SUM(
COUNTIF(
x,
"*"&{"a",
"e",
"i",
"o",
"u"}&"*"
)
)
)
)>2,
A3:D16,
z
),
c
),
3
)
)
)
),
,
1
),
""
)Excel solution 3 for List Words with Three Vowels, proposed by 🇰🇷 Taeyong Shin:
=TRANSPOSE(
TEXTSPLIT(
CONCAT(
TRANSPOSE(
VSTACK(
MAP(
A3:D16,
LAMBDA(
x,
REPT(
x&1,
COUNT(
SEARCH(
{"a",
"e",
"i",
"o",
"u"},
x
)
)>2
)
)
),
N(
+A2:D2
)
)
)
),
1,
0,
1,
,
""
)
)Excel solution 4 for List Words with Three Vowels, proposed by Kris Jaganah:
=VSTACK(A2:D2,IFNA(DROP(REDUCE("",SEQUENCE(COLUMNS(A2:D2)),LAMBDA(v,w,HSTACK(v,LET(p,INDEX(MAP(A3:D16,LAMBDA(x,IF(ROWS(TOCOL(XMATCH({"a","e","i","o","u"},UNIQUE(REGEXEXTRACT(x,"[A-z+]",1),1)),3))>2,x,zz))),,w),FILTER(p,-ISERR(p)=0))))),,1),""))Excel solution 5 for List Words with Three Vowels, proposed by Timothée BLIOT:
=IFNA(DROP(REDUCE("",ROW(1:4),LAMBDA(w,v,HSTACK(w,TOCOL(MAP(INDEX(A3:D16,,v),LAMBDA(x,IF(COUNT(FIND({"a","e","i","o","u"},x))>=3,x,1/0))),3)))),,1),"")Excel solution 6 for List Words with Three Vowels, proposed by Hussein SATOUR:
=IFNA(DROP(REDUCE("",{1,2,3,4},LAMBDA(y,z,HSTACK(y,FILTER(INDEX(A3:D16,,z),MAP(INDEX(A3:D16,,z),LAMBDA(x,COUNTA(UNIQUE(REGEXEXTRACT(x,"[aeiou]",1,1),1))))>2)))),,1),"")Excel solution 7 for List Words with Three Vowels, proposed by Oscar Mendez Roca Farell:
=IFNA(
DROP(
REDUCE(
0,
A2:D2,
LAMBDA(
i,
x,
LET(
f,
TAKE(
x:D16,
,
1
),
HSTACK(
i,
TOCOL(
IFS(
BYROW(
FIND(
{"a",
"e",
"i",
"o",
"u"},
f
),
COUNT
)>2,
f
),
2
)
)
)
)
),
,
1
),
""
)Excel solution 8 for List Words with Three Vowels, proposed by Duy Tùng:
=DROP(
REDUCE(
0,
ROW(
1:4
),
LAMBDA(
x,
y,
LET(
a,
INDEX(
A3:D16,
,
y
),
IFNA(
HSTACK(
x,
VSTACK(
"List"&y,
FILTER(
a,
BYROW(
FIND(
{"a",
"e",
"u",
"i",
"o"},
a
),
COUNT
)>2
)
)
),
""
)
)
)
),
,
1
)Excel solution 9 for List Words with Three Vowels, proposed by Sunny Baggu:
=LET(
_a, IF(
IFERROR(
MAP(
A3:D16,
LAMBDA(a,
ROWS(TOCOL(SEARCH({"a"; "e"; "i"; "o"; "u"}, a), 3))
)
) >= 3,
0
) + 0,
A3:D16,
1 / x
),
VSTACK(
A2:D2,
IFNA(
DROP(
REDUCE(
"🌼🌻🌷",
SEQUENCE(COLUMNS(A3:D16)),
LAMBDA(x, y,
HSTACK(x, LET(_c, INDEX(_a, , y), TOCOL(_c, 3)))
)
),
,
1
),
""
)
)
)Excel solution 10 for List Words with Three Vowels, proposed by LEONARD OCHEA 🇷🇴:
=LET(
i,
A3:D16,
d,
IFERROR(
MAP(
i,
LAMBDA(
a,
IFS(
ROWS(
UNIQUE(
TOCOL(
FIND(
{"a";"e";"i";"o";"u"},
a
),
3
)
)
)>2,
1
)
)
),
0
),
s,
SEQUENCE(
ROWS(
d
)
),
c,
MMULT(
N(
s>=TOROW(
s
)
),
d
)*d,
F,
LAMBDA(
x,
TOCOL(
IFS(
d,
x
),
3
)
),
DROP(
PIVOTBY(
F(
c
),
F(
A2:D2
),
F(
i
),&
SINGLE,
,
0,
,
0
),
,
1
)
)Excel solution 11 for List Words with Three Vowels, proposed by Md. Zohurul Islam:
=LET(
a,
A3:D16,
b,
A2:D2,
IFNA(
VSTACK(
b,
DROP(
REDUCE(
"",
b,
LAMBDA(
z,
y,
HSTACK(
z,
FILTER(
FILTER(
a,
b=y
),
FILTER(
MAP(
a,
LAMBDA(
x,
IF(
COUNT(
XMATCH(
UNIQUE(
MID(
LOWER(
x
),
SEQUENCE(
LEN(
x
)
),
1
)
),
VSTACK(
"a",
"e",
"I",
"o",
"u"
)
)
)>2,
1,
0
)
)
),
b=y
)>0
)
)
)
),
,
1
)
),
""
)
)Excel solution 12 for List Words with Three Vowels, proposed by Md. Zohurul Islam:
=LET(
hdr,
A2:D2,
data,
A3:D16,
vwl,
VSTACK(
"a",
"e",
"i",
"o",
"u"
),
u,
MAP(
data,
LAMBDA(
x,
LET(
a,
UNIQUE(
MID(
LOWER(
x
),
SEQUENCE(
LEN(
x
)
),
1
)
),
b,
COUNT(
XMATCH(
a,
vwl
)
),
d,
IF(
b>2,
1,
0
),
d
)
)
),
v,
IFNA(
DROP(
REDUCE(
"",
hdr,
LAMBDA(
p,
q,
LET(
e,
FILTER(
data,
hdr=q
),
f,
FILTER(
u,
hdr=q
),
g,
FILTER(
e,
f=1
),
h,
HSTACK(
p,
g
),
h
)
)
),
,
1
),
""
),
w,
VSTACK(
hdr,
v
),
w
)Excel solution 13 for List Words with Three Vowels, proposed by Hamidi Hamid:
=LET(x,MAP(A3:D16,LAMBDA(a,BYCOL(IF(SUM(ISNUMBER(UNIQUE(SEARCH(MID(a,SEQUENCE(LEN(a)),1),"aeiou")))*1)>=3,a,"_z"),LAMBDA(a,SORT(a))))),z,DROP(IFERROR(TRANSPOSE(TEXTSPLIT(CONCAT("/"&BYCOL(x,LAMBDA(a,ARRAYTOTEXT(FILTER(a,a<>"_z"))))),",","/")),""),,1),VSTACK(A2:D2,TRIM(z)))Excel solution 14 for List Words with Three Vowels, proposed by Asheesh Pahwa:
=LET(
m,
MAP(
A3:D16,
LAMBDA(
x,
SUM(
--ISNUMBER(
XMATCH(
UNIQUE(
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)
),
{"a";"e";"i";"o";"u"}
)
)
)
)
),
I,
IF(
N(
m>=3
),
A3:D16,
""
),
DROP(
IFNA(
REDUCE(
"",
SEQUENCE(
4
),
LAMBDA(
x,
y,
HSTACK(
x,
LET(
In,
INDEX(
I,
,
y
),
FILTER(
In,
In<>""
)
)
)
)
),
""
),
,
1
)
)Excel solution 15 for List Words with Three Vowels, proposed by Asheesh Pahwa:
=DROP(IFNA(REDUCE("",SEQUENCE(4),LAMBDA(x,y,HSTACK(x,LET(I,INDEX(A3:D16,,y),t,TOCOL(I,3),m,MAP(t,LAMBDA(v,SUM(N(ISNUMBER(XMATCH(UNIQUE(MID(v,SEQUENCE(LEN(v)),1)),{"a";"e";"i";"o";"u"}))))))>2,
FILTER(t,m))))),""),,1)Excel solution 16 for List Words with Three Vowels, proposed by ferhat CK:
=LET(
q,
LAMBDA(
i,
j,
LET(
r,
REDUCE(
j,
i,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
a,
TEXTSPLIT(
"A;E;I;O;U;a;e;i;o;u",
,
";"
),
b,
MID(
y,
SEQUENCE(
LEN(
y
)
),
1
),
c,
ROWS(
UNIQUE(
TOCOL(
XMATCH(
a,
b
),
2
)
)
),
IF(
c>2,
y,
0
)
)
)
)
),
FILTER(
r,
r>0
)
)
),
IFNA(
HSTACK(
q(
A3:A9,
A2
),
q(
B3:B13,
B2
),
q(
C3:C7,
C2
),
q(
D3:D16,
D2
)
),
""
)
)Excel solution 17 for List Words with Three Vowels, proposed by Meganathan Elumalai:
=IFNA(
DROP(
REDUCE(
"",
BYCOL(
A3:D16,
LAMBDA(
a,
ARRAYTOTEXT(
FILTER(
a,
IFERROR(
BYROW(
a,
LAMBDA(
x,
ROWS(
UNIQUE(
TOCOL(
SEARCH(
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
"aeiou"
),
3
)
)
)>2
)
),
)
)
)
)
),
LAMBDA(
p,
q,
HSTACK(
p,
TEXTSPLIT(
q,
,
", "
)
)
)
),
,
1
),
""
)Excel solution 18 for List Words with Three Vowels, proposed by Cary Ballard, DML:
=LET(
n,
A3:D16,
c,
COLUMNS(
n
),
DROP(
REDUCE(
n,
SEQUENCE(
c
),
LAMBDA(
a,
v,
LET(
cc,
INDEX(
a,
,
v
),
IFNA(
HSTACK(
a,
FILTER(
cc,
MAP(
cc,
LAMBDA(
m,
COUNT(
SEARCH(
{"a",
"e",
"i",
"o",
"u"},
m
)
)
)
) > 2
)
),
""
)
)
)
),
,
c
)
)Solving the challenge of List Words with Three Vowels with Python
Python solution 1 for List Words with Three Vowels, proposed by Konrad Gryczan, PhD:
import pandas as pd
import numpy as np
path = "622 At least 3 different vowels.xlsx"
input = pd.read_excel(path, usecols="A:D", skiprows=1, nrows=15)
test = pd.read_excel(path, usecols="F:I", skiprows=1, nrows=5).rename(columns=lambda x: x.split('.')[0])
def count_unique_vowels(word):
vowels = set('aeiou')
return len(set(word.lower()) & vowels)
input_long = input.melt(var_name='list', value_name='word')
filtered = input_long[input_long['word'].astype(str).apply(lambda x: count_unique_vowels(x) >= 3)]
result = filtered.assign(rn=filtered.groupby('list').cumcount() + 1).pivot(index='rn', columns='list', values='word').reset_index(drop=True)
result.columns.name = None
result = result[['List1', 'List2', 'List3', 'List4']]
print(result.equals(test)) # True
Python solution 2 for List Words with Three Vowels, proposed by Abdallah Ally:
import pandas as pd
# Create a function to filter results
cond = sum(v in text.lower() for v in 'aeiou' if pd.notna(text))
return cond > 2
file_path = 'Excel_Challenge_622 - At least 3 different vowels.xlsx'
df = pd.read_excel(io=file_path, usecols='A:D', skiprows=1)
# Perform data manipulation
columns = df.columns
dfs = [
pd.DataFrame(
) for col in columns
]
df = pd.concat(dfs, axis=1, ignore_index=True).fillna('')
df.columns = columns
df
Solving the challenge of List Words with Three Vowels with Python in Excel
Python in Excel solution 1 for List Words with Three Vowels, proposed by Alejandro Campos:
df = xl("A2:D16", headers=True)
has_three_vowels = lambda w: w and len(set(c for c in w.lower() if c in 'aeiou')) >= 3
filtered_data = {k: [w for w in v if has_three_vowels(w)] for k, v in df.items()}
max_len = max(map(len, filtered_data.values()))
padded_data = {k: v + [''] * (max_len - len(v)) for k, v in filtered_data.items()}
filtered_df = pd.DataFrame(padded_data)
Solving the challenge of List Words with Three Vowels with R
R solution 1 for List Words with Three Vowels, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/622 At least 3 different vowels.xlsx"
input = read_excel(path, range = "A2:D16")
test = read_excel(path, range = "F2:I7")
result = input %>%
pivot_longer(everything(), names_to = "list", values_to = "word") %>%
filter(map_int(str_extract_all(str_to_lower(word), "[aeiou]"), ~length(unique(.x))) >= 3) %>%
mutate(rn = row_number(), .by = list) %>%
pivot_wider(names_from = list, values_from = word) %>%
select(List1, List2, List3, List4)
all.equal(result, test)
#> [1] TRUE
&&