_x000D_
Python solution 1 for Characters Repetition!, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "CH-105 Character Repetition.xlsx"
input = pd.read_excel(path, usecols = "B", skiprows = 1, nrows = 10)
test = pd.read_excel(path, usecols = "D:E", skiprows = 1, nrows = 6)
result = (In the question table, where some passwords are provided, extract the 6 most commonly used characters across all the passwords and count their repetitions.
📌 Challenge Details and Links
Challenge Number: 105
Challenge Difficulty: ⭐⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
📥Link to the solution on YouTube
Solving the challenge of Characters Repetition! with Power Query
_x000D_
Power Query solution 1 for Characters Repetition!, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
P = List.TransformMany(Source[Password], Text.ToList, (i, _) => Text.Lower(_)),
S = Table.FromRows(
List.Sort(
List.TransformMany(
List.Accumulate({1 .. 6}, {}, (b, n) => b & {List.Mode(List.RemoveItems(P, b))}),
each {List.PositionOf(P, _, 2)},
(i, _) => {i, List.Count(_)}
),
{{each _{1}, 1}, {each _{0}, 0}}
),
{"Character", "Repetition"}
)
in
S
_x000D_
_x000D_
Power Query solution 2 for Characters Repetition!, proposed by Brian Julius:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
ToList = Table.AddColumn(Source, "Lett", each Text.ToList(Text.Lower(Text.Trim([Password])))),
Union = Table.FromList(List.Combine(ToList[Lett]), Splitter.SplitByNothing(), {"Character"}),
Group = Table.Group(Union, {"Character"}, {{"Repetition", each Table.RowCount(_), Int64.Type}}),
AddRank = Table.RemoveColumns(
Table.SelectRows(
Table.Sort(
Table.AddRankColumn(
Group,
"Rank",
{"Repetition", Order.Descending},
[RankKind = RankKind.Competition]
),
{{"Rank", Order.Ascending}, {"Character", Order.Ascending}}
),
each [Rank] <= 6
),
"Rank"
)
in
AddRank
_x000D_
_x000D_
Power Query solution 3 for Characters Repetition!, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
a = List.Transform(S[Password],Text.Lower),
b = List.Combine(List.Transform(a,Text.ToList)),
c = Table.FromColumns({b},{"Character"}),
d = Table.Group(c,"Character",{"Repitation", each Table.RowCount(_)}),
Sol = Table.FirstN(Table.Sort(d,{{"Repitation",1},{"Character",0}}),6)
in
Sol
_x000D_
_x000D_
Power Query solution 4 for Characters Repetition!, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Split = List.TransformMany(Source[Password], Text.ToList, (x, y) => Text.Lower(y)),
Table = Table.FromColumns({Split}, type table [Character = text]),
Group = Table.Group(Table, "Character", {"Repetition", Table.RowCount}),
Return = Table.MaxN(Group, "Repetition", 6)
in
Return
_x000D_
_x000D_
Power Query solution 5 for Characters Repetition!, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Text = Text.Lower(Text.Combine(Source[Password])),
Distinc = List.Transform(
List.Distinct(Text.ToList(Text)),
each {_, Text.Length(Text.Select(Text, _))}
),
First6 = List.FirstN(List.Sort(Distinc, {{each _{1}, 1}, {each _{0}, 0}}), 6),
Sol = Table.FromRows(First6, {"Character", "Repetition"})
in
Sol
_x000D_
_x000D_
Power Query solution 6 for Characters Repetition!, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
ToList = Text.ToList(Text.Lower(Text.Combine(Source[Password]))),
Distinct = List.Distinct(ToList),
Transform = List.Transform(Distinct, (x) => {x, List.Count(List.Select(ToList, each _ = x))}),
Table = Table.FromRows(Transform, type table [Character = text, Repetition = number]),
Sort = Table.Sort(Table, {{"Repetition", 1}, {"Character", 0}}),
Result = Table.FirstN(Sort, 6)
in
Result
_x000D_
_x000D_
Power Query solution 7 for Characters Repetition!, proposed by Kris Jaganah:
let
A = Text.ToList(
Text.Lower(Text.Combine(Excel.CurrentWorkbook(){[Name = "Table1"]}[Content][Password]))
),
B = List.Sort(List.Distinct(A)),
C = List.Transform(B, each List.Count(List.Select(A, (x) => x = _))),
D = List.MaxN(C, 6),
E = List.Distinct(List.Combine(List.Transform(D, each List.PositionOf(C, _, Occurrence.All)))),
F = List.Transform(E, each B{_}),
G = Table.FromColumns({F, D}, {"Character", "Repetition"})
in
G
_x000D_
_x000D_
Power Query solution 8 for Characters Repetition!, proposed by Yaroslav Drohomyretskyi:
let
Джерело = Table.MaxN(
Table.Group(
Table.FromList(
Text.ToList(Text.Combine(Excel.CurrentWorkbook(){[Name = "Таблиця1"]}[Content][Password])),
Splitter.SplitByNothing(),
{"Character"}
),
{"Character"},
{{"Repitation", each Table.RowCount(_)}}
),
"Repitation",
6
)
in
Джерело
_x000D_
_x000D_
Power Query solution 9 for Characters Repetition!, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
A = Table.AddColumn(Source, "Character", each Text.ToList([Password])),
B = Table.ExpandListColumn(A, "Character"),
B2 = Table.TransformColumns(B, {{"Character", Text.Lower, type text}}),
C = Table.Group(B2, {"Character"}, {{"Rep", each Table.RowCount(_), Int64.Type}}),
D = Table.Sort(C, {{"Rep", Order.Descending}, {"Character", Order.Ascending}}),
E = Table.AddIndexColumn(D, "Index", 1, 1, Int64.Type),
F = Table.SelectRows(E, each [Index] <= 6),
G = Table.RemoveColumns(F, {"Index"})
in
G
_x000D_
_x000D_
Power Query solution 10 for Characters Repetition!, proposed by Ahmed Ariem:
let
f= (w,i)=> [
a = List.Transform( List.Combine( Table.TransformColumns( w,{i,(x)=> Text.ToList(x)})[Password]),Text.Lower),
b = List.Distinct(a),
c = Table.FromList(
List.Select(b,(x)=>
List.Count(List.Select(a,(z)=> z=x))>=5)
,(x)=>{x},{"Charachter"}),
d= Table.AddColumn(c,"Count", each List.Count(List.Select(a,(x)=> x=[Charachter])))
][d],
Source = Excel.CurrentWorkbook(){[Name="tbl"]}[Content],
from = f( Source,"Password")
in
from
_x000D_
_x000D_
Power Query solution 11 for Characters Repetition!, proposed by Meganathan Elumalai:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
All = Table.FromColumns(
{List.Combine(Table.TransformRows(Source, (f) => Text.ToList(Text.Lower(f[Password]))))},
{"Char"}
),
Group = Table.Group(All, {"Char"}, {{"Repitation", each Table.RowCount(_)}}),
Result = Table.MaxN(Group, "Repitation", 6)
in
Result
_x000D_
_x000D_
Power Query solution 12 for Characters Repetition!, proposed by Jacob Geray:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Lowercased Text" = Table.TransformColumns(Source, {{"Password", Text.Lower, type text}}),
TextToChars = Table.TransformColumns(#"Lowercased Text", {"Password", each Text.ToList(_)}),
AllChars = List.Combine(TextToChars[Password]),
DistinctChars = List.Distinct(AllChars),
DistinctCharsToTable = Table.FromList(
DistinctChars,
Splitter.SplitByNothing(),
{"Character"},
null,
ExtraValues.Error
),
CountInstances = Table.AddColumn(
DistinctCharsToTable,
"Repetition",
each List.Count(List.Select(AllChars, (x) => x = [Character]))
),
KeepTop6 = Table.MaxN(CountInstances, "Repetition", 6)
in
KeepTop6
_x000D_
_x000D_
Power Query solution 13 for Characters Repetition!, proposed by Szabolcs Phraner:
let Source =..
Characters = Table.FromColumns( {List.Combine( List.Transform( Source[Password], Text.ToList ) ) }, {"Character"} ),
CountChars = Table.Group(Characters, {"Character"}, {{"Repitation", each Table.RowCount(_), Int64.Type}}),
MaxN = Table.MaxN( CountChars, "Repitation",6 )
in
MaxN
_x000D_
Solving the challenge of Characters Repetition! with Excel
_x000D_
Excel solution 1 for Characters Repetition!, proposed by 🇰🇷 Taeyong Shin:
=LET(
c,
TOCOL(
REGEXEXTRACT(
CONCAT(
B3:B12
),
".",
1
)
),
TAKE(
GROUPBY(
c,
c,
ROWS,
,
0,
-2
),
6
)
)
_x000D_
_x000D_
Excel solution 2 for Characters Repetition!, proposed by محمد حلمي:
=LET(
c,
CONCAT(
B3:B12
),
d,
MID(
c,
SEQUENCE(
LEN(
c
)
),
1
), u,
UNIQUE(
d
),
r,
MAP(
u,
LAMBDA(
a,
SUM(
N(
a=d
)
)
)
),
SORT( FILTER(
HSTACK(
u,
r
),
r>LARGE(
UNIQUE(
r
),
4
)
),
{2,
1},
{-1,
1}
)
)
_x000D_
_x000D_
Excel solution 3 for Characters Repetition!, proposed by Oscar Mendez Roca Farell:
=LET(
n,
TOCOL(
CODE(
LOWER(
MID(
B3:B12,
SEQUENCE(
,
12
),
1
)
)
),
2
),
TAKE(
SORT(
HSTACK(
CHAR(
n
),
FREQUENCY(
n,
n
)
),
2,
-1
),
6
)
)
_x000D_
_x000D_
Excel solution 4 for Characters Repetition!, proposed by Julian Poeltl:
=LET(
C,
LOWER(
CONCAT(
B3:B12
)
),
S,
CHAR(
SEQUENCE(
250
)
),
TAKE(
SORT(
HSTACK(
S,
LEN(
C
)-LEN(
SUBSTITUTE(
C,
S,
""
)
)
),
2,
-1
),
6
)
)
_x000D_
_x000D_
Excel solution 5 for Characters Repetition!, proposed by Kris Jaganah:
=LET(
a,
CONCAT(
B3:B12
),
b,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
TAKE(
SORT(
GROUPBY(
b,
b,
COUNTA,
0,
0
),
2,
-1
),
6
)
)
_x000D_
_x000D_
Excel solution 6 for Characters Repetition!, proposed by Kris Jaganah:
=LET(
a,
CONCAT(
B3:B12
),
b,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
c,
UNIQUE(
b
),
d,
MAP(
c,
LAMBDA(
x,
SUM(
N(
x=b
)
)
)
),
SORT(
FILTER(
HSTACK(
c,
d
),
d>=LARGE(
d,
6
)
),
2,
-1
)
)
_x000D_
_x000D_
Excel solution 7 for Characters Repetition!, proposed by Imam Hambali:
=LET(
a,
LOWER(
CONCAT(
B3:B12
)
), b,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
), TAKE(
GROUPBY(
b,
b,
COUNTA,
0,
0,
-2
),
6
))
_x000D_
_x000D_
Excel solution 8 for Characters Repetition!, proposed by Sunny Baggu:
=LET( a,
CONCAT(
B3:B12
), b,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
), ub,
UNIQUE(
b
), c,
MAP(
ub,
LAMBDA(
t,
SUM(
N(
b = t
)
)
)
), SORT(
FILTER(
HSTACK(
ub,
c
),
c >= LARGE(
c,
6
)
),
{2,
1},
{-1,
1}
))
_x000D_
_x000D_
Excel solution 9 for Characters Repetition!, proposed by Alejandro Campos:
=LET( e,
TOCOL(
LOWER(
MID(
A3:A12,
SEQUENCE(
,
LEN(
@+A3:A12
)
),
1
)
),
3
), c,
CODE(
e
), TAKE(
GROUPBY(
e,
c,
COUNT,
0,
0,
-2
),
6
)
)
_x000D_
_x000D_
Excel solution 10 for Characters Repetition!, proposed by Asheesh Pahwa:
=LET(p,
B3:B12,
c,
CONCAT(
p
),
m,
MID(
c,
SEQUENCE(
LEN(
c
)
),
1
),
u,
UNIQUE(
m
),
l,
MAP(u,
LAMBDA(x,
SUM(--(m=x)))),
TAKE(
SORTBY(
HSTACK(
u,
l
),
l,
-1
),
6
))
_x000D_
_x000D_
Excel solution 11 for Characters Repetition!, proposed by Bilal Mahmoud kh.:
=LET(
a,
CONCAT(
B2:B11
),
b,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
c,
MAP(
b,
LAMBDA(
x,
COUNTA(
FILTER(
b,
b=x
)
)
)
),
TAKE(
SORT(
UNIQUE(
HSTACK(
b,
c
)
),
2,
-1
),
6
)
)
_x000D_
_x000D_
Excel solution 12 for Characters Repetition!, proposed by Eddy Wijaya:
=LET( f,
LAMBDA(
a,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
)
), db,
DROP(
REDUCE(
0,
B3:B12,
LAMBDA(
a,
v,
VSTACK(
a,
f(
v
)
)
)
),
1
), SORT(
HSTACK(
UNIQUE(
db
),
MAP(
UNIQUE(
db
),
LAMBDA(
m,
ROWS(
FILTER(
db,
db=m
)
)
)
)
),
2,
-1
)
)
_x000D_
_x000D_
Excel solution 13 for Characters Repetition!, proposed by El Badlis Mohd Marzudin:
=LET(a,CONCAT(B3:B12),b,MID(a,SEQUENCE(LEN(a)),1),TAKE(GROUPBY(b,b,COUNTA,,,-2),6))
_x000D_
_x000D_
Excel solution 14 for Characters Repetition!, proposed by ferhat CK:
=LET(a,
CONCAT(
B3:B12
),
b,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
c,
MAP(UNIQUE(
b
),
LAMBDA(x,
SUMPRODUCT(--(b=x)))),
TAKE(
SORT(
HSTACK(
UNIQUE(
b
),
c
),
2,
-1
),
6
))
_x000D_
_x000D_
Excel solution 15 for Characters Repetition!, proposed by Gerson Pineda:
=LET(i,
CONCAT(
B3:B12
),
p,
MID(
i,
SEQUENCE(
LEN(
i
)
),
1
),
u,
UNIQUE(
p
),
TAKE(SORT(HSTACK(u,
MAP(u,
LAMBDA(x,
SUM(--(x=p))))),
2,
-1),
6))
_x000D_
_x000D_
Excel solution 16 for Characters Repetition!, proposed by Gerson Pineda:
=LET(
i,
CONCAT(
B3:B12
),
p,
MID(
i,
SEQUENCE(
LEN(
i
)
),
1
),
TAKE(
GROUPBY(
p,
p,
COUNTA,
,
0,
-2
),
6
)
)
_x000D_
_x000D_
Excel solution 17 for Characters Repetition!, proposed by Hussein SATOUR:
=LET(
a,
TOCOL(
REGEXEXTRACT(
CONCAT(
B3:B12
),
".",
1
)
),
b,
UNIQUE(
a
),
c,
MAP(
b,
LAMBDA(
x,
COUNTA(
FILTER(
a,
a=x
)
)
)
),
TAKE(
SORT(
HSTACK(
b,
c
),
2,
-1
),
6
)
)
_x000D_
_x000D_
Excel solution 18 for Characters Repetition!, proposed by Md. Zohurul Islam:
=LET( P,
B3:B12, Q,
LOWER(
P
), R,
CONCAT(
Q
), S,
MID(
R,
SEQUENCE(
LEN(
R
)
),
1
), T,
GROUPBY(
S,
S,
COUNTA,
0,
0,
-2
), result,
TAKE(
T,
6
), header,
{"Characters",
"Repeatation"}, report,
VSTACK(
header,
result
), report)
_x000D_
_x000D_
Excel solution 19 for Characters Repetition!, proposed by Meganathan Elumalai:
=LET(
c,
CONCAT(
B3:B12
),
d,
MID(
c,
SEQUENCE(
LEN(
c
)
),
1
),
e,
UNIQUE(
d
),
f,
MAP(
e,
LAMBDA(
x,
COUNTA(
FILTER(
d,
d=x
)
)
)
),
L,
TAKE(
SORT(
HSTACK(
e,
f
),
2,
-1
),
6
),
L
)
_x000D_
_x000D_
Excel solution 20 for Characters Repetition!, proposed by Nicolas Micot:
=LET(_text;
CONCAT(
B3:B12
);_splitChars;
STXT(
_text;
SEQUENCE(
NBCAR(
_text
)
);
1
);_charsUsed;
UNIQUE(
_splitChars
);_nb;
MAP(_charsUsed;
LAMBDA(l_char;
SOMME(--(_splitChars=l_char))));_table;
ASSEMB.H(
_charsUsed;
_nb
);PRENDRE(
TRIERPAR(
_table;
_nb;
-1
);
6
))
_x000D_
_x000D_
Excel solution 21 for Characters Repetition!, proposed by Pieter de B.:
=LET(
s,
SEQUENCE(
255
),
c,
CHAR(
s
),
n,
MAP(
c,
LAMBDA(
a,
SUM(
N(
EXACT(
MID(
B3:B12,
TOROW(
s
),
1
),
a
)
)
)
)
),
TAKE(
SORT(
HSTACK(
c,
n
),
{2,
1},
{-1,
1}
),
6
)
)
_x000D_
_x000D_
Excel solution 22 for Characters Repetition!, proposed by Pieter de B.:
=LET(
a,
MID(
B3:B12,
SEQUENCE(
,
255
),
1
),
b,
TOCOL(
IFS(
LEN(
a
),
a
),
2
),
u,
UNIQUE(
b
),
n,
MAP(
u,
LAMBDA(
m,
SUM(
N(
m=b
)
)
)
),
TAKE(
SORT(
HSTACK(
u,
n
),
{2,
1},
{-1,
1}
),
6
)
)
_x000D_
_x000D_
Excel solution 23 for Characters Repetition!, proposed by Richard Daniels:
=LET(r,
LET(chars,TOCOL(
CODE(
REGEXEXTRACT(
CONCAT(
B3:B12
),
".",
1
)
)
),LET(
uniqueChars,
UNIQUE(
chars
), counts,
MAP(uniqueChars,
LAMBDA(x,
SUMPRODUCT(--(chars=x)))), HSTACK(
uniqueChars,
CHAR(
uniqueChars
),
counts
)
)
),
DROP(
TAKE(
SORT(
r,
{3,
2},
-1
),
6
),
,
1
))
_x000D_
_x000D_
Excel solution 24 for Characters Repetition!, proposed by Rick Rothstein:
=LET(
c,
CONCAT(
LOWER(
B3:B12
)
),
u,
UNIQUE(
MID(
c,
SEQUENCE(
LEN(
c
)
),
1
)
),
s,
SORT(
HSTACK(
u,
SCAN(
a,
u,
LAMBDA(
a,
x,
LEN(
c
)-LEN(
SUBSTITUTE(
c,
x,
""
)
)
)
)
),
2,
-1
),
t,
TAKE(
s,
,
-1
),
FILTER(
s,
t>=LARGE(
t,
6
)
)
)
_x000D_
Solving the challenge of Characters Repetition! with Python
_x000D_
Python solution 1 for Characters Repetition!, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "CH-105 Character Repetition.xlsx"
input = pd.read_excel(path, usecols = "B", skiprows = 1, nrows = 10)
test = pd.read_excel(path, usecols = "D:E", skiprows = 1, nrows = 6)
result = (