Randomly select a staff member from each department.
📌 Challenge Details and Links
Challenge Number: 93
Challenge Difficulty: ⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Random Selection! Part 1 with Power Query
Power Query solution 1 for Random Selection! Part 1, proposed by Omid Motamedisedeh:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Result = Table.Distinct(
Table.Sort(Source, (x, y) => Value.Compare(Number.Random(), Number.Random())),
{"Department"}
)
in
ResultPower Query solution 2 for Random Selection! Part 1, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
S = Table.Group(
Source,
"Department",
{"Year", each List.Sort([Staff ID], each Number.Random()){0}}
)
in
SPower Query solution 3 for Random Selection! Part 1, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
S = Table.Group(
Source,
"Department",
{"Year", each [Staff ID]{Int64.From(Number.RandomBetween(0, List.Count([Staff ID]) - 1))}}
)
in
SPower Query solution 4 for Random Selection! Part 1, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
S = Table.Group(
Source,
"Department",
{"Year", each List.Sort([Staff ID], each Number.Random()){0}}
)
in
SPower Query solution 5 for Random Selection! Part 1, proposed by Brian Julius:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
ColNames = Table.ColumnNames(Source) & {"Random"},
Cols = Table.ToColumns(Source) & {List.Random(Table.RowCount(Source), 2112)},
AddRandList = Table.FromColumns(Cols, ColNames),
Group = Table.ExpandTableColumn(
Table.Group(
AddRandList,
{"Department"},
{{"MaxRand", each List.Max([Random]), type number}, {"All", each _}}
),
"All",
{"Staff ID", "Random"},
{"Staff ID", "Random"}
),
Filter = Table.SelectColumns(
Table.SelectRows(Group, each [MaxRand] = [Random]),
{"Department", "Staff ID"}
)
in
FilterPower Query solution 6 for Random Selection! Part 1, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
a = Table.Group(S,{"Department"},{"G", each _})[[G]],
b = Table.TransformColumns(a,{"G", each Table.AddColumn(Table.AddIndexColumn(_,"I"),"R", (x)=> Number.Round(Number.RandomBetween(0,Table.RowCount(_))))}),
c = Table.TransformColumns(b,{"G", each Table.MaxN(_,"R",1)[[Department],[Staff ID]]}),
Sol = Table.RenameColumns(Table.Combine(c[G]),{"Staff ID","Year"})
in
SolPower Query solution 7 for Random Selection! Part 1, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Rand = Table.AddColumn(Source, "R", each Number.Random()),
Return = Table.Group(Rand, "Department", {"Staff ID", each Table.Sort(_, "R")[Staff ID]{0}})
in
ReturnPower Query solution 8 for Random Selection! Part 1, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Sol = Table.Group(Source, {"Department"}, {{"Year", each
let
a = [Staff ID],
b = Number.Round(Number.RandomBetween(0, List.Count(a)-1)),
c = a{b}
in c}})
in
SolPower Query solution 9 for Random Selection! Part 1, proposed by Yaroslav Drohomyretskyi:
let
Source = Excel.CurrentWorkbook(){[Name = "Таблиця1"]}[Content],
Result = Table.Group(
Source,
{"Department"},
{{"Random ID", each _[Staff ID]{Int64.From(Number.RandomBetween(0, Table.RowCount(_) - 1))}}}
)
in
ResultPower Query solution 10 for Random Selection! Part 1, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
A = Table.TransformColumnTypes(Source,{{"Department", type text}, {"Staff ID", type text}}),
B = Table.Group(A, {"Department"}, {{"Tbl", each _, type table [Department=nullable text, Staff ID=nullable text]}}),
C = Table.AddColumn(B, "Staff ID", each let
a=List.Transform([Tbl][Staff ID], each Text.AfterDelimiter(_,"_")),
b=List.Count(a)-1,
c=Number.Round(Number.RandomBetween(0,b)),
d=[Tbl][Staff ID]{c}
in
d),
D = Table.SelectColumns(C,{"Department", "Staff ID"})
in
DPower Query solution 11 for Random Selection! Part 1, proposed by Ahmed Ariem:
let
Source = Excel.CurrentWorkbook(){[Name = "Таблица1"]}[Content],
Group = Table.Group(
Source,
{"Department"},
{
{
"Rand",
(x) =>
[
a = Table.AddColumn(x, "rn", each Number.RandomBetween(1000, 10000)),
b = Table.FromRecords({Table.Max(a, "rn")})[Staff ID]
][b]{0}
}
}
)
in
GroupSolving the challenge of Random Selection! Part 1 with Excel
Excel solution 1 for Random Selection! Part 1, proposed by محمد حلمي:
=LET(
b,
B3:B20,
u,
UNIQUE(
b
),
HSTACK(
u,
MAP(
u,
LAMBDA(
a,
INDEX(
FILTER(
C3:C20,
b=a
),
RANDBETWEEN(
1,
COUNTIF(
b,
a
)
)
)
)
)
)
)Excel solution 2 for Random Selection! Part 1, proposed by Aditya Kumar Darak 🇮🇳:
=GROUPBY(
B2:B20,
C2:C20,
LAMBDA(
a,
@SORTBY(
a,
RANDARRAY(
ROWS(
a
)
)
)
),
3,
0
)Excel solution 3 for Random Selection! Part 1, proposed by Oscar Mendez Roca Farell:
=LET(
d,
B3:B20,
u,
UNIQUE(
d
),
HSTACK(
u,
MAP(
COUNTIF(
d,
u
),
u,
LAMBDA(
a,
b,
INDEX(
FILTER(
C3:C20,
d=b
),
RANDBETWEEN(
1,
a
)
)
)
)
)
)Excel solution 4 for Random Selection! Part 1, proposed by Julian Poeltl:
=LET(
D,
B3:B20,
I,
C3:C20,
U,
UNIQUE(
D
),
HSTACK(
U,
MAP(
U,
LAMBDA(
A,
LET(
F,
FILTER(
I,
D=A
),
INDEX(
F,
RANDBETWEEN(
1,
ROWS(
F
)
)
)
)
)
)
)
)Excel solution 5 for Random Selection! Part 1, proposed by John Jairo Vergara Domínguez:
=LET(
d,
B3:B20,
u,
UNIQUE(
d
),
HSTACK(
u,
MAP(
u,
LAMBDA(
x,
@TOCOL(
SORTBY(
IFS(
d=x,
C3:C20
),
RANDARRAY(
ROWS(
d
)
)
),
2
)
)
)
)
)Excel solution 6 for Random Selection! Part 1, proposed by John Jairo Vergara Domínguez:
=GROUPBY(
B2:B20,
C2:C20,
LAMBDA(
x,
@SORTBY(
x,
RANDARRAY(
ROWS(
x
)
)
)
),
3,
0
)Excel solution 7 for Random Selection! Part 1, proposed by Imam Hambali:
=LET( u,
UNIQUE(
B3:B20
), s,
SORTBY(
B3:C20,
RANDARRAY(
ROWS(
B3:B20
)
)
), HSTACK(
u,
XLOOKUP(
u,
TAKE(
s,
,
1
),
TAKE(
s,
,
-1
)
)
))Excel solution 8 for Random Selection! Part 1, proposed by Sunny Baggu:
=LET( t,
B3:C20, r,
ROWS(
t
), _a,
SORTBY(
t,
RANDARRAY(
r,
,
,
r,
1
)
), INDEX(
_a,
XMATCH(
UNIQUE(
TAKE(
t,
,
1
)
),
TAKE(
_a,
,
1
)
),
{1,
2}
))Excel solution 9 for Random Selection! Part 1, proposed by abdelaziz allam:
=HSTACK(
UNIQUE(
B3:B20
),
MAP(
UNIQUE(
B3:B20
),
LAMBDA(
a,
CHOOSEROWS(
FILTER(
C3:C20,
B3:B20=a
),
RANDBETWEEN(
1,
COUNTA(
FILTER(
C3:C20,
B3:B20=a
)
)
)
)
)
)
)Excel solution 10 for Random Selection! Part 1, proposed by Andy Heybruch:
=LET( _rnd,
RANDARRAY(
18
), _randlist,
SORTBY(
B3:C20,
_rnd
), _dept,
UNIQUE(
B3:B20
), HSTACK(
_dept,
XLOOKUP(
_dept,
TAKE(
_randlist,
,
1
),
TAKE(
_randlist,
,
-1
)
)
)
)Excel solution 11 for Random Selection! Part 1, proposed by Asheesh Pahwa:
=DROP(
REDUCE(
"",
UNIQUE(
B3:B20
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
f,
FILTER(
C3:C20,
B3:B20=y
),
s,
SEQUENCE(
ROWS(
f
)
),
r,
RANDBETWEEN(
MIN(
s
),
MAX(
s
)
),
HSTACK(
y,
INDEX(
f,
@r,
)
)
)
)
)
),
1
)Excel solution 12 for Random Selection! Part 1, proposed by Bilal Mahmoud kh.:
=VSTACK(
{"Department",
"StaffID"},
HSTACK(
UNIQUE(
B3:B20
),
MAP(
UNIQUE(
B3:B20
),
LAMBDA(
x,
LET(
a,
FILTER(
C3:C20,
B3:B20=x
),
CHOOSEROWS(
a,
RANDBETWEEN(
1,
COUNTA(
a
)
)
)
)
)
)
)
)Excel solution 13 for Random Selection! Part 1, proposed by Eddy Wijaya:
=VSTACK(
E2:F2,
LET( raw,
B3:C20, dept,
CHOOSECOLS(
raw,
1
), u_dept,
UNIQUE(
dept
), adjTab,
SORT(
HSTACK(
raw,
RANDARRAY(
ROWS(
raw
)
)
),
3,
1
), HSTACK(
u_dept,
VLOOKUP(
u_dept,
adjTab,
2,
0
)
)
)
)Excel solution 14 for Random Selection! Part 1, proposed by El Badlis Mohd Marzudin:
=REDUCE(
B2:C2,
UNIQUE(
B3:B20
),
LAMBDA(
v,
w,
VSTACK(
v,
LET(
a,
FILTER(
B3:C20,
B3:B20=w
),
r,
ROWS(
a
),
b,
XLOOKUP(
RANDBETWEEN(
1,
r
),
SEQUENCE(
r
),
a
),
b
)
)
)
)Excel solution 15 for Random Selection! Part 1, proposed by ferhat CK:
=LET(
a,
UNIQUE(
B3:B20
),
b,
B3:B20,
c,
C3:C20,
HSTACK(
a, BYROW(
a,
LAMBDA(
x,
CHOOSEROWS(
FILTER(
c,
b=x
),
RANDBETWEEN(
1,
COUNTA(
FILTER(
c,
b=x
)
)
)
)
)
)
)
)Excel solution 16 for Random Selection! Part 1, proposed by Gerson Pineda:
=LET(
d,
B3:B20,
u,
UNIQUE(
d
),
HSTACK(
u,
MAP(
u,
LAMBDA(
x,
INDEX(
FILTER(
C3:C20,
d=x
),
RANDBETWEEN(
1,
COUNTIF(
d,
x
)
)
)
)
)
)
)Excel solution 17 for Random Selection! Part 1, proposed by Hamidi Hamid:
=LET(
t,
UNIQUE(
B3:B20
),
x,
DROP(
IFERROR(
REDUCE(
,
VSTACK(
"",
MAP(
t,
LAMBDA(
a,
TEXTJOIN(
"-",
,
FILTER(
C3:C20,
B3:B20=a
)
)
)
)
),
LAMBDA(
a,
b,
VSTACK(
a,
TEXTSPLIT(
b,
"-",
,
1
)
)
)
),
0
),
1
),
y,
BYROW(
x,
LAMBDA(
a,
SUM(
IF(
a<>"",
1,
0
)
)
)
),
w,
HSTACK(
t,
x
),
z,
NOT(
ISERROR(
MAP(
x,
LAMBDA(
a,
IF(
a<>0,
"",
COLUMN(
a
)*1
)
)
)
)
)*1,
s,
BYROW(
z,
LAMBDA(
a,
RANDBETWEEN(
1,
SUM(
a
)
)
)
)+1,
d,
MAP(
t,
w,
s,
LAMBDA(
a,
b,
c,
VLOOKUP(
a,
w,
c,
0
)
)
),
HSTACK(
t,
TAKE(
d,
,
1
)
)
)Excel solution 18 for Random Selection! Part 1, proposed by Hussein SATOUR:
=LET(a,SORTBY(B3:C20,SORTBY(SEQUENCE(18),RANDARRAY(18))),XLOOKUP(E3:E7,INDEX(a,,1),INDEX(a,,2)))Excel solution 19 for Random Selection! Part 1, proposed by Md. Zohurul Islam:
=LET( header,
B2:C2, x,
B3:B20, y,
UNIQUE(
x
), z,
B3:C20, Num,
COUNTA(
x
), srtArray,
RANDARRAY(
Num,
1,
1,
Num,
TRUE
), sortedArray,
SORTBY(
z,
srtArray
), LokupArray,
CHOOSECOLS(
sortedArray,
1
), RetrnArray,
CHOOSECOLS(
sortedArray,
-1
), result,
XLOOKUP(
y,
LokupArray,
RetrnArray
), output,
HSTACK(
y,
result
), Final,
VSTACK(
header,
output
), Final)Excel solution 20 for Random Selection! Part 1, proposed by Pieter de B.:
=LET(
a,
B3:C20,
b,
SORTBY(
a,
RANDARRAY(
ROWS(
a
)
)
),
u,
UNIQUE(
TAKE(
a,
,
1
)
),
HSTACK(
u,
VLOOKUP(
u,
b,
2,
)
)
)Excel solution 21 for Random Selection! Part 1, proposed by Rick Rothstein:
=LET(
u,
UNIQUE(
B2:B20
),
HSTACK(
u,
MAP(
u,
LAMBDA(
x,
LET(
f,
FILTER(
C2:C20,
B2:B20=x
),
INDEX(
f,
RANDBETWEEN(
1,
COUNTA(
f
)
)
)
)
)
)
)
)Solving the challenge of Random Selection! Part 1 with Python
Python solution 1 for Random Selection! Part 1, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "CH-093 Random Selection.xlsx"
input = pd.read_excel(path, usecols= "B:C", skiprows=1)
result = input.groupby("Department").apply(lambda x: x.sample(1)).reset_index(drop=True)
Solving the challenge of Random Selection! Part 1 with Python in Excel
Python in Excel solution 1 for Random Selection! Part 1, proposed by Alejandro Campos:
df = xl("B2:C20", headers=True).sample(frac=1, random_state=1).drop_duplicates('Department').reset_index(drop=True)
df['Department'] = pd.Categorical(df['Department'], categories=['HR', 'Marketing', 'IT', 'Production', 'R&D'], ordered=True)
df = df.sort_values('Department').reset_index(drop=True)Solving the challenge of Random Selection! Part 1 with R
R solution 1 for Random Selection! Part 1, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "files/CH-093 Random Selection.xlsx"
input = read_excel(path, range = "B2:C20")
test = read_excel(path, range = "E2:F7")
result = input %>%
slice_sample(n = 1, by = Department)
# A tibble: 5 × 2
# Department `Staff ID`
#
# 1 HR S_01
# 2 Marketing S_03
# 3 IT S_10
# 4 Production S_16
# 5 R&D S_15 Solving the challenge of Random Selection! Part 1 with Google Sheets
Google Sheets solution 1 for Random Selection! Part 1, proposed by Milan Shrimali:
GOOGLESHEETS SOLUTON :
=map(unique(B3:B20),lambda(x,hstack(x,let(a,arrayformula(filter($C$3:$C$20,$B$3:$B$20=x)),chooserows(sort(a,RANDARRAY(counta(a)),1),1)))))