Find the count of words in columns A through C if the words have sorted alphabets. Ex. fiitu = Alphabets are in sorted order faitu = Alphabets are not in sorted order as a comes after f
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 601
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Count Alphabetically Sorted Words with Power Query
Power Query solution 1 for Count Alphabetically Sorted Words, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.ToColumns(A),
C = Record.FromList(
List.Transform(
List.Positions(B),
(y) =>
List.Sum(
List.Transform(
B{y},
each
let
a = Text.ToList(_)
in
Number.From(List.Sort(a) = a)
)
)
),
Table.ColumnNames(A)
),
D = Table.UnpivotOtherColumns(Table.FromRecords({C}), {}, "Words", "Count")
in
DPower Query solution 2 for Count Alphabetically Sorted Words, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
ToCols = Table.ToColumns(Source),
Transform = List.Transform(
ToCols,
each List.Count(List.Select(_, (f) => f = Text.Combine(List.Sort(Text.ToList(f)))))
),
Return = Record.FromList(Transform, Table.ColumnNames(Source))
in
ReturnPower Query solution 3 for Count Alphabetically Sorted Words, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Count = List.Transform(
Table.ToColumns(Source),
each List.Count(List.Select(_, (x) => Text.ToList(x) = List.Sort(Text.ToList(x))))
),
Sol = Table.FromColumns({Table.ColumnNames(Source)} & {Count}, {"Word", "Count"})
in
SolPower Query solution 4 for Count Alphabetically Sorted Words, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
a = Table.ToColumns(S),
Fx = (x) =>
let
b = List.Transform(x, Text.ToList),
c = List.Count(List.Select(b, each List.Sort(_) = _))
in
c,
d = List.Transform(a, each Fx(_)),
Sol = Table.FromColumns({Table.ColumnNames(S), d}, {"Words", "Count"})
in
SolPower Query solution 5 for Count Alphabetically Sorted Words, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Columns = Table.ToColumns(Source),
Count = List.Transform(
Columns,
(x) =>
List.Sum(
List.Transform(
x,
(y) =>
let
Unsorted = Text.ToList(y),
Sorted = List.Sort(Unsorted)
in
if Unsorted = Sorted then 1 else 0
)
)
)
in
Table.FromColumns({Table.ColumnNames(Source), Count}, {"Words", "Count"})Power Query solution 6 for Count Alphabetically Sorted Words, proposed by Mihai Radu O:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
r = [
a = Table.ToColumns(Source),
hd = Table.ColumnNames(Source),
b = List.Transform(
a,
(x) =>
List.Sum(List.Transform(x, (y) => Byte.From(Text.Combine(List.Sort(Text.ToList(y))) = y)))
),
c = Table.FromColumns({hd, b}, {"Words", "Count"})
][c]
in
rSolving the challenge of Count Alphabetically Sorted Words with Excel
Excel solution 1 for Count Alphabetically Sorted Words, proposed by Bo Rydobon 🇹🇭:
=TRANSPOSE(
VSTACK(
A2:C2,
BYCOL(
--MAP(
A3:C11,
LAMBDA(
a,
a=CONCAT(
SORT(
MID(
a,
SEQUENCE(
20
),
1
)
)
)
)
),
SUM
)
)
)Excel solution 2 for Count Alphabetically Sorted Words, proposed by Rick Rothstein:
=TRANSPOSE(
VSTACK(
A2:C2,
BYCOL(
A3:C11,
LAMBDA(
c,
SUM(
0+MAP(
c,
LAMBDA(
r,
CONCAT(
SORT(
MID(
r,
SEQUENCE(
LEN(
r
)
),
1
)
)
)=r
)
)
)
)
)
)
)Excel solution 3 for Count Alphabetically Sorted Words, proposed by John V.:
=TRANSPOSE(
VSTACK(
A2:C2,
BYCOL(
--MAP(
A3:C11,
LAMBDA(
x,
x=CONCAT(
SORT(
MID(
x,
ROW(
1:15
),
1
)
)
)
)
),
SUM
)
)
)Excel solution 4 for Count Alphabetically Sorted Words, proposed by Kris Jaganah:
=WRAPCOLS(HSTACK(A2:C2,BYCOL(A3:C11,LAMBDA(y,SUM(MAP(y,LAMBDA(x,LET(a,MID(x,SEQUENCE(LEN(x)),1),MIN(--(SORT(a)=a))))))))),3)Excel solution 5 for Count Alphabetically Sorted Words, proposed by Julian Poeltl:
=TRANSPOSE(VSTACK(A2:C2,BYCOL(--MAP(A3:C11,LAMBDA(A,CONCAT(SORT(MID(A,SEQUENCE(LEN(A)),1)))=A)),SUM)))Excel solution 6 for Count Alphabetically Sorted Words, proposed by Aditya Kumar Darak 🇮🇳:
=TRANSPOSE(VSTACK(A2:C2, BYCOL(--(MAP(A3:C11, LAMBDA(x, CONCAT(SORT(MID(x, SEQUENCE(LEN(x)), 1))))) = A3:C11), SUM)))Excel solution 7 for Count Alphabetically Sorted Words, proposed by Timothée BLIOT:
=HSTACK(
TOCOL(
A2:C2
),
TOCOL(
BYCOL(
A3:C11,
LAMBDA(
z,
SUM(
--MAP(
z,
LAMBDA(
y,
CONCAT(
SORT(
MID(
y,
SEQUENCE(
LEN(
y
)
),
1
)
)
)=y
)
)
)
)
)
)
)Excel solution 8 for Count Alphabetically Sorted Words, proposed by Duy Tùng:
=TRANSPOSE(
VSTACK(
A2:C2,
BYCOL(
N(
MAP(
A3:C11,
LAMBDA(
v,
v=CONCAT(
SORT(
MID(
v,
SEQUENCE(
LEN(
v
)
),
1
)
)
)
)
)
),
SUM
)
)
)Excel solution 9 for Count Alphabetically Sorted Words, proposed by Sunny Baggu:
=HSTACK(
TOCOL(A2:C2),
TOCOL(
BYCOL(
MAP(
A3:C11,
LAMBDA(w,
LET(
_m, CODE(MID(w, SEQUENCE(LEN(w)), 1)),
N(AND(DROP(_m, 1) >= DROP(_m, -1)))
)
)
),
LAMBDA(d, SUM(d))
)
)
)Excel solution 10 for Count Alphabetically Sorted Words, proposed by LEONARD OCHEA 🇷🇴:
=TRANSPOSE(
VSTACK(
A2:C2,
BYCOL(
N(
MAP(
A3:C11,
LAMBDA(
x,
x=CONCAT(
SORT(
REGEXEXTRACT(
x,
".",
1
),
,
,
1
)
)
)
)
),
SUM
)
)
)
=LET(
m,
TOCOL(
IFS(
MAP(
A3:C11,
LAMBDA(
x,
x=CONCAT(
SORT(
REGEXEXTRACT(
x,
".",
1
),
,
,
1
)
)
)
),
A2:C2
),
3
),
GROUPBY(
m,
m,
ROWS,
,
0
)
)Excel solution 11 for Count Alphabetically Sorted Words, proposed by Anshu Bantra:
=TRANSPOSE(
VSTACK(
A2:C2,
BYCOL(
MAP(
A3:C11,
LAMBDA(word_,
LET(
split_, MID(word_, SEQUENCE(LEN(word_)), 1),
sorted_, SORT(split_),
PRODUCT(--(split_ = sorted_))
)
)
),
SUM
)
)
)Excel solution 12 for Count Alphabetically Sorted Words, proposed by Md. Zohurul Islam:
=LET(
p,
A2:C2,
q,
A3:C11,
r,
SEQUENCE(
ROWS(
q
)
),
s,
HSTACK(
"Words",
"Count"
),
hdr,
TOCOL(
IFNA(
p,
r
)
),
wrds,
TOCOL(
q
),
data,
--MAP(
wrds,
LAMBDA(
x,
CONCAT(
SORT(
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)
)
)=x
)
),
u,
GROUPBY(
hdr,
data,
SUM,
0,
0
),
result,
VSTACK(
s,
u
),
result
)Excel solution 13 for Count Alphabetically Sorted Words, proposed by Hamidi Hamid:
=LET(f,
LAMBDA(plg,
LET(x,
MID(
plg,
SEQUENCE(
,
100
),
1
),
y,
IFERROR(
CODE(
DROP(
HSTACK(
x,
0
),
,
1
)
)-CODE(
x
),
0
),
g,
BYROW(y,
LAMBDA(a,
SUM((a<0)*1))),
MAX(SCAN(,
(g=0)*1,
SUM)))),
VSTACK(
f(
A3:A11
),
f(
B3:B11
),
f(
C3:C11
)
))Excel solution 14 for Count Alphabetically Sorted Words, proposed by Hamidi Hamid:
=LET(f,
LAMBDA(plg,
LET(x,
MID(
plg,
SEQUENCE(
,
100
),
1
),
y,
IFERROR(
CODE(
DROP(
HSTACK(
x,
0
),
,
1
)
)-CODE(
x
),
0
),
g,
BYROW(y,
LAMBDA(a,
SUM((a<0)*1))),
MAX(SCAN(,
(g=0)*1,
SUM)))),
TRANSPOSE(
BYCOL(
A3:C11,
LAMBDA(
a,
f(
a
)
)
)
))Excel solution 15 for Count Alphabetically Sorted Words, proposed by Asheesh Pahwa:
=HSTACK(VSTACK(E2,E3:E5),REDUCE("Count",SEQUENCE(3),LAMBDA(x,y,
VSTACK(x,LET(I,INDEX(A3:C11,,y),SUM(MAP(I,LAMBDA(a,LET(m,MID(a,SEQUENCE(LEN(a)),1),c,CODE(m),s,SORT(c),N(AND(s>=c)))))))))))Excel solution 16 for Count Alphabetically Sorted Words, proposed by ferhat CK:
=TOCOL(BYCOL(MAP(A3:C11,LAMBDA(x,LET(a,CODE(MID(x,SEQUENCE(LEN(x)),1)),IF(SUM(N(DROP(a,1)-DROP(a,-1)<0))=0,1,0)))),SUM))Excel solution 17 for Count Alphabetically Sorted Words, proposed by Jaroslaw Kujawa:
=TRANSPOSE(VSTACK(A2:C2;
BYCOL(MAP(A3:C11;
LAMBDA(x;
LET(sq;
SEQUENCE(
LEN(
x
)
);
m;
MID(
x;
sq;
1
);
mp;
INDEX(
m;
sq+1
);
1*(LEN(
x
)-1=SUM(IF(ISNUMBER(1*(m<=mp));
1*(m<=mp)))))));
SUM)))Excel solution 18 for Count Alphabetically Sorted Words, proposed by Albert Cid Cañigueral:
=HSTACK(TOCOL&(A2:C2),TOCOL(BYCOL(--MAP(A3:C11,LAMBDA(e,LET(b,REGEXEXTRACT(e,".",1),CONCAT(SORT(b,,,1))=e))),SUM)))Excel solution 19 for Count Alphabetically Sorted Words, proposed by Ankur Sharma:
=HSTACK(TOCOL(
A2:C2
),
TOCOL(
BYCOL(A3:C11,
LAMBDA(z,
SUM(MAP(z,
LAMBDA(y,
LET(a,
MID(
y,
SEQUENCE(
LEN(
y
)
),
1
),
b,
XMATCH(
a,
CHAR(
SEQUENCE(
26,
,
65
)
)
),
c,
DROP(
b,
-1
),
d,
DROP(
b,
1
),
--(SUM(--(d >= c)) = COUNT(
c
))))))))))Excel solution 20 for Count Alphabetically Sorted Words, proposed by Meganathan Elumalai:
=TRANSPOSE(
VSTACK(
A2:C2,
BYCOL(
A3:C11,
LAMBDA(
x,
SUM(
MAP(
x,
LAMBDA(
v,
1*AND(
MMULT(
CODE(
MID(
v,
SEQUENCE(
LEN(
v
)-1
)+{0,
1},
1
)
),
{-1;1}
)>=0
)
)
)
)
)
)
)
)Excel solution 21 for Count Alphabetically Sorted Words, proposed by JvdV -:
=TRANSPOSE(VSTACK(A2:C2,BYCOL(--REGEXTEST(A3:C11,CONCAT("^",CHAR(ROW(97:122))&"*","$")),SUM)))Excel solution 22 for Count Alphabetically Sorted Words, proposed by CA Raghunath Gundi:
=LET(
Words,
TRANSPOSE(
A2:C2
),
Words1,
SUM(
--BYROW(
A3:A11,
LAMBDA(
a,
CONCAT(
SORT(
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
)
)
)=a
)
)
),
Words2,
SUM(
--BYROW(
B3:B11,
LAMBDA(
a,
CONCAT(
SORT(
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
)
)
)=a
)
)
),
Words3,
SUM(
--BYROW(
C3:C11,
LAMBDA(
a,
CONCAT(
SORT(
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
)
)
)=a
)
)
),
HSTACK(
Words,
VSTACK(
Words1,
Words2,
Words3
)
)
)Excel solution 23 for Count Alphabetically Sorted Words, proposed by Milan Shrimali:
=WRAPROWS(
TOCOL(
BYCOL(
A2:C11,
LAMBDA(
U,
VSTACK(
CHOOSEROWS(
U,
1
),
COUNTIF(
BYROW(
U,
LAMBDA(
Y,
ARRAYFORMULA(
LET(
RNG,
ARRAYFORMULA(
CHAR(
SEQUENCE(
26,
1,
97,
1
)
)
),
SPLT,
ARRAYFORMULA(
MID(
Y,
SEQUENCE(
LEN(
Y
)
),
1
)
),
MTCH,
ARRAYFORMULA(
MATCH(
SPLT,
RNG,
0
)
),
TBL,
IFERROR(
HSTACK(
MTCH,
FILTER(
MTCH,
MTCH>CHOOSEROWS(
MTCH,
1
)
)
),
0
),
MNUS,
BYROW(
TBL,
LAMBDA(
X,
CHOOSECOLS(
X,
2
)-CHOOSECOLS(
X,
1
)
)
),
IF(
FILTER(
MNUS,
MNUS<0
)>0,
0,
1
)
)
)
)
),
1
)
)
)
),
0,
1
),
2
)Excel solution 24 for Count Alphabetically Sorted Words, proposed by Eric Baldwin:
=IF(
IFERROR(
MID(
word,
n,
1
)<=mid(
word,
n+1,
1
),
TRUE
),
1,
0
)
Then
=BYCOL(
A3:C11,
LAMBDA(
curr_col,
SUM(
BYROW(
curr_col,
LAMBDA(
word,
MIN(
ARRAYFORMULA(
MONO_ALPHA(
word,
SEQUENCE(
1,
LEN(
word
)-1
)
)
)
)
)
)
)
)
)Solving the challenge of Count Alphabetically Sorted Words with Python
Python solution 1 for Count Alphabetically Sorted Words, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "601 Count Sorted Words.xlsx"
input = pd.read_excel(path, usecols="A:C", skiprows=1, nrows=10)
test = pd.read_excel(path, usecols="E:F", skiprows=1, nrows=3).squeeze()
result = input.apply(lambda col: sum(list(x) == sorted(x) for x in col)).to_frame().reset_index()
result.columns = test.columns
print(test.equals(test))
Solving the challenge of Count Alphabetically Sorted Words with Python in Excel
Python in Excel solution 1 for Count Alphabetically Sorted Words, proposed by Alejandro Campos:
df = xl("A2:C11", headers=True)
sorted_counts_df = df.applymap(lambda w: w == ''.join(sorted(w))).sum()
.reset_index(name='Count').rename(columns={'index': 'Words'})
sorted_counts_df
Python in Excel solution 2 for Count Alphabetically Sorted Words, proposed by Aditya Kumar Darak 🇮🇳:
data = xl("A2:C11", headers=True)
def Check(word):
result = data.applymap(Check).sum()
result
Python in Excel solution 3 for Count Alphabetically Sorted Words, proposed by Anshu Bantra:
def check_order(word: str) -> bool:
df=xl("A2:C11", headers=True)
df.applymap(check_order).sum()
Python in Excel solution 4 for Count Alphabetically Sorted Words, proposed by Mey Tithveasna:
Python in excel
def check_word(word):
df=xl("A2:C11", headers=True)
check_result = df.applymap(check_word)
Solving the challenge of Count Alphabetically Sorted Words with R
R solution 1 for Count Alphabetically Sorted Words, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/601 Count Sorted Words.xlsx"
input = read_excel(path, range = "A2:C11")
test = read_excel(path, range = "E2:F5")
result = input %>%
summarize(across(everything(), ~ sum(map_lgl(str_split(.x, ""), ~ all(.x == sort(.x)))))) %>%
pivot_longer(everything(), names_to = "Words", values_to = "Count")
all.equal(result, test, check.attributes = FALSE)
#> [1] TRUE
&&