Extract the unique alphabets from the given strings and count their frequencies of occurrence. It should appear in the format alphabet:its count, next alphabet:its count….The result should be sorted on alphabets. Ex. cbacacbaa = a:4, b:2, c:3
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 399
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Count Unique Letters in String with Power Query
Power Query solution 1 for Count Unique Letters in String, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
res = Table.AddColumn(
Fonte,
"Personalizar",
each [
a = Text.ToList([String]),
b = Text.Combine(
List.Sort(
List.Transform(
List.Distinct(a),
each Text.Combine({_} & {Text.From(List.Count(List.Select(a, (x) => _ = x)))}, ":")
),
0
),
", "
)
][b]
)
in
resPower Query solution 2 for Count Unique Letters in String, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Fx = (x) =>
let
a = Table.FromColumns({Text.ToList(x)}),
b = Table.Group(a, {"Column1"}, {{"G", each List.Count(_)}}),
c = Table.Sort(b, {{"Column1", 0}}),
d = Table.CombineColumns(
Table.TransformColumnTypes(c, {{"G", type text}}),
{"Column1", "G"},
Combiner.CombineTextByDelimiter(":"),
"M"
),
e = Text.Combine(d[M], ", ")
in
e,
Sol = Table.AddColumn(S, "Answer Expected", each Fx([String]))
in
SolPower Query solution 3 for Count Unique Letters in String, proposed by Rafael González B.:
let
Source = Excel.CurrentWorkbook(){0}[Content],
Result = Table.AddColumn(Source, "Answer Expected", each
let
t = [String],
ttl = Text.ToList(t),
Letters = List.Sort(List.Distinct(ttl)),
LA = List.Accumulate(Letters,
{},
(s,c) =>
let
a = List.Select(ttl, each _ = c),
b = Text.From(List.Count(a)),
d = s & {a{0} & ":" & b}
in
d)
in
Text.Combine(LA, ", ")
)[[Answer Expected]]
in
Result
🧙♂️🧙♂️🧙♂️
Power Query solution 4 for Count Unique Letters in String, proposed by Luke Jarych:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Answer = Table.AddColumn(
Source,
"Answer Expected",
each
let
a = List.Distinct(List.Sort(Text.ToList([String]))),
b = Text.ToList([String]),
c = List.Transform(
a,
(x) =>
let
c0 = Text.From(x),
c1 = Text.Combine({c0, Text.From(List.Count(List.Select(b, each _ = x)))}, ":")
in
c1
),
d = Text.Combine(c, ", ")
in
d
)[[Answer Expected]]
in
AnswerPower Query solution 5 for Count Unique Letters in String, proposed by Mihai Radu O:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
sol = Table.AddColumn(
Source,
"r",
each
let
a = Text.ToList([String]),
b = List.Sort(List.Distinct(a)),
c = List.TransformMany(
b,
(x) => {List.Count(List.Select(a, each _ = x))},
(x, y) => x & ":" & Number.ToText(y)
),
d = Text.Combine(c, ", ")
in
d
)[[r]]
in
solPower Query solution 6 for Count Unique Letters in String, proposed by Tyler N.:
let a = Table.AddColumn(Table.Group(Table.FromList(List.Sort(Text.ToList([String]))),"Column1",{{"c", each Text.From(Table.RowCount(_))}}),"s",each Text.Combine({[Column1],":",[c]})) in Text.Combine(a[s],", "))
Power Query solution 7 for Count Unique Letters in String, proposed by Karunakaran S K P:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source, {{"String", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Char List", each Text.ToList([String])),
#"Expanded Char List" = Table.ExpandListColumn(#"Added Custom", "Char List"),
#"Grouped Rows" = Table.Group(
#"Expanded Char List",
{"String", "Char List"},
{{"Char Count", each Table.RowCount(_), Int64.Type}}
),
#"Added Custom1" = Table.AddColumn(
#"Grouped Rows",
"Custom",
each [Char List] & ":" & Text.From([Char Count])
),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1", {"Char List", "Char Count"}),
#"Grouped Rows1" = Table.Group(
#"Removed Columns",
{"String"},
{{"Expected Answer", each Text.Combine([Custom], ", "), type text}}
)
in
#"Grouped Rows1"Solving the challenge of Count Unique Letters in String with Excel
Excel solution 1 for Count Unique Letters in String, proposed by Rick Rothstein:
=MAP(
A2:A10,
LAMBDA(
a,
LET(
s,
CHAR(
SEQUENCE(
26
)+96
),
m,
MID(
a,
LEN(
a
),
1
),
c,
s&":"&LEN(
a
)-LEN(
SUBSTITUTE(
a,
s,
""
)
),
TEXTJOIN(
", ",
,
FILTER(
c,
RIGHT(
c
)<>"0"
)
)
)
)
)Excel solution 2 for Count Unique Letters in String, proposed by محمد حلمي:
=MAP(
A2:A10,
LAMBDA(
i,
LET(
d,
MID(
i,
SEQUENCE(
LEN(
i
)
),
1
),
ARRAYTOTEXT(
MAP(
SORT(
UNIQUE(
d
)
),
LAMBDA(
a,
a&":"&SUM(
N(
d=a
)
)
)
)
)
)
)
)Excel solution 3 for Count Unique Letters in String, proposed by محمد حلمي:
=MAP(
A2:A10,
LAMBDA(
a,
LET(
d,
SORT(
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
)
),
u,
UNIQUE(
d
),
ARRAYTOTEXT(
u&":"&XMATCH(
u,
d,
,
-1
)-XMATCH(
u,
d
)+1
)
)
)
)Excel solution 4 for Count Unique Letters in String, proposed by 🇰🇷 Taeyong Shin:
=MAP(
A2:A10,
LAMBDA(
x,
LET(
u,
UNIQUE(
SORT(
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)
)
),
ARRAYTOTEXT(
u&":"&LEN(
x
)-LEN(
SUBSTITUTE(
x,
u,
)
)
)
)
)
)Excel solution 5 for Count Unique Letters in String, proposed by Kris Jaganah:
=MAP(A2:A10,
LAMBDA(y,
LET(a,
MID(
y,
SEQUENCE(
LEN(
y
)
),
1
),
b,
SORT(
UNIQUE(
a
)
),
ARRAYTOTEXT(MAP(b,
LAMBDA(x,
x&":"&SUM(--(a=x))))))))Excel solution 6 for Count Unique Letters in String, proposed by Julian Poeltl:
=MAP(
A2:A10,
LAMBDA(
A,
LET(
Letters,
MID(
A,
SEQUENCE(
1,
LEN(
A
)
),
1
),
UNIQ,
SORT(
UNIQUE(
Letters,
TRUE
),
,
,
TRUE
),
Count,
LEN(
A
)-LEN(
SUBSTITUTE(
A,
UNIQ,
""
)
),
TEXTJOIN(
". ",
,
UNIQ&":"&Count
)
)
)
)Excel solution 7 for Count Unique Letters in String, proposed by Timothée BLIOT:
=MAP(A2:A10,
LAMBDA(z,
LET(A,
MID(
z,
SEQUENCE(
LEN(
z
)
),
1
),
ARRAYTOTEXT(SORT(MAP(UNIQUE(
A
),
LAMBDA(x,
x&":"&SUM(--(A=x)))))))))Excel solution 8 for Count Unique Letters in String, proposed by Hussein SATOUR:
=MAP(
A2:A10,
LAMBDA(
y,
LET(
a,
MID(
y,
SEQUENCE(
LEN(
y
)
),
1
),
b,
SORT(
UNIQUE(
a
)
),
ARRAYTOTEXT(
MAP(
b,
LAMBDA(
x,
x&":"&COUNTA(
FILTER(
a,
a=x
)
)
)
)
)
)
)
)Excel solution 9 for Count Unique Letters in String, proposed by Oscar Mendez Roca Farell:
=MAP(
A2:A10,
LAMBDA(
a,
LET(
_l,
SORT(
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
)
),
_f,
FREQUENCY(
CODE(
_l
),
96+SEQUENCE(
26
)
),
ARRAYTOTEXT(
UNIQUE(
_l
)&":"&TOCOL(
IFS(
_f,
_f
),
2
)
)
)
)
)Excel solution 10 for Count Unique Letters in String, proposed by Duy Tùng:
=MAP(
A2:A10,
LAMBDA(
x,
LET(
a,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
TEXTJOIN(
{":",
", "},
,
GROUPBY(
a,
a,
ROWS,
,
0
)
)
)
)
)Excel solution 11 for Count Unique Letters in String, proposed by Sunny Baggu:
=MAP(
A2:A10,
LAMBDA(x,
LET(
_m, MID(x, SEQUENCE(LEN(x)), 1),
_um, SORT(UNIQUE(_m)),
_cn, MAP(_um, LAMBDA(a, ROWS(FILTER(_m, _m = a)))),
ARRAYTOTEXT(_um & ":" & _cn)
)
)
)Excel solution 12 for Count Unique Letters in String, proposed by 🇵🇪 Ned Navarrete C.:
=u,
LAMBDA(
i,
SUM(
--i
)
))))))Excel solution 13 for Count Unique Letters in String, proposed by Thang Van:
=MAP(A2:A10,
LAMBDA(_value,
LET(a,
_value,
s,
SEQUENCE(
LEN(
a
),
,
1
),
_mid,
MID(
a,
s,
1
),
r,
HSTACK(UNIQUE(
_mid
),
MAP(UNIQUE(
_mid
),
LAMBDA(_each,
SUMPRODUCT(--(_each=_mid))))),
ARRAYTOTEXT(
BYROW(
r,
LAMBDA(
a,
TEXTJOIN(
":",
,
a
)
)
)
)
)))Excel solution 14 for Count Unique Letters in String, proposed by Charles Roldan:
=LET(
a,
CHAR(
SEQUENCE(
26,
,
CODE(
"a"
)
)
),
MAP(
A2:A10,
LAMBDA(
x,
LET(
n,
LEN(
x
)-LEN(
SUBSTITUTE(
x,
a,
)
),
ARRAYTOTEXT(
FILTER(
a&":"&n,
n
)
)
)
)
)
)Excel solution 15 for Count Unique Letters in String, proposed by Andy Heybruch:
=MAP(
A2:A10,
LAMBDA(
_string,
LET(
_a,
_string,
_letters,
& SORT(
UNIQUE(
MID(
_a,
SEQUENCE(
LEN(
_a
)
),
1
)
),
1
),
_ct,
BYROW(
_letters,
LAMBDA(
v,
LEN(
_a
)-LEN(
SUBSTITUTE(
_a,
v,
""
)
)
)
),
TEXTJOIN(
", ",
1,
_letters&":"&_ct
)
)
)
)Excel solution 16 for Count Unique Letters in String, proposed by Gerson Pineda:
=MAP(A2:A10,
LAMBDA(x,
LET(m,
SORT(
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)
),
ARRAYTOTEXT(MAP(UNIQUE(
m
),
LAMBDA(i,
i&":"&COUNT(1/(i=m))))))))Excel solution 17 for Count Unique Letters in String, proposed by Victor Momoh (MVP, MOS, R.Eng):
=MAP(A2:A10,LAMBDA(x,LET(a,MID(x,SEQUENCE(LEN(x)),1),TEXTJOIN({":",", "},1,GROUPBY(a,a,COUNTA,0,0)))))Excel solution 18 for Count Unique Letters in String, proposed by Nicolas Micot:
=LET(_lettres;
STXT(
A2;
SEQUENCE(
NBCAR(
A2
)
);
1
);
_lettres_unique;
TRIER(
UNIQUE(
_lettres
)
);
_cpt;
MAP(_lettres_unique;
LAMBDA(l_car;
SOMME(--(_lettres=l_car))));
JOINDRE.TEXTE(
", ";
;
_lettres_unique&":"&_cpt
))Excel solution 19 for Count Unique Letters in String, proposed by Sandeep Marwal:
=MAP(
A2:A10,
LAMBDA(
I,
LET(
a,
SORT(
MID(
I,
SEQUENCE(
LEN(
I
)
),
1
),
,
1
),
b,
UNIQUE(
a
),
c,
MAP(
b,
LAMBDA(
x,
COUNTA(
FILTER(
a,
a=x
)
)
)
),
d,
b&":"&c,
TEXTJOIN(
", ",
,
d
)
)
)
)Excel solution 20 for Count Unique Letters in String, proposed by Ernesto Vega Castillo:
=BYROW(
A2:A10,
LAMBDA(
data,
LET(
str,
CHAR(
SEQUENCE(
26,
,
97
)
),
ext,
MID(
data,
SEQUENCE(
LEN(
data
),
,
1
),
1
),
newData,
str&":"&LEN(
data
)-LEN(
SUBSTITUTE(
data,
str,
""
)
),
TEXTJOIN(
", ",
,
FILTER(
newData,
RIGHT(
newData
)>"0"
)
)
)
)
)Excel solution 21 for Count Unique Letters in String, proposed by Gabriel Pugliese:
=DROP(
REDUCE(
0;
$A$2:$A$10;
LAMBDA(
t;
c;
VSTACK(
t;
LET(
a;
MID(
c;
SEQUENCE(
LEN(
c
)
);
1
);
LET(
sol;
MAP(
SORT(
UNIQUE(
a
)
);
LAMBDA(
x;
COUNTA(
FILTER(
a;
a=x
)
)
)
);
TEXTJOIN(
",";
;
SORT(
UNIQUE(
a
)
)&": "&sol
)
)
)
)
)
);
1
)Excel solution 22 for Count Unique Letters in String, proposed by Fábio Gatti:
=LAMBDA(Text,
Delimiter1,
Delimiter2,
LET(
_Letters,
MID(
Text,
SEQUENCE(
LEN(
Text
)
),
1
),
_Arr,
SORT(
UNIQUE(
_Letters
)
),
_Count,
BYROW(_Arr,
LAMBDA(x,
SUM(--(x=_Letters)))),
_Concat,
MAP(
_Arr,
_Count,
LAMBDA(
a,
b,
a&Delimiter1&b
)
),
_Result,
TEXTJOIN(
Delimiter2,
1,
_Concat
),
_Result
)
)(A2,
":",
", ")Excel solution 23 for Count Unique Letters in String, proposed by Surendra Reddy:
=MAP(
A2:A10,
LAMBDA(
each_string,
LET(
string_split,
MID(
each_string,
SEQUENCE(
LEN(
each_string
)
),
1
),
count_summary,
GROUPBY(
string_split,
string_split,
COUNTA,
,
0,
),
TEXTJOIN(
{":",
","},
,
count_summary
)
)
)
)Solving the challenge of Count Unique Letters in String with Python
Python solution 1 for Count Unique Letters in String, proposed by Luke Jarych:
Python solutions - pandas + xlwings:
import pandas as pd
import xlwings as xw
wb = xw.Book(r'Excel_Challenge_399 - Counter Dictionary.xlsx')
sh = wb.sheets[0]
table = sh.tables['Table1']
rng = sh.range(table.range.address)
df = rng.options(pd.DataFrame, header = True, index=False, numbers=int).value
results_list =[]
for _, row in df.iterrows():
a = sorted(list(row['String']))
b = list(row['String'])
counts = {}
for char in a:
count = sum(1 for item in b if char in item)
counts[char] = count
counts_str = ', '.join([f'{char}:{count}' for char, count in counts.items()])
results_list.append(counts_str)
result_df = pd.DataFrame({'Expected Answer': results_list})
Python solution 2 for Count Unique Letters in String, proposed by Giorgi Goderdzishvili:
from collections import Counter
lst = pd.read_clipboard().String.to_list()
for i in lst:
dc = Counter(i)
sorted_dict = dict(sorted(dc.items(), key=lambda item: item[0]))
print(sorted_dict)
Solving the challenge of Count Unique Letters in String with R
R solution 1 for Count Unique Letters in String, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("Excel/399 Counter Dictionary.xlsx", range = "A1:A10")
test = read_excel("Excel/399 Counter Dictionary.xlsx", range = "B1:B10")
count_chars = function(string) {
chars = string %>%
str_split(., pattern = "") %>%
unlist() %>%
tibble(char = .) %>%
group_by(char) %>%
summarise(count = n()) %>%
ungroup() %>%
arrange(char) %>%
unite("char_count", c("char", "count"), sep = ":") %>%
pull(char_count) %>%
str_c(collapse = ", ")
return(chars)
}
result = input %>%
mutate(`Answer Expected` = map_chr(String, count_chars)) %>%
select(-String)
&&