Staff Rota Formula

[runderwood10]

Hey everyone.

I've created a staff rota spreadsheet which takes a list of staff members and returns 2 staff names with either a blank "date" cell or the oldest date.

I'm a bit stuck though as I can't seem to get the formula to return 2 unique names. Here are some images to explain further, you will see for Centre 3 on the Formula tab that it is returning the same staff name twice - is anyone able to help at all?

The formula is as follows:
=INDEX(IFERROR(FILTER('Staff List'!$B$2:$B$80,('Staff List'!$C$2:$C$80=C$1)*('Staff List'!$D$2:$D$80="")),FILTER(SORT(FILTER('Staff List'!$B$2:$D$80,('Staff List'!$C$2:$C$80=C$1)),3,1),{1,0,0})),SEQUENCE(2),SEQUENCE(,1))

Thank you so much in advance!

 

[Fluff]

Hi & welcome to MrExcel.
How about

=INDEX(SORT(FILTER('Staff list'!$B$2:$D$80&"",('Staff list'!$C$2:$C$80=C$1)),3,1),{1;2},1)

 

[runderwood10]

Hi & welcome to MrExcel.
How about

=INDEX(SORT(FILTER('Staff list'!$B$2:$D$80&"",('Staff list'!$C$2:$C$80=C$1)),3,1),{1;2},1)

Hi Fluff,

Thanks so much for your response & your warm welcome.

My apologies, I don't think I used enough detail to explain the intent of the formula.

So I need the rota to return the 2 members of staff that either do not have a "Last Date" or the earliest date in "Last Date" column, moving down the rows using the incremental number in column A.

So for example; For Centre 3, this should be returning Name 2 and Name 4. For Centre 4 this should be returning Name 3 and Name 6. For Centre 5 this should be returning Name 1 and Name 2.

I hope that is a little clearer.

Thank you so much for your help.
R

 

[Fluff]

For Centre 3, this should be returning Name 2 and Name 4.

That is what it should be doing.

 

[runderwood10]

That is what it should be doing.

So using the formula you've provided, Centre 1 looks like this:

So I would expect this to return Name 2 and Name 3, however it is currently returning Name 2 and Name 4, so is missing Name 3 even though this cell is blank:

It is repeating this on all centres where it is missing blank cells.

Thank you
R

 

[Fluff]

Check that the cells are actually empty, rather than contain a space or such like.

 

[runderwood10]

Check that the cells are actually empty, rather than contain a space or such like.

That was my first check just to make sure! Definitely all blank.

 

[Fluff]

I cannot replicate that behaviour, can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[runderwood10]

I cannot replicate that behaviour, can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Here is the staff list tab:

Rota - Test.xlsx
	A	B	C	D
1	Name		Centre	Last Date
2	1	Name 1	Centre 1	05/01/2022
3	2	Name 2	Centre 1
4	3	Name 3	Centre 1
5	4	Name 4	Centre 1
6	5	Name 5	Centre 1
7	6	Name 6	Centre 1	27/01/2022
8	7	Name 7	Centre 1
9	8	Name 8	Centre 1
10	1	Name 1	Centre 2	-
11	2	Name 2	Centre 2
12	3	Name 3	Centre 2	-
13	4	Name 4	Centre 2	25/05/2022
14	5	Name 5	Centre 2
15	6	Name 6	Centre 2
16	7	Name 7	Centre 2
17	8	Name 8	Centre 2	21/10/2021
18	9	Name 9	Centre 2	20/07/2022
19	10	Name 10	Centre 2	29/04/2022
20	1	Name 1	Centre 3	26/10/2021
21	2	Name 2	Centre 3
22	3	Name 3	Centre 3	11/05/2022
23	4	Name 4	Centre 3	06/09/2021
24	5	Name 5	Centre 3	07/12/2021
25	1	Name 1	Centre 4	23/09/2021
26	2	Name 2	Centre 4	05/10/2021
27	3	Name 3	Centre 4
28	4	Name 4	Centre 4	22/06/2022
29	5	Name 5	Centre 4	02/11/2021
30	6	Name 6	Centre 4
31	7	Name 7	Centre 4
32	1	Name 1	Centre 5
33	2	Name 2	Centre 5
34	3	Name 3	Centre 5
35	4	Name 4	Centre 5
36	5	Name 5	Centre 5
37	6	Name 6	Centre 5
38	1	Name 1	Centre 6	17/09/2021
39	2	Name 2	Centre 6	16/06/2022
40	1	Name 1	Centre 7	24/11/2021
41	2	Name 2	Centre 7
42	1	Name 1	Centre 8	-
43	2	Name 2	Centre 8	-
44	3	Name 3	Centre 8
45	4	Name 4	Centre 8	17/03/2022
46	5	Name 5	Centre 8	06/10/2021
47	6	Name 6	Centre 8
48	7	Name 7	Centre 8
49	8	Name 8	Centre 8
50	9	Name 9	Centre 8
51	10	Name 10	Centre 8
52	1	Name 1	Centre 9
53	2	Name 2	Centre 9	08/03/2022
54	3	Name 3	Centre 9	01/10/2021
55	4	Name 4	Centre 9	08/03/2022
56	5	Name 5	Centre 9	27/05/2022
57	6	Name 6	Centre 9
58	7	Name 7	Centre 9	25/04/2022
59	8	Name 8	Centre 9	16/03/2022
60	9	Name 9	Centre 9	25/04/2022
61	10	Name 10	Centre 9	01/10/2021
62	11	Name 11	Centre 9	13/06/2022
63	12	Name 12	Centre 9
64	13	Name 13	Centre 9
65	14	Name 14	Centre 9	17/09/2021
66	15	Name 15	Centre 9	18/05/2022
67	16	Name 16	Centre 9
68	1	Name 1	Centre 10
69	2	Name 2	Centre 10
70	3	Name 3	Centre 10	08/11/2021
71	4	Name 4	Centre 10	18/03/2022
72	5	Name 5	Centre 10	08/12/2021
73	6	Name 6	Centre 10	29/10/2021
74	7	Name 7	Centre 10	15/07/2022
75	8	Name 8	Centre 10
76	9	Name 9	Centre 10
77	10	Name 1	Centre 11
78	11	Name 2	Centre 11
79	12	Name 3	Centre 11
80	13	Name 4	Centre 11
Staff List

And here is the formula tab:

Rota - Test.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Centre 1	Centre 2	Centre 3	Centre 4	Centre 5	Centre 6	Centre 7	Centre 8	Centre 9	Centre 10	Centre 11
2	Name 2	Name 3	Name 1	Name 1	Name 2	Name 3	Name 1	Name 1	Name 1	#CALC!	#CALC!
3	Name 4	Name 6	Name 2	Name 2	Name 1	Name 6	Name 6	Name 2	Name 2	#CALC!	#CALC!
Formula

Cell Formulas
Range	Formula
A1:K1	A1	=TRANSPOSE(UNIQUE('Staff List'!$C$2:$C$80,FALSE))
A2:K3	A2	=INDEX(SORT(FILTER('Staff List'!$B$2:$D$80&"",('Staff List'!$C$2:$C$80=C$1)),3,1),{1;2},1)
Dynamic array formulas.

 

[Fluff]

Thanks for that.
The formula in A2 is looking at C1 for the centre not A1