Staff Rota Formula

R

runderwood10

New Member
Joined
Aug 4, 2022
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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?

1659601448863.png


1659601472203.png


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!
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
Hi & welcome to MrExcel.
How about
Excel Formula: 
=INDEX(SORT(FILTER('Staff list'!$B$2:$D$80&"",('Staff list'!$C$2:$C$80=C$1)),3,1),{1;2},1)
 
Upvote 0
Fluff said:
Hi & welcome to MrExcel.
How about
Excel Formula: 
=INDEX(SORT(FILTER('Staff list'!$B$2:$D$80&"",('Staff list'!$C$2:$C$80=C$1)),3,1),{1;2},1)
Click to expand...
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
 
Upvote 0
Fluff said:
That is what it should be doing.
Click to expand...
So using the formula you've provided, Centre 1 looks like this:

1659609821830.png


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:

1659609864887.png


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

Thank you
R
 
Upvote 0
Check that the cells are actually empty, rather than contain a space or such like.
 
Upvote 0
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.
 
Upvote 0
Fluff said:
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.
Click to expand...
Here is the staff list tab:

Rota - Test.xlsx
ABCD
1NameCentreLast Date
21Name 1Centre 105/01/2022
32Name 2Centre 1
43Name 3Centre 1
54Name 4Centre 1
65Name 5Centre 1
76Name 6Centre 127/01/2022
87Name 7Centre 1
98Name 8Centre 1
101Name 1Centre 2-
112Name 2Centre 2
123Name 3Centre 2-
134Name 4Centre 225/05/2022
145Name 5Centre 2
156Name 6Centre 2
167Name 7Centre 2
178Name 8Centre 221/10/2021
189Name 9Centre 220/07/2022
1910Name 10Centre 229/04/2022
201Name 1Centre 326/10/2021
212Name 2Centre 3
223Name 3Centre 311/05/2022
234Name 4Centre 306/09/2021
245Name 5Centre 307/12/2021
251Name 1Centre 423/09/2021
262Name 2Centre 405/10/2021
273Name 3Centre 4
284Name 4Centre 422/06/2022
295Name 5Centre 402/11/2021
306Name 6Centre 4
317Name 7Centre 4
321Name 1Centre 5
332Name 2Centre 5
343Name 3Centre 5
354Name 4Centre 5
365Name 5Centre 5
376Name 6Centre 5
381Name 1Centre 617/09/2021
392Name 2Centre 616/06/2022
401Name 1Centre 724/11/2021
412Name 2Centre 7
421Name 1Centre 8-
432Name 2Centre 8-
443Name 3Centre 8
454Name 4Centre 817/03/2022
465Name 5Centre 806/10/2021
476Name 6Centre 8
487Name 7Centre 8
498Name 8Centre 8
509Name 9Centre 8
5110Name 10Centre 8
521Name 1Centre 9
532Name 2Centre 908/03/2022
543Name 3Centre 901/10/2021
554Name 4Centre 908/03/2022
565Name 5Centre 927/05/2022
576Name 6Centre 9
587Name 7Centre 925/04/2022
598Name 8Centre 916/03/2022
609Name 9Centre 925/04/2022
6110Name 10Centre 901/10/2021
6211Name 11Centre 913/06/2022
6312Name 12Centre 9
6413Name 13Centre 9
6514Name 14Centre 917/09/2021
6615Name 15Centre 918/05/2022
6716Name 16Centre 9
681Name 1Centre 10
692Name 2Centre 10
703Name 3Centre 1008/11/2021
714Name 4Centre 1018/03/2022
725Name 5Centre 1008/12/2021
736Name 6Centre 1029/10/2021
747Name 7Centre 1015/07/2022
758Name 8Centre 10
769Name 9Centre 10
7710Name 1Centre 11
7811Name 2Centre 11
7912Name 3Centre 11
8013Name 4Centre 11
Staff List


And here is the formula tab:

Rota - Test.xlsx
ABCDEFGHIJK
1Centre 1Centre 2Centre 3Centre 4Centre 5Centre 6Centre 7Centre 8Centre 9Centre 10Centre 11
2Name 2Name 3Name 1Name 1Name 2Name 3Name 1Name 1Name 1#CALC!#CALC!
3Name 4Name 6Name 2Name 2Name 1Name 6Name 6Name 2Name 2#CALC!#CALC!
Formula
Cell Formulas
RangeFormula
A1:K1A1=TRANSPOSE(UNIQUE('Staff List'!$C$2:$C$80,FALSE))
A2:K3A2=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.
 
Upvote 0
Thanks for that.
The formula in A2 is looking at C1 for the centre not A1
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Return last date checked
Replies
3
Views
75
Peter_SSs
Peter_SSs
T
  • Question Question
Pulling Data from a work Rota
Replies
2
Views
225
Tyroneb90
T
M
  • Question Question
Pulling a list from a rota
Replies
1
Views
263
KRice
K
E
  • Question Question
Find data from a range and then the oldest date from a row when matched
Replies
0
Views
235
-emma-
E
E
  • Solved
Duplicate row based on cell with text separated by semicolon
Replies
5
Views
99
JEC
JEC

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top