List the bottom 3 unique values across the columns.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 650
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Bottom Three Unique Values with Power Query
Power Query solution 2 for Bottom Three Unique Values, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[ Name = "data" ]}[Content],
Return = Table.Profile (
Source,
{
{
"Answer",
each true,
each [
U = List.Distinct ( _ ),
M = List.MinN ( U, 3, ( f ) => f ?? hashtag#infinity ),
T = List.Transform ( M, Text.From ),
R = Text.Combine ( T, ", " )
][R]
}
}
)[[Answer]]
in
Return
Power Query solution 3 for Bottom Three Unique Values, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = List.Transform(
Table.ToColumns(Source),
each Text.Combine(List.Transform(List.MinN(List.Distinct(_), 3), Text.From), ", ")
)
in
SolPower Query solution 4 for Bottom Three Unique Values, proposed by Luan Rodrigues:
let
Fonte = Table.FromColumns(
{
List.Transform(
Table.ToColumns(Tabela1),
each Text.Combine(List.Transform(List.MinN(List.Distinct(_), 3), (x) => Text.From(x)), ", ")
)
}
)
in
FontePower Query solution 5 for Bottom Three Unique Values, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Result = Table.FromColumns(
{
List.Transform(
Table.ToColumns(Source),
each Text.Combine(
List.Transform(
List.FirstN(List.RemoveNulls(List.Distinct(List.Sort(_))), 3),
(x) => Text.From(x)
),
", "
)
)
},
{"Answer Expected"}
)
in
ResultPower Query solution 6 for Bottom Three Unique Values, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
LT = List.Transform,
a = LT(Table.ToColumns(S), each List.MinN(List.RemoveNulls(List.Sort(List.Distinct(_))), 3)),
b = LT(a, each Text.Combine(LT(_, Text.From), ", ")),
Sol = Table.FromColumns({b}, {"Answer Expected"})
in
SolPower Query solution 7 for Bottom Three Unique Values, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Cols = Table.ToColumns(Source),
Res = Table.FromList(Cols, Fun, {"Answer Expected"}),
Fun = each [
A = List.Distinct(List.RemoveNulls(_)),
B = List.FirstN(List.Sort(A), 3),
C = {Text.Combine(List.Transform(B, each Text.From(_)), ", ")}
][C]
in
ResPower Query solution 8 for Bottom Three Unique Values, proposed by Meganathan Elumalai:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Result = Table.FromColumns(
{
List.Transform(
Table.ToColumns(Source),
(f) => Text.Combine(List.Transform(List.MinN(List.Distinct(f), 3), Text.From), ", ")
)
},
{"Result"}
)
in
ResultPower Query solution 9 for Bottom Three Unique Values, proposed by Peter Krkos:
PowerQuery solution:
= Table.FromColumns({
List.Transform(
Table.ToColumns(Source), each
Text.Combine(List.Transform(List.MinN(_, 3), Text.From), ", "))},
type table[Answer=text])
Power Query solution 10 for Bottom Three Unique Values, proposed by CA Raghunath Gundi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
A = List.Transform(
Table.ToColumns(Source),
(a) =>
Text.Combine(
List.Transform(
List.FirstN(List.Sort(List.Distinct(List.RemoveNulls(a))), 3),
each Text.From(_)
),
", "
)
)
in
APower Query solution 11 for Bottom Three Unique Values, proposed by Oleksandr Mynka:
let
src = Excel.CurrentWorkbook(){[Name = "Source"]}[Content],
lst = Table.ToColumns(src),
num_lst = List.Transform(lst, (x) => List.MinN(List.Distinct(x), 3)),
abc_lst = List.Transform(num_lst, (x) => List.Transform(x, Text.From)),
res = List.Transform(abc_lst, (x) => Text.Combine(x, ", "))
in
resSolving the challenge of Bottom Three Unique Values with Excel
Excel solution 1 for Bottom Three Unique Values, proposed by Bo Rydobon 🇹🇭:
=TOCOL(BYCOL(A2:E15,LAMBDA(x, ARRAYTOTEXT(TAKE(SORT(UNIQUE(x)),3)))))Excel solution 2 for Bottom Three Unique Values, proposed by Rick Rothstein:
=TOCOL(
BYCOL(
A2:E15,
LAMBDA(
c,
ARRAYTOTEXT(
TAKE(
SORT(
UNIQUE(
c
)
),
3
)
)
)
)
)Excel solution 3 for Bottom Three Unique Values, proposed by John V.:
=TOCOL(BYCOL(A2:E15,LAMBDA(i,ARRAYTOTEXT(TAKE(UNIQUE(SORT(i)),3)))))
=TOCOL(BYCOL(A2:E15,LAMBDA(i,ARRAYTOTEXT(SMALL(UNIQUE(i),{1;2;3})))))Excel solution 4 for Bottom Three Unique Values, proposed by 🇰🇷 Taeyong Shin:
=TOCOL(
BYCOL(
A2:E15,
LAMBDA(
r,
ARRAYTOTEXT(
TAKE(
GROUPBY(
r,
,
),
3
)
)
)
)
)
=TOCOL(
TEXTBEFORE(
BYCOL(
A2:E15,
LAMBDA(
r,
ARRAYTOTEXT(
GROUPBY(
r,
,
)
)
)
),
",",
3
)
)Excel solution 5 for Bottom Three Unique Values, proposed by Kris Jaganah:
=TOCOL(
BYCOL(
A2:E16,
LAMBDA(
x,
ARRAYTOTEXT(
SMALL(
UNIQUE(
x
),
{1,
2,
3}
)
)
)
)
)Excel solution 6 for Bottom Three Unique Values, proposed by Kris Jaganah:
=TOCOL(
BYCOL(
A2:E15,
LAMBDA(
x,
ARRAYTOTEXT(
TAKE(
GROUPBY(
x,
,
),
3
)
)
)
)
)Excel solution 7 for Bottom Three Unique Values, proposed by Julian Poeltl:
=TOCOL(BYCOL(A2:E15,LAMBDA(A,ARRAYTOTEXT(SMALL(UNIQUE(A),SEQUENCE(3))))))Excel solution 8 for Bottom Three Unique Values, proposed by Aditya Kumar Darak 🇮🇳:
=TOCOL(
BYCOL(
A2:E15,
LAMBDA(
a,
ARRAYTOTEXT(
SMALL(
UNIQUE(
a
),
{1,
2,
3}
)
)
)
)
)Excel solution 9 for Bottom Three Unique Values, proposed by Timothée BLIOT:
=TOCOL(BYCOL(A2:E15,LAMBDA(x,TEXTJOIN(", ",,TAKE(SORT(UNIQUE(x)),3)))))Excel solution 10 for Bottom Three Unique Values, proposed by Hussein SATOUR:
=TOCOL(BYCOL(A2:E15,LAMBDA(x,ARRAYTOTEXT(TAKE(UNIQUE(SORT(x)),3)))))Excel solution 11 for Bottom Three Unique Values, proposed by Duy Tùng:
=TOCOL(BYCOL(A2:E15,LAMBDA(s,TEXTJOIN(", ",,TAKE(SORT(UNIQUE(s)),3)))))Excel solution 12 for Bottom Three Unique Values, proposed by Sunny Baggu:
=TOCOL(
BYCOL(
A2:E15,
LAMBDA(a,
ARRAYTOTEXT(
TAKE(UNIQUE(SORT(TOCOL(IF(a = "", 1 / x, a), 3))), 3)
)
)
)
)Excel solution 13 for Bottom Three Unique Values, proposed by Sunny Baggu:
=TOCOL(
BYCOL(
A2:E15,
LAMBDA(
a,
ARRAYTOTEXT(
TAKE(
UNIQUE(
SORT(
FILTER(
a,
a <> ""
)
)
),
3
)
)
)
)
)Excel solution 14 for Bottom Three Unique Values, proposed by Abdallah Ally:
=LET(a,A2:E15,REDUCE("Answer Expected",SEQUENCE(COLUMNS( a)),LAMBDA(x,y,VSTACK(x,ARRAYTOTEXT(TAKE(UNIQUE(SORT(CHOOSECOLS(a,y))),3))))))Excel solution 15 for Bottom Three Unique Values, proposed by Anshu Bantra:
=TOCOL(
BYCOL(
A2:E15,
LAMBDA(
col_,
TEXTJOIN(
", ",
,
SMALL(
UNIQUE(
IF(
col_ <> "",
col_,
""
)
),
{1,
2,
3}
)
)
)
)
)Excel solution 16 for Bottom Three Unique Values, proposed by Anshu Bantra:
=3):
rng = rng[~pd.isnull(
rng
)]
rng = [str(
int(
_
)
) for _ in np.unique(
rng
)[:n]]
return ',
'.join(
rng
)
df = to_df(
REF(
"A1:E15"
)
)
df.apply(
get_smallest_N
)Excel solution 17 for Bottom Three Unique Values, proposed by Md. Zohurul Islam:
=TOCOL(
BYCOL(
A2:E15,
LAMBDA(
x,
ARRAYTOTEXT(
SMALL(
UNIQUE(
x
),
SEQUENCE(
3
)
)
)
)
)
)Excel solution 18 for Bottom Three Unique Values, proposed by Pieter de B.:
=TOCOL(
BYCOL(
A2:E15,
LAMBDA(
b,
ARRAYTOTEXT(
SMALL(
UNIQUE(
b
),
{1,
2,
3}
)
)
)
)
)Excel solution 19 for Bottom Three Unique Values, proposed by Hamidi Hamid:
=TRANSPOSE(
BYCOL(
A2:E15,
LAMBDA(
a,
ARRAYTOTEXT(
TAKE(
SORT(
UNIQUE(
SMALL(
a,
SEQUENCE(
13
)
)
)
),
3
)
)
)
)
)Excel solution 20 for Bottom Three Unique Values, proposed by Asheesh Pahwa:
=DROP(
REDUCE(
"",
SEQUENCE(
,
5
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
I,
INDEX(
A2:E15,
,
y
),
t,
UNIQUE(
TOCOL(
I,
1
)
),
ARRAYTOTEXT(
SMALL(
t,
{1,
2,
3}
)
)
)
)
)
),
1
)_x000D_
Excel solution 21 for Bottom Three Unique Values, proposed by Asheesh Pahwa:
=TOCOL(
BYCOL(
A2:E15,
LAMBDA(
x,
LET(
t,
TOCOL(
x,
1
),
u,
UNIQUE(
t
),
ARRAYTOTEXT(
SMALL(
u,
{1,
2,
3}
)
)
)
)
)
)_x000D_
_x000D_
Excel solution 22 for Bottom Three Unique Values, proposed by ferhat CK:
=TOCOL(BYCOL(SEQUENCE(,COLUMNS(A2:E15)),LAMBDA(x,ARRAYTOTEXT(INDEX(UNIQUE(SORT(CHOOSECOLS(A2:E15,x))),{1,2,3})))))
_x000D_
_x000D_
Excel solution 23 for Bottom Three Unique Values, proposed by Jaroslaw Kujawa:
=DROP(
REDUCE(
"";
SEQUENCE(
5
);
LAMBDA(
a;
x;
LET(
r;
A2:E15;
xx;
SORT(
FILTER(
CHOOSECOLS(
r;
x
);
ISNUMBER(
CHOOSECOLS(
r;
x
)
)
)
);
VSTACK(
a;
TEXTJOIN(
", ";
1;
UNIQUE(
FILTER(
xx;
xx<=SMALL(
UNIQUE(
xx
);
3
)
)
)
)
)
)
)
);
1
)
_x000D_
_x000D_
Excel solution 24 for Bottom Three Unique Values, proposed by Seokho MOON:
=TOCOL(
BYCOL(
A2:E15,
LAMBDA(x, TEXTJOIN(", ", 1, TAKE(SORT(UNIQUE(x)), 3)))
)
)
_x000D_
_x000D_
Excel solution 25 for Bottom Three Unique Values, proposed by Ankur Sharma:
=TOCOL(
BYCOL(
A2:E15,
LAMBDA(
a,
ARRAYTOTEXT(
SMALL(
UNIQUE(
TOCOL(
a,
1
)
),
{1,
2,
3}
)
)
)
)
)
_x000D_
_x000D_
Excel solution 26 for Bottom Three Unique Values, proposed by Meganathan Elumalai:
=TOCOL(
BYCOL(
A2:E15,
LAMBDA(
x,
ARRAYTOTEXT(
TAKE(
SORT(
UNIQUE(
TOCOL(
x,
1
)
)
),
3
)
)
)
)
)
_x000D_
_x000D_
Excel solution 27 for Bottom Three Unique Values, proposed by Imam Hambali:
=TOCOL(
BYCOL(
A2:E15,
LAMBDA(
x,
ARRAYTOTEXT(
TAKE(
UNIQUE(
SORT(
FILTER(
x,
x<>""
),
1,
1
)
),
3
)
)
)
)
)
_x000D_
_x000D_
Excel solution 28 for Bottom Three Unique Values, proposed by CA Raghunath Gundi:
=TRANSPOSE(
BYCOL(
A2:E15,
LAMBDA(
a,
ARRAYTOTEXT(
TAKE(
SORT(
UNIQUE(
TOCOL(
a,
3
)
)
),
3
)
)
)
)
)
_x000D_
_x000D_
Excel solution 29 for Bottom Three Unique Values, proposed by Peter Bartholomew:
= TOCOL(
BYCOL(
valueColumns,
LAMBDA(
column,
LET(
sorted,
SORT(
UNIQUE(
column
)
),
ARRAYTOTEXT(
TAKE(
sorted,
3
)
)
)
)
)
)
_x000D_
_x000D_
Excel solution 30 for Bottom Three Unique Values, proposed by Ahmed Ariem:
=TOCOL(BYCOL(A2:E15,LAMBDA(x, ARRAYTOTEXT(SMALL(UNIQUE(x),{1,2,3})))))
_x000D_
_x000D_
Excel solution 31 for Bottom Three Unique Values, proposed by Nicolas Micot:
=TRANSPOSE(BYCOL(A2:E15;LAMBDA(l_col;JOINDRE.TEXTE(", ";;PETITE.VALEUR(UNIQUE(l_col);SEQUENCE(3))))))
_x000D_
_x000D_
Excel solution 32 for Bottom Three Unique Values, proposed by Ernesto Vega Castillo:
=LET(c,A2:E15,WRAPCOLS(BYCOL(c,LAMBDA(x,ARRAYTOTEXT(TAKE(GROUPBY(x,,),3)))),COLUMNS(c)))
_x000D_
_x000D_
Excel solution 33 for Bottom Three Unique Values, proposed by Ernesto Vega Castillo:
TOCOL(BYCOL(A2:E15,LAMBDA(x,ARRAYTOTEXT(TAKE(REGEXEXTRACT(UNIQUE(SORT(x)),"[0-9]+",),3)))))
_x000D_
_x000D_
Excel solution 34 for Bottom Three Unique Values, proposed by Ernesto Vega Castillo:
=TOCOL(
BYCOL(
A2:E15,
LAMBDA(
arr,
ARRAYTOTEXT(
TAKE(
SORT(
UNIQUE(
arr
)
),
3
)
)
)
)
)
_x000D_
_x000D_
Excel solution 35 for Bottom Three Unique Values, proposed by Ben Warshaw:
=TOCOL(BYCOL(A2:E15,LAMBDA(x,TEXTJOIN(", ",,TAKE(SORT(UNIQUE(x)),3)))))
_x000D_
_x000D_
Excel solution 36 for Bottom Three Unique Values, proposed by Stefan Alexandrov:
=TOCOL(
BYCOL($A$2:$E$15,
LAMBDA(x,
TEXTJOIN(", ",1,SORT(UNIQUE(FILTER(FILTER(x,x<>""),FILTER(x,x<>"")<=SMALL(UNIQUE(FILTER(x,x<>"")),3))))))
)
)
_x000D_
_x000D_
Excel solution 37 for Bottom Three Unique Values, proposed by Jorge Alvarez:
=TRANSPONER(
BYCOL(
A2:E15;
LAMBDA(
a;
MATRIZATEXTO(
TOMAR(
ORDENAR(
UNICOS(
a
)
);
3
)
)
)
)
)
_x000D_
_x000D_
Excel solution 38 for Bottom Three Unique Values, proposed by abdelaziz kamal allam:
=TOCOL(BYCOL(A2:E15,LAMBDA(x,TEXTJOIN(",",TRUE,SMALL(UNIQUE(x,FALSE,FALSE),SEQUENCE(3))))))
_x000D_
_x000D_
Excel solution 39 for Bottom Three Unique Values, proposed by Fredrick Nwanyanwu:
=TRANSPOSE(BYCOL(A2:E15,LAMBDA(a,TEXTJOIN(", ",,TAKE(UNIQUE(SORT(a),,0),3)))))
_x000D_
_x000D_
Excel solution 40 for Bottom Three Unique Values, proposed by Ana Di Nezio Pérez:
=TRANSPOSE(
BYCOL(
A2:E15,
LAMBDA(
a,
TEXTJOIN(
", ",
,
TAKE(
SORT(
UNIQUE(
a
)
),
3
)
)
)
)
)
_x000D_
_x000D_
Excel solution 41 for Bottom Three Unique Values, proposed by Casper Badenhorst:
=TOCOL(BYCOL(A:F,
LAMBDA(col,
(IFERROR(
TEXTJOIN(
",",
,
SMALL(
UNIQUE(
col
),
SEQUENCE(
3
)
)
),
""
)))))
_x000D_
Solving the challenge of Bottom Three Unique Values with Python
_x000D_
Python solution 1 for Bottom Three Unique Values, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "650 Top 3 Across Columns.xlsx"
input = pd.read_excel(path, usecols="A:E", nrows=15)
test = pd.read_excel(path, usecols="G", nrows=5).values.flatten().tolist()
result = input.apply(lambda col: ', '.join(map(str, sorted(col.dropna().astype(int).unique())[:3]))).tolist()
print(result == test) # True
_x000D_
_x000D_
Python solution 2 for Bottom Three Unique Values, proposed by Luan Rodrigues:
import pandas as pd
file = "Excel_Challenge_650 - Top 3 Across Columns.xlsx"
df = pd.read_excel(file,usecols='A:E')
a =[list(set(df[i].dropna().to_list())) for i in df.columns]
b = pd.DataFrame([ ", ".join([str(sorted(a[i])[:3][x]) for x in range(3)]) for i in range(len(df.columns))],columns=['Result'])
print(b)
_x000D_
_x000D_
Python solution 3 for Bottom Three Unique Values, proposed by Abdallah Ally:
import pandas as pd
file_path = 'Excel_Challenge_650 - Top 3 Across Columns.xlsx'
df = pd.read_excel(io=file_path, usecols='A:E')
# Perform data manipulation
df = pd.DataFrame(
data ={
'Answer Expected': [
', '.join([str(int(v)) for v in sorted(df[c].dropna().unique())[:3]])
for c in df.columns
]
}
)
df
_x000D_
Solving the challenge of Bottom Three Unique Values with Python in Excel
_x000D_
Python in Excel solution 1 for Bottom Three Unique Values, proposed by Alejandro Campos:
df = xl("A1:E15", headers=True)
result_df = pd.DataFrame([[", ".join(map(str, sorted(df[col].dropna()
.astype(int).unique())[:3]))] for col in df], columns=["Bottom 3 Values"])
_x000D_
_x000D_
Python in Excel solution 2 for Bottom Three Unique Values, proposed by Aditya Kumar Darak 🇮🇳:
df = xl("A1:E15", True)
result = df.apply(
lambda x: ", ".join(x.drop_duplicates().nsmallest(3).astype(int).astype(str))
).values
result
_x000D_
Solving the challenge of Bottom Three Unique Values with R
_x000D_
R solution 1 for Bottom Three Unique Values, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/650 Top 3 Across Columns.xlsx"
input = read_excel(path, range = "A1:E15")
test = read_excel(path, range = "G1:G6") %>% pull()
result = input %>%
summarise(across(everything(), ~ paste(sort(unique(na.omit(.)))[1:3], collapse = ", "))) %>%
as.list() %>%
unlist()
all.equal(result, test)
# [1] TRUE
_x000D_
&&