How to create work shifts based on these conditions?

[PhBarreto]

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]

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]

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)),"-"))

 

[PhBarreto]

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]

happy to help