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

 

[ExceLoki]

which version of excel are you using?
i suggest adding to your profile so anytime you make a post readers will know.
also, does the shift "yes" or "no" column play a part in scheduling?

 

[PhBarreto]

which version of excel are you using?
i suggest adding to your profile so anytime you make a post readers will know.
also, does the shift "yes" or "no" column play a part in scheduling?

Hello, @ExcelLoki!

Q-which version of excel are you using?
A- 365
Q-Does the shift "yes" or "no" column play a part in scheduling?
A- Yes!

What do you mean by that? Or how do I do this?
"...i suggest adding to your profile so anytime you make a post readers will know...."

Edit1
I was able to add office 365 to my profile.

 

[ExceLoki]

try this
---------------

Book1
	A	B	C
1	Week's Index	Sector	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
Shift

Cell Formulas
Range	Formula
C2:C37	C2	=IFERROR(IFERROR(INDEX(FILTER(Workers!$B$2:$B$37,(Workers!$C$2:$C$37=Shift!B2)*(Workers!$D$2:$D$37="Yes")),COUNTIF($B$2:B2,B2)),INDEX(FILTER(Workers!$B$2:$B$37,(Workers!$C$2:$C$37=Shift!B2)*(Workers!$D$2:$D$37="Yes")),1)),"-")

 

[PhBarreto]

Almost... column A 'Week's Index', in the 'Shift' sheet means the week of the year. We have 52.
For example, from week 4 onwards, for Sector 1, a single consultant repeats, and it should be for week 4 Name 1, for week 5 Name 2, for week 6 Name 3 and so on.

I applied a simple filter to make it easier to understand:

Current results:

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

Desired result:

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

 

[PhBarreto]

Hey guys!
Does anyone have any more ideas?

 

[PhBarreto]

@ExceLoki Please forgive me! I ended up forgetting to thank you. Thank you very much for your attention and help.

Does anyone else have any ideas on how to solve this problem?
Thanks in advance!

 

[ExceLoki]

glad to help, and looks like might have a solution

 

[PhBarreto]

glad to help, and looks like might have a solution

I'm also thinking there's a solution, you've renewed my hope. I had been trying for a week and couldn't make any progress. The only thing missing is the issue of the loop between workers. I hope someone else comes along to help.
Thanks!

 

[ExceLoki]

I hope someone else comes along to help.

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