How to create work shifts based on these conditions?

PhBarreto · Sep 18, 2023

Hello guys!
In an Excel spreadsheet, I have two sheets, 'Workers' and 'Shift'.
I need one worker from each sector based on the 'Workers' sheet to be scheduled per week (52 weeks) if there is more than one.
If there is only one consultant, he must be scheduled weekly, if not, I would like the worker to be repeated after everyone has worked their weekly shifts.
Are there any formulas so I can achieve this result?
Thanks in advance!

Sheet 'Workers'

Plantão Semanal - Projeto Klabin.xlsx

A

B

C

D

1

Sequence

Workers

Sector

Shift

2

1

Name 1

Sector 1

Yes

3

2

Name 2

Sector 1

Yes

4

3

Name 3

Sector 1

Yes

5

4

Name 4

Sector 1

No

6

5

Name 5

Sector 1

No

7

1

Name 6

Sector 2

Yes

8

2

Name 7

Sector 2

Yes

9

3

Name 8

Sector 2

Yes

10

4

Name 9

Sector 2

No

11

5

Name 10

Sector 2

Yes

12

6

Name 11

Sector 2

Yes

13

1

Name 12

Sector 3

No

14

1

Name 13

Sector 4

Yes

15

2

Name 14

Sector 4

Yes

16

3

Name 15

Sector 4

Yes

17

1

Name 16

Sector 5

No

18

1

Name 17

Sector 6

Yes

19

1

Name 18

Sector 7

Yes

20

2

Name 19

Sector 7

Yes

21

3

Name 20

Sector 7

Yes

22

1

Name 21

Sector 8

No

23

1

Name 22

Sector 9

Yes

24

2

Name 23

Sector 9

Yes

25

3

Name 24

Sector 9

Yes

26

4

Name 25

Sector 9

No

27

1

Name 26

Sector 11

Yes

28

1

Name 27

Sector 12

Yes

29

1

Name 28

Sector 13

Yes

30

1

Name 29

Sector 14

No

31

1

Name 30

Sector 15

Yes

32

2

Name 31

Sector 15

Yes

33

3

Name 32

Sector 15

Yes

34

1

Name 33

Sector 16

Yes

35

2

Name 34

Sector 17

Yes

36

1

Name 35

Sector 18

Yes

37

1

Name 36

Sector 19

No

Weeks

Sheet 'Shift'

Plantão Semanal - Projeto Klabin.xlsx

A

B

C

1

Week's Index

Sector

Worker

2

1

Sector 1

3

1

Sector 2

4

1

Sector 3

5

1

Sector 4

6

1

Sector 5

7

1

Sector 6

8

1

Sector 7

9

1

Sector 8

10

1

Sector 9

11

1

Sector 11

12

1

Sector 12

13

1

Sector 13

14

2

Sector 1

15

2

Sector 2

16

2

Sector 3

17

2

Sector 4

18

2

Sector 5

19

2

Sector 6

20

2

Sector 7

21

2

Sector 8

22

2

Sector 9

23

2

Sector 11

24

2

Sector 12

25

2

Sector 13

26

3

Sector 1

27

3

Sector 2

28

3

Sector 3

29

3

Sector 4

30

3

Sector 5

31

3

Sector 6

32

3

Sector 7

33

3

Sector 8

34

3

Sector 9

35

3

Sector 11

36

3

Sector 12

37

3

Sector 13

Testes

Desired result

Plantão Semanal - Projeto Klabin.xlsx

A

B

C

1

Week's Index

Sector

Worker

2

1

Sector 1

Name 1

3

1

Sector 2

Name 6

4

1

Sector 3

-

5

1

Sector 4

Name 13

6

1

Sector 5

-

7

1

Sector 6

Name 17

8

1

Sector 7

Name 18

9

1

Sector 8

-

10

1

Sector 9

Name 22

11

1

Sector 11

Name 26

12

1

Sector 12

Name 27

13

1

Sector 13

Name 28

14

2

Sector 1

Name 2

15

2

Sector 2

Name 7

16

2

Sector 3

-

17

2

Sector 4

Name 14

18

2

Sector 5

-

19

2

Sector 6

Name 17

20

2

Sector 7

Name 19

21

2

Sector 8

-

22

2

Sector 9

Name 23

23

2

Sector 11

Name 26

24

2

Sector 12

Name 27

25

2

Sector 13

Name 28

26

3

Sector 1

Name 3

27

3

Sector 2

Name 8

28

3

Sector 3

-

29

3

Sector 4

Name 15

30

3

Sector 5

-

31

3

Sector 6

Name 17

32

3

Sector 7

Name 20

33

3

Sector 8

-

34

3

Sector 9

Name 24

35

3

Sector 11

Name 26

36

3

Sector 12

Name 27

37

3

Sector 13

Name 28

Testes

PhBarreto · Sep 19, 2023

ExceLoki said:
so you dont want to solution i came up with?!?

Unfortunately no, it is a complete solution. The formula you provided only solved part of the problem.

With your formula I have the result below.

Plantão Semanal - Projeto Klabin (1).xlsx

A

B

C

1

Week's Index

Sector

Worker

2

1

Sector 1

Name 1

3

2

Sector 1

Name 2

4

3

Sector 1

Name 3

5

4

Sector 1

Name 1

6

5

Sector 1

Name 1

7

6

Sector 1

Name 1

8

7

Sector 1

Name 1

9

8

Sector 1

Name 1

10

9

Sector 1

Name 1

Planilha1

And there needs to be a loop between workers in the sector.

Plantão Semanal - Projeto Klabin (1).xlsx

A

B

C

13

Week's Index

Sector

Worker

14

1

Sector 1

Name 1

15

2

Sector 1

Name 2

16

3

Sector 1

Name 3

17

4

Sector 1

Name 1

18

5

Sector 1

Name 2

19

6

Sector 1

Name 3

20

7

Sector 1

Name 1

21

8

Sector 1

Name 2

22

9

Sector 1

Name 3

Planilha1

Did you understand? I don't know if I explained it very well in the first place...
Thanks!

ExceLoki · Sep 19, 2023

i mean i have an updated solution, from your other post that gave more information than your first post.
i understand my first solution only worked for the information given at first, and not the actual end result desired.
try this
column c is the full formula, column d is the same thing, but condensed using let function
-----------------

multiple posts solutions-v3.xlsx

A

B

C

D

1

Week's Index

Sector

long Result

let result

2

1

Sector 1

Name 1

3

1

Sector 2

Name 6

4

1

Sector 3

-

5

1

Sector 4

Name 13

6

1

Sector 5

-

7

1

Sector 6

Name 17

8

1

Sector 7

Name 18

9

1

Sector 8

-

10

1

Sector 9

Name 22

11

1

Sector 11

Name 26

12

1

Sector 12

Name 27

13

1

Sector 13

Name 28

14

2

Sector 1

Name 2

15

2

Sector 2

Name 7

16

2

Sector 3

-

17

2

Sector 4

Name 14

18

2

Sector 5

-

19

2

Sector 6

Name 17

20

2

Sector 7

Name 19

21

2

Sector 8

-

22

2

Sector 9

Name 23

23

2

Sector 11

Name 26

24

2

Sector 12

Name 27

25

2

Sector 13

Name 28

26

3

Sector 1

Name 3

27

3

Sector 2

Name 8

28

3

Sector 3

-

29

3

Sector 4

Name 15

30

3

Sector 5

-

31

3

Sector 6

Name 17

32

3

Sector 7

Name 20

33

3

Sector 8

-

34

3

Sector 9

Name 24

35

3

Sector 11

Name 26

36

3

Sector 12

Name 27

37

3

Sector 13

Name 28

38

4

Sector 1

Name 1

39

4

Sector 2

Name 10

40

5

Sector 1

Name 2

41

5

Sector 2

Name 11

42

6

Sector 1

Name 3

43

6

Sector 2

Name 6

Shift

Cell Formulas
Range	Formula
C2:C43	C2	=IFERROR(IFERROR(INDEX(FILTER(Workers!$B$2:$B$37,(Workers!$C$2:$C$37=B2)(Workers!$D$2:$D$37="Yes")),IF(A2>COUNTIFS(Workers!$C$2:$C$37,B2,Workers!$D$2:$D$37,"Yes"),IF(MOD(A2,COUNTIFS(Workers!$C$2:$C$37,B2,Workers!$D$2:$D$37,"Yes"))=0,COUNTIFS(Workers!$C$2:$C$37,B2,Workers!$D$2:$D$37,"Yes"),MOD(A2,COUNTIFS(Workers!$C$2:$C$37,B2,Workers!$D$2:$D$37,"Yes"))),A2)),INDEX(FILTER(Workers!$B$2:$B$37,(Workers!$C$2:$C$37=B2)(Workers!$D$2:$D$37="Yes")),1)),"-")
D2:D43	D2	=LET(fff,FILTER(Workers!$B$2:$B$37,(Workers!$C$2:$C$37=Shift!B2)*(Workers!$D$2:$D$37="Yes")),ccc,COUNTIFS(Workers!$C$2:$C$37,B2,Workers!$D$2:$D$37,"Yes"),mmm,MOD(A2,COUNTIFS(Workers!$C$2:$C$37,B2,Workers!$D$2:$D$37,"Yes")),IFERROR(IFERROR(INDEX(fff,IF(A2>ccc,IF(mmm=0,ccc,mmm),A2)),INDEX(fff,1)),"-"))

PhBarreto · Sep 19, 2023

ExceLoki said:
i mean i have an updated solution, from your other post that gave more information than your first post.
i understand my first solution only worked for the information given at first, and not the actual end result desired.
try this
column c is the full formula, column d is the same thing, but condensed using let function
-----------------

multiple posts solutions-v3.xlsx
A B C D
1 Week's Index Sector long Result let result
2 1 Sector 1 Name 1 Name 1
3 1 Sector 2 Name 6 Name 6
4 1 Sector 3 - -
5 1 Sector 4 Name 13 Name 13
6 1 Sector 5 - -
7 1 Sector 6 Name 17 Name 17
8 1 Sector 7 Name 18 Name 18
9 1 Sector 8 - -
10 1 Sector 9 Name 22 Name 22
11 1 Sector 11 Name 26 Name 26
12 1 Sector 12 Name 27 Name 27
13 1 Sector 13 Name 28 Name 28
14 2 Sector 1 Name 2 Name 2
15 2 Sector 2 Name 7 Name 7
16 2 Sector 3 - -
17 2 Sector 4 Name 14 Name 14
18 2 Sector 5 - -
19 2 Sector 6 Name 17 Name 17
20 2 Sector 7 Name 19 Name 19
21 2 Sector 8 - -
22 2 Sector 9 Name 23 Name 23
23 2 Sector 11 Name 26 Name 26
24 2 Sector 12 Name 27 Name 27
25 2 Sector 13 Name 28 Name 28
26 3 Sector 1 Name 3 Name 3
27 3 Sector 2 Name 8 Name 8
28 3 Sector 3 - -
29 3 Sector 4 Name 15 Name 15
30 3 Sector 5 - -
31 3 Sector 6 Name 17 Name 17
32 3 Sector 7 Name 20 Name 20
33 3 Sector 8 - -
34 3 Sector 9 Name 24 Name 24
35 3 Sector 11 Name 26 Name 26
36 3 Sector 12 Name 27 Name 27
37 3 Sector 13 Name 28 Name 28
38 4 Sector 1 Name 1 Name 1
39 4 Sector 2 Name 10 Name 10
40 5 Sector 1 Name 2 Name 2
41 5 Sector 2 Name 11 Name 11
42 6 Sector 1 Name 3 Name 3
43 6 Sector 2 Name 6 Name 6
Shift
Cell Formulas
Range Formula
C2:C43 C2 =IFERROR(IFERROR(INDEX(FILTER(Workers!$B$2:$B$37,(Workers!$C$2:$C$37=B2)*(Workers!$D$2:$D$37="Yes")),IF(A2>COUNTIFS(Workers!$C$2:$C$37,B2,Workers!$D$2:$D$37,"Yes"),IF(MOD(A2,COUNTIFS(Workers!$C$2:$C$37,B2,Workers!$D$2:$D$37,"Yes"))=0,COUNTIFS(Workers!$C$2:$C$37,B2,Workers!$D$2:$D$37,"Yes"),MOD(A2,COUNTIFS(Workers!$C$2:$C$37,B2,Workers!$D$2:$D$37,"Yes"))),A2)),INDEX(FILTER(Workers!$B$2:$B$37,(Workers!$C$2:$C$37=B2)*(Workers!$D$2:$D$37="Yes")),1)),"-")
D2:D43 D2 =LET(fff,FILTER(Workers!$B$2:$B$37,(Workers!$C$2:$C$37=Shift!B2)*(Workers!$D$2:$D$37="Yes")),ccc,COUNTIFS(Workers!$C$2:$C$37,B2,Workers!$D$2:$D$37,"Yes"),mmm,MOD(A2,COUNTIFS(Workers!$C$2:$C$37,B2,Workers!$D$2:$D$37,"Yes")),IFERROR(IFERROR(INDEX(fff,IF(A2>ccc,IF(mmm=0,ccc,mmm),A2)),INDEX(fff,1)),"-"))

@ExceLoki This is awesome! This is exactly what I was looking for! Thank you very much for your help and time!
I have a big smile on my face and I really appreciate your help!
Thank you again!

ExceLoki · Sep 19, 2023

happy to help

How to create work shifts based on these conditions?

PhBarreto

New Member

PhBarreto

New Member

Excel Facts

ExceLoki

Well-known Member

PhBarreto

New Member

ExceLoki

Well-known Member

Similar threads

Forum statistics

Share this page

How to create work shifts based on these conditions?

PhBarreto

New Member

PhBarreto

New Member

Excel Facts

ExceLoki

Well-known Member

PhBarreto

New Member

ExceLoki

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock