Extract the group of numbers from A2:A11 and list the top 3 unique maximum and minimum numbers. For example – A5A36449EYU9C577AAAQ – This has following groups of numbers 5, 36449, 9, 577
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 114
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Extract Top Number Groups with Power Query
Power Query solution 1 for Extract Top Number Groups, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Rs =
let
a = List.Sort(
List.Distinct(
List.RemoveNulls(
List.Transform(
Text.SplitAny(Text.Combine(Source[String], "A"), Text.Combine({"A" .. "Z"})),
Number.From
)
)
)
)
in
Table.FromColumns(
{List.Reverse(List.LastN(a, 3)), List.FirstN(a, 3)},
{"Top 3 Max", "Top 3 Min"}
)
in
RsPower Query solution 2 for Extract Top Number Groups, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Transform = List.Distinct(
List.RemoveNulls(
List.Transform(
List.Combine(
List.Transform(Source[String], (f) => Text.SplitAny(f, Text.Combine({"A" .. "Z"})))
),
Number.From
)
)
),
Result = Table.FromColumns({List.MaxN(Transform, 3)} & {List.MinN(Transform, 3)}, {"Max", "Min"})
in
ResultPower Query solution 3 for Extract Top Number Groups, proposed by Alejandro Simón 🇵🇦 🇪🇸:
Acabo de revisar la ventaja de utilizar la función Text.Split,Any, simplemente brillante, sencilla y elegante!!! 🤐
Show translation
Power Query solution 4 for Extract Top Number Groups, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Custom1 = Table.AddColumn(
Source,
"New",
each List.Transform(
List.Select(
Text.Split(
Text.Combine(
List.ReplaceMatchingItems(
Text.ToList([String]),
List.Zip({{"A" .. "Z"}, List.Repeat({","}, List.Count({"A" .. "Z"}))})
),
""
),
","
),
each _ <> ""
),
each Number.From(_)
)
),
Custom2 = Table.FromColumns(
{
List.FirstN(List.Reverse(List.Distinct(List.Sort(List.Combine(Custom1[New])))), 3),
List.FirstN(List.Distinct(List.Sort(List.Combine(Custom1[New]))), 3)
},
{"Top 3 Max", "Top 3 Min"}
)
in
Custom2Power Query solution 5 for Extract Top Number Groups, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
tab = Table.AddColumn(
Fonte,
"Personalizar",
each Splitter.SplitTextByAnyDelimiter({"A" .. "Z"}, QuoteStyle.Csv)([String])
),
Result = Table.FromColumns(
[
a = List.Select(Table.ExpandListColumn(tab, "Personalizar")[Personalizar], each _ <> ""),
b = List.Distinct(List.Sort(List.Transform(a, Number.From))),
c = List.FirstN(b, 3),
d = List.Sort(List.LastN(b, 3), Order.Descending),
e = {d, c}
][e],
{"Top 3 Max", "Top 3 Min"}
)
in
ResultPower Query solution 6 for Extract Top Number Groups, proposed by Bhavya Gupta:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Final = Table.FromColumns(
List.Transform(
List.Transform(
{1, 0},
(s) =>
List.Sort(
List.Distinct(
List.Combine(
Table.TransformRows(
Source,
each Splitter.SplitTextByAnyDelimiter({"A" .. "Z"})([String])
)
)
),
{each Number.From(_), s}
)
),
(l) => List.Transform(List.FirstN(List.Select(l, each _ <> ""), 3), Number.From)
),
{"Top 3 Max", "Top 3 Min"}
)
in
FinalPower Query solution 7 for Extract Top Number Groups, proposed by Matthias Friedmann:
let
Source = Excel.CurrentWorkbook(){[Name = "MinMaxString"]}[Content],
list = List.Transform(
List.Distinct(Text.SplitAny(Text.Combine(Source[String], "A"), Text.Combine({"A" .. "Z"}))),
Number.From
),
top = Table.FromColumns({List.MaxN(list, 3), List.MinN(list, 3)}, {"Top 3 Max", "Top 3 Min"})
in
topPower Query solution 8 for Extract Top Number Groups, proposed by Krzysztof Kominiak:
let
Source = Data,
Ls = List.Union(
Table.TransformColumns(
Source,
{
"String",
each List.Transform(Splitter.SplitTextByAnyDelimiter({"A" .. "Z"})(_), each Number.From(_))
}
)[String]
),
Result = Table.FromColumns({List.MaxN(Ls, 3)} & {List.MinN(Ls, 3)}, {"Top 3 Max", "Top 3 Min"})
in
ResultPower Query solution 9 for Extract Top Number Groups, proposed by Jan Willem Van Holst:
let
Source = Your Data,
Split = Table.TransformColumns(Source,{{"String", Splitter.SplitTextByCharacterTransition({"0".."9"}, {"A".."Z"})}}),
#"Expanded String" = Table.ExpandListColumn(Split, "String"),
#"Split Column by Character Transition" = Table.SplitColumn(#"Expanded String", "String", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"String.1", "String.2"}),
#"Filtered Rows" = Table.SelectRows(#"Split Column by Character Transition", each ([String.2] <> null)),
#"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"String.2", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"String.1"}),
myList = List.Sort(List.Distinct(#"Removed Columns"[String.2]))
in
Table.FromColumns({List.Sort(List.LastN(myList,3), Order.Descending), List.FirstN(myList,3)}, {"Top 3 max", "Top 3 Min"})
Solving the challenge of Extract Top Number Groups with Excel
Excel solution 1 for Extract Top Number Groups, proposed by Rick Rothstein:
=LET(
t,
TEXTJOIN(
"X",
,
A2:A11
),
m,
MID(
t,
SEQUENCE(
LEN(
t
)
),
1
),
AGGREGATE(
{14,
15},
6,
UNIQUE(
0+TEXTSPLIT(
TRIM(
CONCAT(
IFERROR(
0+m,
" "
)
)
),
" "
),
1
),
{1;2;3}
)
)Excel solution 2 for Extract Top Number Groups, proposed by John V.:
=AGGREGATE(
{14,
15},
6,
UNIQUE(
--TEXTSPLIT(
CONCAT(
A2:A11&"J"
),
,
CHAR(
ROW(
65:90
)
)
)
),
{1;2;3}
)
p.d: Bo Rydobon has a similar approach. I didn't see it when I posted this attempt)Excel solution 3 for Extract Top Number Groups, proposed by محمد حلمي:
=LET(
a,
UNIQUE(
TEXTSPLIT(
CONCAT(
A2:A11&" "
),
,
VSTACK(
" ",
CHAR(
ROW(
65:90
)
)
),
1
)
)+0,
r,
ROW(
1:3
),
HSTACK(
LARGE(
a,
r
),
SMALL(
a,
r
)
)
)Excel solution 4 for Extract Top Number Groups, proposed by محمد حلمي:
=REDUCE(
0,
A2:A11,
LAMBDA(
a,
d,
LET(
s,
TEXTSPLIT(
d,
CHAR(
ROW(
65:90
)
),
,
1
)+0,
i,
VSTACK(
a,
HSTACK(
MAX(
s
),
MIN(
s
)
)
),
TAKE(
HSTACK(
SORT(
UNIQUE(
TAKE(
i,
,
1
)
),
,
-1
),
SORT(
UNIQUE(
DROP(
i,
,
1
)
)
)
),
3
)
)
)
)Excel solution 5 for Extract Top Number Groups, proposed by 🇰🇷 Taeyong Shin:
=AGGREGATE(
{14,
15},
,
UNIQUE(
--REGEXEXTRACT(
ARRAYTOTEXT(
A2:A11
),
"d+",
1
),
1
),
{1;2;3}
)
=LET(
s,
{1;2;3},
WRAPCOLS(
CHOOSEROWS(
GROUPBY(
--TEXTSPLIT(
CONCAT(
A2:A11&"A"
),
,
CHAR(
SEQUENCE(
26,
,
65
)
),
1
),
,
,
,
0
),
-s,
s
),
3
)
)Excel solution 6 for Extract Top Number Groups, proposed by Kris Jaganah:
=LET(
a,
UNIQUE(
TOCOL(
TEXTSPLIT(
TEXTJOIN(
"@",
TRUE,
A2:A11
),
,
CHAR(
SEQUENCE(
,
27,
64
)
)
)/1,
3
)
),
HSTACK(
LARGE(
a,
{1;2;3}
),
SMALL(
a,
{1;2;3}
)
)
)Excel solution 7 for Extract Top Number Groups, proposed by Julian Poeltl:
=LET(
C,
TEXTJOIN(
"A",
,
A2:A11
),
N,
TEXTSPLIT(
C,
CHAR(
64+SEQUENCE(
26
)
)
),
F,
UNIQUE(
--FILTER(
N,
N<>""
),
1
),
HSTACK(
LARGE(
F,
SEQUENCE(
3
)
),
SMALL(
F,
SEQUENCE(
3
)
)
)
)Excel solution 8 for Extract Top Number Groups, proposed by Timothée BLIOT:
=LET(
A,
A2:A11,
B,
1*TEXTSPLIT(
TEXTJOIN(
"/",
,
BYROW(
A,
LAMBDA(
a,
TEXTJOIN(
",",
,
TEXTSPLIT(
a,
TEXTSPLIT(
a,
SEQUENCE(
10,
,
0
),
,
1
),
,
1
)
)
)
)
),
",",
"/",
1,
,
""
),
C,
SORT(
UNIQUE(
TOCOL(
B,
3
)
),
,
-1
),
HSTACK(
TAKE(
C,
3
),
TAKE(
C,
-3
)
)
)Excel solution 9 for Extract Top Number Groups, proposed by Bhavya Gupta:
=LET(
a,
SORT(
UNIQUE(
TEXTSPLIT(
CONCAT(
Table1[String]&"["
),
,
CHAR(
SEQUENCE(
27,
,
65
)
),
TRUE
)*1
)
),
HSTACK(
SORT(
TAKE(
a,
-3
),
,
-1
),
TAKE(
a,
3
)
)
)Excel solution 10 for Extract Top Number Groups, proposed by Md. Zohurul Islam:
=LET(
hdr,
HSTACK(
"Top 3 Max",
"Top 3 Min"
),
a,
A2:A11,
b,
TEXTJOIN(
",",
,
a
),
seq,
SEQUENCE(
LEN(
b
)
),
d,
IFERROR(
MID(
b,
seq,
1
)+0,
"-"
),
e,
CONCAT(
d
),
f,
TOCOL(
0+TEXTSPLIT(
e,
"-"
),
3
),
mx,
TAKE(
UNIQUE(
SORT(
f,
,
-1
)
),
3
),
mn,
TAKE(
UNIQUE(
SORT(
f
)
),
3
),
rng,
HSTACK(
mx,
mn
),
result,
VSTACK(
hdr,
rng
),
result
)Excel solution 11 for Extract Top Number Groups, proposed by Stefan Olsson:
=LAMBDA(
a,
{SORTN(
a,
3,
2,
1,
FALSE
),
SORTN(
a,
3,
2
)}
)
(FLATTEN(
SPLIT(
REGEXREPLACE(
TEXTJOIN(
"|",
TRUE ,
& A2:A11
),
"D",
"|"
),
"|",
TRUE,
TRUE
)
))Excel solution 12 for Extract Top Number Groups, proposed by Stefan Olsson:
=LAMBDA(
a,
{SORTN(
a,
3,
2,
1,
false
),
SORTN(
a,
3,
2
)}
)(
FLATTEN(
SPLIT(
TEXTJOIN(
"A",
true,
A2:A11
),
"ABCDEFGHIJKLMNOPQRSTUVXYZ",
true,
true
)
))Excel solution 13 for Extract Top Number Groups, proposed by Victor Momoh (MVP, MOS, R.Eng):
=LET(
a,
SORT(
UNIQUE(
0+TEXTSPLIT(
TEXTJOIN(
"A",
1,
A2:A11
),
,
CHAR(
SEQUENCE(
26,
,
65
)
),
1
)
)
),
HSTACK(
SORT(
TAKE(
a,
-3
),
,
-1
),
TAKE(
a,
3
)
)
)Excel solution 14 for Extract Top Number Groups, proposed by Abhishek Kumar Jain:
=LET(
a,
SORT(
UNIQUE(
--TEXTSPLIT(
TEXTJOIN(
"A",
1,
A2:A11
),
,
CHAR(
SEQUENCE(
26,
,
65
)
),
1
)
)
),
b,
LARGE(
a,
{1;2;3}
)&"-"&SMALL(
a,
{1;2;3}
),
TEXTSPLIT(
TEXTJOIN(
"|",
1,
b
),
"-",
"|"
)
)Excel solution 15 for Extract Top Number Groups, proposed by Philippe Brillault:
=LET(
x,
SORT(
UNIQUE(
--REGEX.EXTRACT(
CONCAT(
A2:A11&"Z"
),
"(d+)",
1
),
1
),
,
-1,
1
),
WRAPCOLS(
HSTACK(
TAKE(
x,
,
3
),
SORT(
TAKE(
x,
,
-3
),
,
,
1
)
),
3
)
)Excel solution 16 for Extract Top Number Groups, proposed by Fábio Gatti:
=LAMBDA(
Array,
nRanking,
LET(
fxRow,
LAMBDA(
Row,
TEXTJOIN(
";",
1,
TEXTSPLIT(
Row,
,
CHAR(
SEQUENCE(
26,
,
65
)
),
1
)
)
),
vValues,
TEXTJOIN(
";",
1,
BYROW(
A2:A11,
fxRow
)
),
xValues,
UNIQUE(
--TEXTSPLIT(
vValues,
,
";"
)
),
xRangeRanking,
SEQUENCE(
nRanking
),
xTopValues,
LARGE(
xValues,
xRangeRanking
),
xMinValues,
SMALL(
xValues,
xRangeRanking
),
xHeaders,
HSTACK(
"Top "&nRanking&" Max",
"Top "&nRanking&" Min"
),
xData,
HSTACK(
xTopValues,
xMinValues
),
VSTACK(
xHeaders,
xData
)
)
)(A2:A11,
3)Solving the challenge of Extract Top Number Groups with SQL
SQL solution 1 for Extract Top Number Groups, proposed by Zoran Milokanović:
WITH /* Microsoft SQL Server 2019 */
DATA_PREP
AS
(
SELECT
T.STRING
,T.LETTERS
,TRANSLATE(T.STRING, T.LETTERS, REPLICATE(' ', DATALENGTH(T.LETTERS))) AS NUMBERS
FROM
(
SELECT
D.STRING
,TRANSLATE(D.STRING, '0123456789', ' ') AS LETTERS
FROM DATA D
) T
),
CALC
AS
(
SELECT DISTINCT
CAST(VALUE AS DECIMAL) AS NUMBER
,DENSE_RANK() OVER (ORDER BY CAST(VALUE AS DECIMAL)) AS TOP_MIN
,DENSE_RANK() OVER (ORDER BY CAST(VALUE AS DECIMAL) DESC) AS TOP_MAX
FROM DATA_PREP DP
CROSS APPLY STRING_SPLIT(DP.NUMBERS, ' ')
WHERE
VALUE <> ''
)
SELECT
MX.NUMBER AS TOP_3_MAX
,MN.NUMBER AS TOP_3_MIN
FROM CALC MX
JOIN CALC MN ON MN.TOP_MIN = MX.TOP_MAX
WHERE
MX.TOP_MAX <= 3
ORDER BY
MX.TOP_MAX
;
&&