Sort the consonants in given sentences while keeping the vowels wherever they are. Ex. otter => ortet otter pond => odnep rott
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 576
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Sort Consonants Keep Vowels with Power Query
Power Query solution 1 for Sort Consonants Keep Vowels, proposed by Omid Motamedisedeh:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Vo = Text.ToList("ieauo "),
#"Added Custom" = Table.AddColumn(
Source,
"Awnser",
each [
a = Text.ToList([Sentences]),
b = List.Sort(List.RemoveItems(a, Vo)),
c = Text.Combine(
List.Generate(
() => 0,
each _ < List.Count(a),
each _ + 1,
each
if List.Contains(Vo, a{_}) then
a{_}
else
b{List.Count(List.RemoveItems(List.FirstN(a, _ + 1), Vo)) - 1}
)
)
][c]
)
in
#"Added Custom"Power Query solution 2 for Sort Consonants Keep Vowels, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.AddColumn(
A,
"Answer Expected",
each
let
a = Text.ToList([Sentences]),
b = {"a", "e", "i", "o", "u", " "},
c = (v) =>
List.TransformMany(v, each List.PositionOf(a, _, Occurrence.All), (x, y) => {x, y}),
d = List.Zip(c(List.RemoveMatchingItems({"a" .. "z"}, b))),
e = List.Zip({d{0}, List.Sort(d{1})}),
f = Text.Combine(List.Zip(List.Sort(List.Combine({e, c(b)}), {each _{1}})){0})
in
f
)
in
BPower Query solution 3 for Sort Consonants Keep Vowels, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
Source,
"Answer",
each
let
a = Text.ToList([Sentences]),
b = List.Positions(a),
c = List.Zip({a, b}),
d = List.Select(c, each List.Contains({"a", "e", "i", "o", "u", " "}, _{0})),
e = List.RemoveMatchingItems(c, d),
f = List.Zip({List.Sort(List.Transform(e, each _{0})), List.Transform(e, each _{1})}),
g = Text.Combine(List.Transform(List.Sort(d & f, each _{1}), each _{0}))
in
g
)
in
SolPower Query solution 4 for Sort Consonants Keep Vowels, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
S,
"Answer Expected",
each
let
a = Text.ToList([Sentences]),
b = List.Zip({List.Positions(a), a}),
c = List.Select(b, each List.ContainsAny(_, {"a", "e", "i", "o", "u", " "})),
d = List.Difference(b, c),
e = List.Transform(d, each _{0}),
f = List.Sort(List.Transform(d, each _{1})),
g = Table.FromRows(c & List.Zip({e, f})),
h = Text.Combine(Table.Sort(g, {"Column1", 0})[Column2])
in
h
)
in
SolPower Query solution 5 for Sort Consonants Keep Vowels, proposed by Alexandre Garcia:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = {"a", "e", "i", "o", "u", " "},
C = (x, y) =>
[
a = Table.FromList(x, each {_}, {"v"}),
b = Table.Group(a, "v", {"w", each [v]}, 0, (x, y) => Byte.From(not List.Contains(B, y))),
c =
if List.Contains(B, b[v]{0}) then
Table.AddIndexColumn(b, "x", - 1, 1)
else
Table.AddIndexColumn(b, "x"),
d = Table.AddColumn(c, "y", each try y{[x]} otherwise null),
e = Text.Combine(
Table.AddColumn(
d,
"z",
each Text.Combine(List.Transform(List.Zip({[w], {[y]}}), each _{1} ?? _{0}))
)[z]
)
][e],
D = Table.AddColumn(
A,
"Answer",
each
let
x = Text.ToList([Sentences]),
y = {x, List.Sort(List.RemoveItems(x, B))}
in
C(y{0}, y{1})
)
in
DPower Query solution 6 for Sort Consonants Keep Vowels, proposed by Venkata Rajesh:
let
Source = Data,
Result = Table.AddColumn(
Source,
"Expected",
each [
v_s = {"a", "e", "i", "o", "u", " "},
c_sort = List.Sort(Text.ToList(Text.Remove([Sentences], v_s))),
v_s_p = Text.PositionOfAny([Sentences], v_s, Occurrence.All),
c_p = List.RemoveMatchingItems({0 .. Text.Length([Sentences]) - 1}, v_s_p),
result = List.Accumulate(
{0 .. List.Count(c_p) - 1},
[Sentences],
(state, current) => Text.ReplaceRange(state, c_p{current}, 1, c_sort{current})
)
][result]
)
in
ResultPower Query solution 7 for Sort Consonants Keep Vowels, proposed by Oleksandr Mynka:
let
src = Excel.CurrentWorkbook(){[Name = "input"]}[Content],
fx = (txt) =>
[
a = List.Transform(
Text.ToList(txt),
(i) =>
if List.Contains({"a", "e", "i", "o", "u", " "}, Text.Lower(i)) then
{i, null}
else
{null, i}
),
b = List.Zip(a),
c = List.Sort(List.RemoveNulls(b{1})),
d = List.Accumulate(
b{0},
{c, {}},
(state, current) =>
if current = null then
{List.RemoveFirstN(state{0}, 1), state{1} & {List.First(state{0})}}
else
{state{0}, state{1} & {current}}
){1},
e = Text.Combine(d)
][e],
to = Table.AddColumn(src, "Result", (tbl) => fx(tbl[Column1]))
in
toSolving the challenge of Sort Consonants Keep Vowels with Excel
Excel solution 1 for Sort Consonants Keep Vowels, proposed by Bo Rydobon 🇹🇭:
=MAP(A2:A10,LAMBDA(a,LET(s,SEQUENCE(LEN(a)),m,MID(a,s,1),o,ISERR(FIND(m,"aeiou ")),CONCAT(SORTBY(SORTBY(m,IF(o,m,0)),SORTBY(s,o))))))Excel solution 2 for Sort Consonants Keep Vowels, proposed by Rick Rothstein:
=MAP(
A2:A10,
LAMBDA(
r,
LET(
m,
MID(
r,
SEQUENCE(
LEN(
r
)
),
1
),
t,
IF(
m=" ",
" ",
IF(
ISERR(
FIND(
m,
"aeiou"
)
),
".",
m
)
),
REDUCE(
CONCAT(
t
),
SORT(
FILTER(
m,
t="."
)
),
LAMBDA(
a,
x,
SUBSTITUTE(
a,
".",
x,
1
)
)
)
)
)
)Excel solution 3 for Sort Consonants Keep Vowels, proposed by John V.:
=MAP(
A2:A10,
LAMBDA(
x,
LET(
w,
MID(
x,
ROW(
1:50
),
1
),
p,
ISERR(
FIND(
w,
"aeiou "
)
),
CONCAT(
IF(
p,
INDEX(
SORT(
IF(
p,
w
)
),
SCAN(
,
p,
SUM
)
),
w
)
)
)
)
)Excel solution 4 for Sort Consonants Keep Vowels, proposed by Kris Jaganah:
=MAP(A2:A10,LAMBDA(x,LET(a,SEQUENCE(LEN(x)),b,MID(x,a,1),c,{"a";"e";"i";"o";"u";" "},d,XLOOKUP(b,c,c,1),e,TOCOL(a*d,3),f,SORT(XLOOKUP(e,a,b)),CONCAT(IFNA(XLOOKUP(a,e,f),b)))))Excel solution 5 for Sort Consonants Keep Vowels, proposed by Julian Poeltl:
=MAP(A2:A10,LAMBDA(S,LET(V,"aeiou",SP,MID(S,SEQUENCE(LEN(S)),1),N,(SP<>" ")*NOT(ISNUMBER(SEARCH(SP,V))),So,SORT(FILTER(SP,N)),Sc,SCAN(0,N,SUM),CONCAT(IF(N,INDEX(So,Sc),SP)))))Excel solution 6 for Sort Consonants Keep Vowels, proposed by Timothée BLIOT:
=MAP(A2:A10,LAMBDA(z, LET(A,LEN(z), B,SEQUENCE(A), C,MID(z,B,1), E,NOT(ISNUMBER(XMATCH(C,{"a";"e";"i";"o";"u";" "}))), CONCAT(IF(E, INDEX(SORT(FILTER(C,E)),SCAN(0,--E,SUM)), C)))))Excel solution 7 for Sort Consonants Keep Vowels, proposed by Hussein SATOUR:
=MAP(A2:A10,LAMBDA(x,LET(F,FILTER,V,VSTACK,a,SEQUENCE(LEN(x)),b,MID(x,a,1),c,"aeiou ",n,ISNUMBER(FIND(b,c)),I,ISERR(FIND(b,c)),CONCAT(XLOOKUP(a,V(F(a,n),F(a,I)),V(F(b,n),SORT(F(b,I))))))))Excel solution 8 for Sort Consonants Keep Vowels, proposed by Sunny Baggu:
=MAP(
A2:A10,
LAMBDA(t,
LET(
_s,
SEQUENCE(
LEN(
t
)
),
_m,
MID(
t,
_s,
1
),
_x,
XMATCH(
_m,
{"a"; "e"; "i"; "o"; "u"}
),
_a,
FILTER(
_s,
ISNUMBER(
_x
)
),
_b,
FILTER(
_m,
ISNUMBER(
_x
)
),
_c,
FILTER(_s,
(_m <> " ") * ISNA(
_x
)),
_d,
SORT(FILTER(_m,
(_m <> " ") * ISNA(
_x
))),
_e,
CONCAT(
DROP(
SORT(
HSTACK(
VSTACK(
_a,
_c
),
VSTACK(
_b,
_d
)
),
1,
),
,
1
)
),
_f,
FILTER(
_s,
_m = " "
),
IFERROR(
REDUCE(
_e,
_f,
LAMBDA(
x,
y,
REPLACE(
x,
y,
0,
" "
)
)
),
_e
)
)
)
)Excel solution 9 for Sort Consonants Keep Vowels, proposed by LEONARD OCHEA 🇷🇴:
=MAP(
A2:A10,
LAMBDA(
x,
LET(
e,
REGEXEXTRACT(
x,
".",
1
),
b,
ISNUMBER(
FIND(
e,
"aeiou "
)
)-1,
p,
SCAN(
,
b,
SUM
)*b,
CONCAT(
IF(
p,
INDEX(
SORT(
FILTER(
e,
b
),
,
,
1
),
p
),
e
)
)
)
)
)Excel solution 10 for Sort Consonants Keep Vowels, proposed by Md. Zohurul Islam:
=MAP(A2:A10,LAMBDA(p,LET(
a,SEQUENCE(LEN(p)),
b,MID(p,a,1),
c,VSTACK("a","e","i","o","u"," "),
d,IFNA(XMATCH(b,c),0),
x,FILTER(a,d=0),
y,SORT(FILTER(b,d=0)),
z,HSTACK(x,y),
e,FILTER(HSTACK(a,b),d>0),
rng,SORT(VSTACK(z,e),1,1),
f,DROP(rng,,1),
g,CONCAT(f),g)))Excel solution 11 for Sort Consonants Keep Vowels, proposed by Jaroslaw Kujawa:
=BYROW(
A2:A10;
LAMBDA(
x;
LET(
y;
{"a";
"e";
"i";
"o";
"u";
" "};
z;
SEQUENCE(
LEN(
x
)
);
a;
MID(
x;
z;
1
);
b;
SORT(
FILTER(
HSTACK(
a;
z
);
ISNA(
XMATCH(
a;
y;
0
)
)
)
);
b_s;
SEQUENCE(
ROWS(
b
)
);
e;
HSTACK(
a;
z;
b_s;
b
);
f;
HSTACK(
e;
IFNA(
XLOOKUP(
z;
SMALL(
TAKE(
CHOOSECOLS(
e;
5
);
ROWS(
b
)
);
b_s
);
TAKE(
CHOOSECOLS(
e;
4
);
ROWS(
b
)
)
);
a
)
);
CONCAT(
TAKE(
f;
;
-1
)
)
)
)
)Excel solution 12 for Sort Consonants Keep Vowels, proposed by Nicolas Micot:
=LET(_chars;STXT(A2;SEQUENCE(NBCAR(A2));1);
_isConsonants;SIERREUR(CHERCHE(_chars;"bcdfghjklmnpqrstvwxyz");-1)<>-1;
_num;SCAN(0;_isConsonants;LAMBDA(l_valeur;l_test;SI(l_test;l_valeur+1;l_valeur)));
_sortedConsonants;TRIER(FILTRE(_chars;_isConsonants);;1);
CONCAT(SI(_isConsonants;INDEX(_sortedConsonants;_num);_chars)))Excel solution 13 for Sort Consonants Keep Vowels, proposed by Songglod P.:
=MAP(
A2:A10,
LAMBDA(
s,
LET(
i,
SEQUENCE(
LEN(
s
)
),
t,
MID(
s,
i,
1
),
c,
ISERR(
FIND(
t,
"aeiou "
)
),
srt,
LAMBDA(
x,
SORT(
FILTER(
x,
c
)
)
),
REDUCE(
s,
srt(
i
),
LAMBDA(
a,
v,
REPLACE(
a,
v,
1,
XLOOKUP(
v,
srt(
i
),
srt(
t
)
)
)
)
)
)
)
)Excel solution 14 for Sort Consonants Keep Vowels, proposed by Ben Warshaw:
=BYROW(A2:A10,
LAMBDA(z,
LET(
a,
TRANSPOSE(
MID(
z,
SEQUENCE(
LEN(
z
)
),
1
)
),
b,
TEXTSPLIT(
{"a",
"e",
"i",
"o",
"u"},
","
),
c,
LET(
a,
FILTER(
a,
ISERROR(
XMATCH(
a,
b
)
)
),
b,
SORTBY(
a,
a
),
FILTER(
b,
b<>" "
)
),
d,
LET(
a,
REDUCE(
0,
a,
LAMBDA(
st,
cur,
IF(
OR(
1-ISERROR(
XMATCH(
cur,
b
)
),
cur=" "
),
st&","&cur,
st&","&"0"
)
)
),
DROP(
TEXTSPLIT(
a,
","
),
,
1
)
),
e,
SCAN(
0,
d,
LAMBDA(
st,
cur,
IF(
cur="0",
st+1,
st
)
)
),
f,
INDEX(
c,
e
),
g,
IF((d="0")*(a<>" "),
f,
d),
SUBSTITUTE(
ARRAYTOTEXT(
g
),
", ",
""
))))Solving the challenge of Sort Consonants Keep Vowels with Python
Python solution 1 for Sort Consonants Keep Vowels, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "576 Sort only Consonants.xlsx"
input = pd.read_excel(path, usecols="A", nrows=10)
test = pd.read_excel(path, usecols="B", nrows=10)
def process_column(word):
col = list(word)
consonant_pos = [i for i, letter in enumerate(col) if letter.lower() in "bcdfghjklmnpqrstvwxyz"]
sorted_consonants = sorted([col[i] for i in consonant_pos])
for i, pos in enumerate(consonant_pos):
col[pos] = sorted_consonants[i]
return ''.join(col)
input['result'] = input.iloc[:, 0].apply(process_column)
print(input["result"].equals(test["Answer Expected"])) # True
Solving the challenge of Sort Consonants Keep Vowels with Python in Excel
Python in Excel solution 1 for Sort Consonants Keep Vowels, proposed by Alejandro Campos:
def sort_consonants(sentence):
vowels = "aeiouAEIOU"
consonants = sorted([char for char in sentence if char not in vowels and char.isalpha()])
result, consonant_index = [], 0
for char in sentence:
result.append(char if char in vowels or not char.isalpha() else consonants[consonant_index])
consonant_index += 1 if char not in vowels and char.isalpha() else 0
return ''.join(result)
sentences = xl("A2:A10")[0]
df = pd.DataFrame({
'Original Sentence': sentences,
'Sorted Consonants Sentence': [sort_consonants(sentence) for sentence in sentences]
})
df
Python in Excel solution 2 for Sort Consonants Keep Vowels, proposed by Anshu Bantra:
def word_to_dict(string: str) -> dict:
return {key: char for key, char in enumerate(string, 1)}
def sort_consonants(sentence: str) -> str:
vowels = [*' aeiou']
char_dict = word_to_dict(sentence)
others_dict = {key: value for key, value in char_dict.items() if value.lower() not in vowels}
sorted_dict = sorted(char_dict[key] for key in others_dict)
for key, value in zip(others_dict, sorted_dict):
char_dict[key] = value
return ''.join(char_dict.values())
df = xl("A1:A10", headers=True)
df['Answer'] = df['Sentences'].apply(sort_consonants)
df
Solving the challenge of Sort Consonants Keep Vowels with R
R solution 1 for Sort Consonants Keep Vowels, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/576 Sort only Consonants.xlsx"
input = read_excel(path, range = "A1:A10")
test = read_excel(path, range = "B1:B10")
process_column <- function(word) {
col <- strsplit(word, "")[[1]]
consonant_pos <- grep("[b-df-hj-np-tv-z]", col)
sorted_consonants <- sort(col[consonant_pos])
col[consonant_pos] <- sorted_consonants
paste(col, collapse = "")
}
result = input %>%
mutate(result = map_chr(Sentences, process_column))
all.equal(result$result, test$`Answer Expected`, check.attributes = FALSE)
# [1] TRUE
&&