There are 4 persons who can be a candidate to do a task. Assign a single person to every task while balancing the load. In the problem, there are 9 tasks and 4 persons. Hence, 1 person can be assigned 3 tasks and remaining 3 need to be assigned 2 tasks each. If there are 12 tasks, then all need to be assigned 3 tasks each. The answer given in illustrative and your answer may be different from mine.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 465
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Task Assignment Load Balancing with Excel
Excel solution 1 for Task Assignment Load Balancing, proposed by Bo Rydobon 🇹🇭:
=LET(
p,
B2:E10,
u,
UNIQUE(
TOCOL(
p,
3
)
),
t,
REDUCE(
"",
SEQUENCE(
,
ROWS(
p
)
),
LAMBDA(
a,
i,
TOCOL(
a&TOROW(
INDEX(
p,
i,
),
3
)
)
)
),
d,
MAP(
t,
LAMBDA(
x,
STDEV(
LEN(
SUBSTITUTE(
x,
u,
)
)
)
)
),
FILTER(
t,
d=MIN(
d
)
)
)Excel solution 2 for Task Assignment Load Balancing, proposed by Bo Rydobon 🇹🇭:
=share&utm_medium=member_desktop
That is an annoying bug.
=MAP(
SEQUENCE(
9
),
LAMBDA(
i,
LET(
ZZ,
INDEX(
1,
{1}
),
i
)
)
)
=MAP(
SEQUENCE(
9
),
LAMBDA(
i,
LET(
ZZ,
N(
+A2:A3
),
i
)
)
)
=MAP(
SEQUENCE(
9
),
LAMBDA(
i,
LET(
ZZ,
N(
{1}
),
i
)
)
)
=MAP(
SEQUENCE(
9
),
LAMBDA(
i,
LET(
ZZ,
TEXTSPLIT(
{"a"},
1
),
i
)
)
)
=MAP(
SEQUENCE(
9
),
LAMBDA(
i,
LET(
ZZ,
SEQUENCE(
{"1"}
),
i
)
)
)
=MAP(
SEQUENCE(
9
),
LAMBDA(
i,
LET(
ZZ,
EDATE(
+A2:A3,
1
),
i
)
)
)
=MAP(
SEQUENCE(
9
),
LAMBDA(
i,
LET(
ZZ,
EOMONTH(
+A2:A3,
1
),
i
)
)
)
The ZZ variable has nothing to do with i at all but causes MAP to return 1 cell
ZZ can be anything that causes array-in-array?? (I'm not sure what to call this) conflict.
Quick fixed for now is IFS
=LET(
s,
SEQUENCE(
,
4
),
m,
MID(
REDUCE(
,
s,
LAMBDA(
a,
v,
IFS(
1,
TOCOL(
REPLACE(
a,
SEQUENCE(
,
v
),
,
v
)
)
)
)
),
s,
1
),
m
)Excel solution 3 for Task Assignment Load Balancing, proposed by Bo Rydobon 🇹🇭:
=LET(a,A2:E10,v,SORT(UNIQUE(TOCOL(DROP(a,,1),3))),s,SEQUENCE(ROWS(v)),b,SORTBY(a,BYROW(a,LAMBDA(x,IFS(1,SUM((x>0)*100,COUNTIF(a,x)))))),
d,REDUCE(IF(s,{0,""}),SEQUENCE(ROWS(a)),LAMBDA(x,i,LET(d,VSTACK(v,TOCOL(DROP(b,i-1,1),3)),u,GROUPBY(d,d,ROWS,,0),
c,TAKE(x,,1),y,s=XMATCH(0,c*100+IFS(ISNUMBER(XMATCH(v,INDEX(b,i,0))),DROP(u,,1)),1),IF(y,HSTACK(c+y,DROP(x,,1)&" "&INDEX(b,i,1)),x)))),
VSTACK({"Task ID","Person"},DROP(SORT(REDUCE(0,s,LAMBDA(a,i,VSTACK(a,IF({1,0},--TEXTSPLIT(INDEX(d,i,2),," ",1),INDEX(v,i)))))),1)))
Priority by task of fewer people is the same as previous method
4 1 2 3 5 6 8 9 7
Priority by person of fewer tasks based on count of task left after
instead of a fixed count A D C B => 4 4 5 6
Step 1 for task 4
A B C D => 4 6 5 4
only B possible =>B
Step 2 for Task 1
count A B C D without task 4
A B C D => 4 5 5 4
c*100+IFS(ISNUMBER(XMATCH(v,INDEX(b,i,0))),DROP(u,,1))Excel solution 4 for Task Assignment Load Balancing, proposed by Bo Rydobon 🇹🇭:
=LET(a,
A2:E10,
v,
UNIQUE(
TOCOL(
DROP(
a,
,
1
),
3
)
),
u,
SORTBY(
v,
COUNTIF(
a,
v
)
),
s,
SEQUENCE(
ROWS(
u
)
),
b,
SORTBY(a,
BYROW(a,
LAMBDA(x,
SUM(--(x>0))))),
d,
REDUCE(
IF(
s,
{0,
""}
),
SEQUENCE(
ROWS(
a
)
),
LAMBDA(
x,
i,
LET(
c,
TAKE(
x,
,
1
),
y,
s=XMATCH(
0,
IFS(
ISNUMBER(
XMATCH(
u,
INDEX(
b,
i,
0
)
)
),
c
),
1
),
IF(
y,
HSTACK(
c+y,
DROP(
x,
,
1
)&" "&INDEX(
b,
i,
1
)
),
x
)
)
)
),
VSTACK(
G1:H1,
DROP(
SORT(
REDUCE(
0,
s,
LAMBDA(
a,
i,
VSTACK(
a,
IF(
{1,
0},
--TEXTSPLIT(
INDEX(
d,
i,
2
),
,
" ",
1
),
INDEX(
u,
i
)
)
)
)
)
),
1
)
))
u,
SORTBY(
v,
COUNTIF(
a,
v
)
)
Give priority to Person with fewer tasks first.
A,
D with 4 tasks then C with 5 tasks then B with 6 tasks
b,
SORTBY(a,
BYROW(a,
LAMBDA(x,
SUM(--(x>0)))))
Give priority to Taks with fewer Persons first.
Task 4 with 1 persons assign first
Task 7 with 4 persons assign last
y,
s=XMATCH(
0,
IFS(
ISNUMBER(
XMATCH(
u,
INDEX(
b,
i,
0
)
)
),
c
),
1
)Excel solution 5 for Task Assignment Load Balancing, proposed by John V.:
=LET(s,
SORTBY,
r,
B2:E10,
x,
ROWS(
r
),
y,
COLUMNS(
r
),
n,
BYCOL(
N(
r>0
),
SUM
),
c,
s(
HSTACK(
A2:A10,
s(
r&"",
n
)
),
BYROW(
N(
r>0
),
SUM
)
),
t,
TAKE(
c,
,
1
),
b,
DROP(
c,
,
1
),
z,
s(
LEFT(
BYCOL(
r,
CONCAT
)
),
-n
),
SORT(HSTACK(t,
DROP(REDUCE("",
SEQUENCE(
x
),
LAMBDA(a,
v,
LET(i,
INDEX(
b,
v,
),
f,
FILTER(
i,
i>""
),
VSTACK(a,
XLOOKUP(1,
N(XLOOKUP(f,
z,
1+INT(
x/y
)-(SEQUENCE(
,
y
)>MOD(
x,
y
))-BYCOL(
N(
a=z
),
SUM
))>0),
f))))),
1))))Excel solution 6 for Task Assignment Load Balancing, proposed by محمد حلمي:
=REDUCE(
C2,
A2:A10,
LAMBDA(
a,
v,
LET(
b,
B2:E10,
c,
CONCAT(
b
),
e,
--TEXTSPLIT(
CONCAT(
0&IF(
b>0,
A2:A10,
)
),
,
0,
1
),
i,
FILTER(
MID(
c,
SEQUENCE(
LEN(
c
)
),
1
),
v=e
),
u,
XLOOKUP(
i,
a,
a,
1
),
VSTACK(
a,
IFNA(
XLOOKUP(
1,
u,
i
),
@u
)
)
)
)
)
///
=LET(b,
B2:E10,
v,
TOCOL(
b
),
IF(b=0,
"",
WRAPROWS(MAP(SEQUENCE(
ROWS(
v
)
),
LAMBDA(a,
SUM(--(TAKE(
v,
a
)=INDEX(
v,
a
))))),
4)))Excel solution 7 for Task Assignment Load Balancing, proposed by Julian Poeltl:
=LET(P,
B2:E10,
T,
ROWS(
P
),
UP,
UNIQUE(
TOCOL(
P,
3
)
),
CUP,
COUNTA(
UP
),
LL,
ROUNDDOWN(
T/CUP,
0
),
M,
BYROW(
L_MixLargeArraysColumnsbyColumntogehter_AllPossibilities_INT(
TRANSPOSE(
P
)
),
LAMBDA(
A,
CONCAT(
A
)
)
),
R,
MAP(M,
LAMBDA(A,
(MIN(
T-LEN(
SUBSTITUTE(
A,
UP,
""
)
)
)=LL)*(MAX(
T-LEN(
SUBSTITUTE(
A,
UP,
""
)
)
)=(LL+1)))),
C,
CONCAT(
FILTER(
M,
R
)
),
WRAPCOLS(
MID(
C,
SEQUENCE(
LEN(
C
)
),
1
),
T
))
L_MixLargeArraysColumnsbyColumntogehter_AllPossibilities_INT:
=LAMBDA(
Array1,
[Array2],
LET(
z,
IF(
ISOMITTED(
Array2
),
Array1,
HSTACK(
Array1,
Array2
)
),
D,
"|",
c,
SEQUENCE(
,
COLUMNS(
z
)
),
re,
REDUCE(
"",
c,
LAMBDA(
A,
n,
UNIQUE(
TOCOL(
A&D&TOROW(
INDEX(
z,
,
n
),
3
)
)
)
)
),
TEXTBEFORE(
TEXTAFTER(
re,
D,
c
),
D,
,
,
1
)
)
)Excel solution 8 for Task Assignment Load Balancing, proposed by Julian Poeltl:
=LET(P,
B2:E10,
T,
ROWS(
P
),
UP,
UNIQUE(
TOCOL(
P,
3
)
),
CUP,
COUNTA(
UP
),
LL,
ROUNDDOWN(
T/CUP,
0
),
M,
BYROW(
L_MixLargeArraysColumnsbyColumntogehter_AllPossibilities_INT(
TRANSPOSE(
P
)
),
LAMBDA(
A,
CONCAT(
A
)
)
),
R,
MAP(M,
LAMBDA(A,
(MIN(
T-LEN(
SUBSTITUTE(
A,
UP,
""
)
)
)=LL)*(MAX(
T-LEN(
SUBSTITUTE(
A,
UP,
""
)
)
)=(LL+1)))),
C,
CONCAT(
FILTER(
M,
R
)
),
TAKE(
WRAPCOLS(
MID(
C,
SEQUENCE(
LEN(
C
)
),
1
),
T
),
,
1
))
L_MixLargeArraysColumnsbyColumntogehter_AllPossibilities_INT:
=LAMBDA(
Array1,
[Array2],
LET(
z,
IF(
ISOMITTED(
Array2
),
Array1,
HSTACK(
Array1,
Array2
)
),
D,
"|",
c,
SEQUENCE(
,
COLUMNS(
z
)
),
re,
REDUCE(
"",
c,
LAMBDA(
A,
n,
UNIQUE(
TOCOL(
A&D&TOROW(
INDEX(
z,
,
n
),
3
)
)
)
)
),
TEXTBEFORE(
TEXTAFTER(
re,
D,
c
),
D,
,
,
1
)
)
)Excel solution 9 for Task Assignment Load Balancing, proposed by Timothée BLIOT:
=LET(A,
A2:A10,
B,
B2:E10,
F,
LAMBDA(
n,
n&FILTER(
INDEX(
B,
n,
),
INDEX(
B,
n,
)<>0
)
),
C,
REDUCE(
TOCOL(
F(
1
)
),
DROP(
A,
1
),
LAMBDA(
w,
v,
TOCOL(
w&F(
v
)
)
)
),
D,
FILTER(C,
MAP(C,
LAMBDA(x,
PRODUCT(--(SORT(
LEN(
x
)-LEN(
SUBSTITUTE(
x,
{"A";"B";"C";"D"},
""
)
)
)={2;2;2;3}))))),
WRAPROWS(MID(INDEX(D,
(ROWS(
D
)*RAND())+1),
SEQUENCE(
18
),
1),
2))Excel solution 10 for Task Assignment Load Balancing, proposed by Sunny Baggu:
=LET(
t,
A2:A10,
rng,
B2:E10,
_a,
SORT(
UNIQUE(
TOCOL(
rng,
1
)
)
),
_b,
MAP(
_a,
LAMBDA(
x,
SUM(
N(
& rng = x
)
)
)
),
_c,
TOROW(
SORTBY(
_a,
_b,
1
)
),
_d,
{3,
2,
2,
2},
n,
DROP(
REDUCE(
"",
SEQUENCE(
4
),
LAMBDA(
a,
v,
VSTACK(
a,
LET(
_e,
TOCOL(
IF(
rng = INDEX(
_c,
,
v
),
t,
x
),
3
),
_f,
TAKE(
FILTER(
_e,
ISNA(
XMATCH(
_e,
a
)
)
),
INDEX(
_d,
1,
v
)
),
_f
)
)
)
),
1
),
p,
DROP(
REDUCE(
"",
SEQUENCE(
4
),
LAMBDA(
x,
y,
VSTACK(
x,
IF(
SEQUENCE(
INDEX(
_d,
1,
y
)
),
INDEX(
_c,
,
y
)
)
)
)
),
1
),
SORT(
HSTACK(
n,
p
),
1
)
)Excel solution 11 for Task Assignment Load Balancing, proposed by LEONARD OCHEA 🇷🇴:
=LET(m,
B2:E10,
n,
ROWS(
m
),
r,
TOROW(
REDUCE(
"",
n-SEQUENCE(
n
)+1,
LAMBDA(
a,
b,
TOROW(
IFERROR(
TOCOL(
INDEX(
m,
b,
),
3
),
""
)&""&TOROW(
a
)
)
)
)
),
t,
MID(
r,
A2:A10,
1
),
s,
BYCOL(t,
LAMBDA(c,
LET(f,
GROUPBY(
c,
c,
COUNTA,
,
0
),
(ROWS(
f
)=4)*AND(
TAKE(
f,
,
-1
)>1
)))),
FILTER(
t,
s
))Excel solution 12 for Task Assignment Load Balancing, proposed by Tyler Cameron:
=HSTACK(A2:A10,
DROP(REDUCE("",
BYROW(
B2:F10,
LAMBDA(
t,
CONCAT(
t
)
)
),
LAMBDA(x,
y,
LET(a,
TOROW(
MID(
y,
SEQUENCE(
LEN(
y
)
),
1
)
),
VSTACK(x,
INDEX(SORTBY(a,
TOROW(BYCOL(--(x=a),
LAMBDA(
v,
SUM(
v
)
)))),
,
1))))),
1))Solving the challenge of Task Assignment Load Balancing with Python in Excel
Python in Excel solution 1 for Task Assignment Load Balancing, proposed by Owen Price:
Here's my Python in Excel solution:
&&