Create a Recruitment plan from the Projected Headcount. 1st hire = 1st month of projected headcount. e.g. Clerk’s count start on Mar-22 (2 clerks) next hire Jun-22 (6 -2= 4 clerks) Dynamic array function allowed, but Extra marks for Legacy Solutions or PowerQuery Solutions. Edit: Note For accountant: March 22 is 2 hires instead of 1
📌 Challenge Details and Links
Challenge Number: 50
Challenge Difficulty: ⭐⭐
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Creating a Recruitment Plan from Headcount with Power Query
Power Query solution 1 for Creating a Recruitment Plan from Headcount, proposed by Omid Motamedisedeh:
let
Source = Excel.CurrentWorkbook(){[Name = "HeadCount"]}[Content],
Un = Table.Distinct(
Table.UnpivotOtherColumns(Source, {"Position"}, "Month", "Value"),
{"Position", "Value"}
),
Add = Table.AddColumn(
Un,
"Hire",
each [Value]
- (
try
Table.Last(Table.SelectRows(Un, (x) => x[Value] < _[Value] and x[Position] = _[Position]))[
Value
]
otherwise
0
)
),
remove = Table.RemoveColumns(Add, {"Value"})
in
removePower Query solution 2 for Creating a Recruitment Plan from Headcount, proposed by Zoran Milokanović:
let
Source = Table.UnpivotOtherColumns(
Excel.CurrentWorkbook(){[Name = "HeadCount"]}[Content],
{"Position"},
"Month",
"Hire"
),
R = Table.ToRows(Source),
S = Table.FromRows(
List.TransformMany(
R,
each
let
c = List.PositionOf(R, _),
P = R{c - 1}
in
{{0}, {0}, {P{2}}, {}}{List.PositionOf({c = 0, _{0} <> P{0}, _{2} <> P{2}, true}, true)},
(i, _) => {i{0}, i{1}} & {i{2} - _}
),
Table.ColumnNames(Source)
)
in
SPower Query solution 3 for Creating a Recruitment Plan from Headcount, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "HeadCount"]}[Content],
B = Table.UnpivotOtherColumns(A, {"Position"}, "Month", "CHire"),
C = Table.Group(
B,
{"Position"},
{
"All",
(w) =>
Table.AddColumn(
w,
"Hire",
each try [CHire] - w[CHire]{List.PositionOf(w[Month], [Month]) - 1} otherwise [CHire]
)
}
),
D = Table.ExpandTableColumn(C, "All", {"Month", "Hire"}),
E = Table.SelectRows(D, each [Hire] > 0)
in
EPower Query solution 4 for Creating a Recruitment Plan from Headcount, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "HeadCount"]}[Content],
Group = Table.Group(
Source,
{"Position"},
{
{
"A",
each
let
a = _,
b = Table.DemoteHeaders(a),
c = List.Select({{"B", 0}} & List.Skip(Table.ToColumns(b)), each _{1} <> null),
d = List.Transform({1 .. List.Count(c) - 1}, each {c{_}{0}, c{_}{1} - c{_ - 1}{1}}),
e = List.Select(d, each _{1} <> 0),
f = Table.FromRows(e, {"Moth", "Hire"})
in
f
}
}
),
Sol = Table.ExpandTableColumn(Group, "A", Table.ColumnNames(Group[A]{0}))
in
SolPower Query solution 5 for Creating a Recruitment Plan from Headcount, proposed by Brian Julius:
let
Source = Excel.CurrentWorkbook(){[Name = "HeadCount"]}[Content],
UnpivOth = Table.UnpivotOtherColumns(Source, {"Position"}, "Attribute", "Value"),
Group = Table.Group(UnpivOth, {"Position"}, {{"All", each _}}),
AddPrev = Table.AddColumn(
Group,
"Prev",
each [
a = [All],
b = {"Position", "Month", "Value", "Prev"},
c = {0} & List.RemoveLastN(a[Value], 1),
d = Table.ToColumns(a) & {c},
e = Table.FromColumns(d, b),
f = Table.AddColumn(e, "Hire", each [Value] - [Prev]),
g = Table.SelectRows(f, each [Hire] <> 0)
][g]
),
Clean = Table.SelectColumns(AddPrev, "Prev"),
Exp = Table.ExpandTableColumn(Clean, "Prev", {"Position", "Month", "Hire"})
in
ExpPower Query solution 6 for Creating a Recruitment Plan from Headcount, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S = Excel.CurrentWorkbook(){[Name = "HeadCount"]}[Content],
A = Table.UnpivotOtherColumns(S, {"Position"}, "Month", "Hire"),
B = Table.Group(A, {"Position"}, {{"T", each _}}),
F = (T) =>
let
a = Table.AddIndexColumn(T, "I", 0, 1),
b = Table.AddColumn(a, "Hire.", each try [Hire] - a[Hire]{[I] - 1} otherwise [Hire]),
c = Table.SelectRows(b, each ([#"Hire."] <> 0)),
d = Table.SelectColumns(c, {"Month", "Hire."})
in
d,
C = Table.AddColumn(B, "F", each F([T])),
D = Table.SelectColumns(C, {"Position", "F"}),
E = Table.ExpandTableColumn(D, "F", {"Month", "Hire."}, {"Month", "Hire."})
in
EPower Query solution 7 for Creating a Recruitment Plan from Headcount, proposed by Peter Krkos:
let
Ad_H = Table.AddColumn(
Source,
"H",
each [
a = List.Accumulate(
List.Skip(Record.FieldNames(_)),
{{null, null, 0}},
(st, cur) =>
if Record.Field(_, cur) = List.Last(st){2} or Record.Field(_, cur) is null then
st
else
st & {{cur, Record.Field(_, cur) - List.Last(st){2}, Record.Field(_, cur)}}
),
b = List.Transform(List.Skip(a), (x) => {[Position]} & List.FirstN(x, 2))
][b],
type list
),
Combined = Table.FromRows(
List.Combine(Ad_H[H]),
type table [Position = text, Month = text, Hire = Int64.Type]
)
in
CombinedPower Query solution 8 for Creating a Recruitment Plan from Headcount, proposed by Nelson Mwangi:
let
Source = Excel.CurrentWorkbook(){[Name = "HeadCount"]}[Content],
Unpivot = Table.UnpivotOtherColumns(Source, {"Position"}, "Month", "H"),
Group = Table.Group(
Unpivot,
{"Position"},
{
{
"All",
each
let
AddIndex = Table.AddIndexColumn(_, "Index", 0, 1),
AddPrevious = Table.AddColumn(
AddIndex,
"Hire",
each if [Index] = 0 then [H] else [H] - AddIndex[H]{[Index] - 1}
),
Filter = Table.SelectRows(AddPrevious, each [Hire] <> 0)
in
Filter
}
}
),
Expand = Table.ExpandTableColumn(Group, "All", {"Month", "Hire"})
in
ExpandSolving the challenge of Creating a Recruitment Plan from Headcount with Excel
Excel solution 1 for Creating a Recruitment Plan from Headcount, proposed by Bo Rydobon 🇹🇭:
=LET(
n,
C4:I8,
y,
n-IFNA(
HSTACK(
0,
n),
),
L,
LAMBDA(
x,
TOCOL(
IFS(
y,
x),
3)),
HSTACK(
L(
B4:B8),
L(
C3:I3),
L(
y)))Excel solution 2 for Creating a Recruitment Plan from Headcount, proposed by Kris Jaganah:
=LET(
a,
B4:B8,
b,
C3:I3,
c,
C4:I8,
d,
TOCOL(
a&"-"&b),
e,
TEXTSPLIT(
d,
"-"),
f,
--TEXTAFTER(
d,
"-"),
g,
INDEX(
c,
MATCH(
e,
a,
0),
MATCH(
f,
--b,
0)),
h,
IF(
e=VSTACK(
"",
DROP(
e,
-1)),
g-VSTACK(
@g,
DROP(
g,
-1)),
g),
FILTER(
HSTACK(
e,
f,
h),
h>0))Excel solution 3 for Creating a Recruitment Plan from Headcount, proposed by Julian Poeltl:
=LET(
T,
C4:I8,
H,
TOCOL(
T-IFNA(
HSTACK(
0,
DROP(
T,
,
-1)),
0)),
W,
WRAPROWS(
TEXTSPLIT(
TEXTJOIN(
";",
,
FILTER(
HSTACK(
TOCOL(
B4:B8&";"&C3:I3),
H),
H>0)),
";"),
3),
VSTACK(
HSTACK(
"Position",
"Month",
"Hire"),
IFERROR(
W*1,
W)))Excel solution 4 for Creating a Recruitment Plan from Headcount, proposed by Hussein SATOUR:
=LET(
S,
SEQUENCE,
C,
TOCOL,
a,
HSTACK(
C(
B4:B8&IF(
S(
,
7),
"")),
C(
C3:I3&IF(
S(
5),
"")),
C(
C4:I8-IF(
ISTEXT(
B4:H8),
0,
B4:H8))),
FILTER(
a,
INDEX(
a,
,
3)>0))Excel solution 5 for Creating a Recruitment Plan from Headcount, proposed by Oscar Mendez Roca Farell:
=LET(
n,
C4:I8-N(
+B4:H8),
TEXTSPLIT(
CONCAT(
TOCOL(
IFS(
n,
B4:B8&"|"&C3:I3&"|"&n),
2)&"_"),
"|",
"_",
1))Excel solution 6 for Creating a Recruitment Plan from Headcount, proposed by Hamidi Hamid:
=LET(
x,
TOCOL(
DROP(
IFERROR(
-B4:I8+C4:I8,
C4:I8),
,
-1)),
y,
TOCOL(
IFNA(
B4:B8,
C3:I3)),
z,
TOCOL(
IFNA(
C3:I3,
B4:B8)),
t,
VSTACK(
{"Position",
"Month",
"Hire"},
HSTACK(
y,
z,
x)),
FILTER(
t,
TAKE(
t,
,
-1)>0))Excel solution 7 for Creating a Recruitment Plan from Headcount, proposed by ferhat CK:
=LET(
r,
DROP(
REDUCE(
0,
A2:A6,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
a,
XMATCH(
y,
A2:A6),
w,
CHOOSEROWS(
B2:H6,
a),
b,
UNIQUE(
w,
TRUE),
c,
XLOOKUP(
b,
w,
B1:H1),
IFERROR(
HSTACK(
y,
TOCOL(
c),
TOCOL(
b-HSTACK(
0,
DROP(
b,
,
-1)))),
y))))),
1),
FILTER(
r,
TAKE(
r,
,
-1)))Excel solution 8 for Creating a Recruitment Plan from Headcount, proposed by Ankur Sharma:
=TEXTSPLIT(
TEXTJOIN(
", ",
,
BYROW(
B4:I8,
LAMBDA(
r,
LET(
Po,
TAKE(
r,
,
1),
J,
IF(
CHOOSECOLS(
r,
2) > 0,
Po & ":" & C3 & ":" & CHOOSECOLS(
r,
2),
""),
HC_2,
DROP(
r,
,
2),
HC_1,
DROP(
DROP(
r,
,
1),
,
-1),
D,
HC_2 - HC_1,
TEXTJOIN(
", ",
,
J,
IF(
D > 0,
Po & ":" & D3:I3 & ":" & D,
"")))))),
":",
", ")Excel solution 9 for Creating a Recruitment Plan from Headcount, proposed by Imam Hambali:
=LET(
v,
HeadCount[[Jan-22]:[Jul-22]]-HSTACK(
HeadCount[Jan-22]*0,
DROP(
HeadCount[[Jan-22]:[Jul-22]],
,
-1)),
l,
LAMBDA(
x,
TOCOL(
IF(
v>0,
x,
NA()),
3)),
VSTACK(
Recruitment[
hashtag
#Headers],
HSTACK(
l(
HeadCount[Position]),
l(
HeadCount[[
hashtag
#Headers],
[Jan-22]:[Jul-22]]),
l(
v)))
)Excel solution 10 for Creating a Recruitment Plan from Headcount, proposed by Eddy Wijaya:
=LET(
adj_d,
BYROW(
B4:I8,
LAMBDA(
r,
TEXTJOIN(
",",
,
MAP(
r,
LAMBDA(
m,
IFERROR(
m-OFFSET(
m,
,
-1),
m)))))),
REDUCE(
K3:M3,
adj_d,
LAMBDA(
a,
v,
VSTACK(
a,
LET(
i,
TEXTBEFORE(
v,
","),
n,
TEXTSPLIT(
TEXTAFTER(
v,
",",
1),
,
","),
c,
SEQUENCE(
COUNTA(
n)),
m,
IFNA(
HSTACK(
i,
INDEX(
C3:I3,
,
c),
--n),
i),
FILTER(
m,
TAKE(
m,
,
-1)>0))))))Solving the challenge of Creating a Recruitment Plan from Headcount with Python
Python solution 1 for Creating a Recruitment Plan from Headcount, proposed by Konrad Gryczan, PhD:
import pandas as pd
from pandas.tseries.offsets import MonthEnd
path = "files/Excel Challenge Nov 3rd.xlsx"
input = pd.read_excel(path, usecols="B:I", skiprows=2, nrows=6)
test = pd.read_excel(path, usecols="K:M", skiprows=2, nrows=12).rename(columns=lambda x: x.replace('.1', ''))
input = input.melt(id_vars=input.columns[0], var_name="Month", value_name="HC")
input["HC"] = input["HC"].fillna(0)
input["Month"] = pd.to_datetime(input["Month"], format='%b-%y') + MonthEnd(0)
input["Hire"] = input.groupby("Position")["HC"].diff().fillna(input["HC"]).astype(int)
result = input[input["Hire"] > 0].drop(columns=["HC"]).sort_values(by=["Position", "Month"]).reset_index(drop=True)
print(result.equals(test)) # False, one value mistaken in challenge.Solving the challenge of Creating a Recruitment Plan from Headcount with R
R solution 1 for Creating a Recruitment Plan from Headcount, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "files/Excel Challenge Nov 3rd.xlsx"
input = read_excel(path, range = "B3:I8")
test = read_excel(path, range = "K3:M15")
result = input %>%
pivot_longer(cols = -c(1), names_to = "Month", values_to = "HC") %>%
replace_na(list(HC = 0)) %>%
mutate(Month = my(Month)) %>%
mutate(Hire = HC - lag(HC, default = 0), .by = Position) %>%
filter(Hire > 0) %>%
select(-HC)
all.equal(result, test, check.attributes = FALSE)
# False, one value is different. Mistake in construction of challenge.