Returning matches from mutiple rows

[Sunshine]

I use a nursing schedule that shows all nursing staff's schedule for a whole
month. This is referred to as the master schedule.
From this schedule I need to be able to identify specific staff scheduled to
work on a specific day during a specific shift on a daily basis in a separate
worksheet.

So if the master schedule is:

A1 A2 A3 A4
April 1, 2008 April 2, 2008 April 3, 2008
NAME SHIFT SHIFT SHIFT
Debbie 7-3 7-3 Off
Trina 3-11 7-3 7-3
Sherry 7-3 Off 7-3
Lisa 3-11 3-11 3-11
Lewis 7-3 3-11 3-11
Paula Off 7-3 3-11
Sharon 11-7 11-7 11-7

I need a daily schedule for April 1. If I put in that date, it will return
everyone working that day and segregate it by shift. So everyone who is
working 7-3 on April 1 in the first 3 rows, 3-11 the next 3 and ll-7.

 

[Max]

Try this set-up which provides the automation that you seek
with an output format that is clear & acceptable

Illustrated in this sample:
http://www.freefilehosting.net/download/3ec3d
Nursing schedule.xls

Source data is assumed in sheet: x, names in A3:A9, real dates for the month
listed across in B1:AF1 (the max 31 days per any month are catered for), and
with the shift detail (eg: 7-3, 3-11, etc) listed within B3:AF9

In x,
List in AH2:AK2 the 4 "Shift" labels: 7-3, 3-11, 11-7, Off
Put in AH3:
=IF(OFFSET($A$3:$A$9,,MATCH(y!$A$2,$A$1:$Z$1,0)-1)="","",
IF(OFFSET($A$3:$A$9,,MATCH(y!$A$2,$A$1:$Z$1,0)-1)=AH$2,ROWS($1:1),""))
Copy AH3 across/fill down to AK9

Then in another sheet: y (say),
In A2 is a DV to select the date, eg: April 1, 2008
(Data > Validation, Allow: List, Source: = DateR,
where DateR is a defined range, referring to: =x!$B$1:$AF$1)

Paste into C2:F2 the 4 "Shift" labels: 7-3, 3-11, 11-7, Off
Then put in C3
=IF(ROWS($1:1)>COUNT(OFFSET(x!$AG$3:$AG$9,,MATCH(C$2,x!$AH$2:$AK$2,0))),"",INDEX(x!$A$3:$A$9,SMALL(OFFSET(x!$AG$3:$AG$9,,MATCH(C$2,x!$AH$2:$AK$2,0)),ROWS($1:1))))
Copy C3 across/fill down to F9. This will return the required staff names
(from x) for the particular date selected in A2 under the correct shift
labels, with names neatly bunched at the top.

Example outputs:
For April 1, 2008
7-3 3-11 11-7 Off
Debbie Trina Sharon Paula
Sherry Lisa
Lewis

For April 2, 2008
7-3 3-11 11-7 Off
Debbie Lisa Sharon Sherry
Trina Lewis
Paula

 

[Lars-Åke Aspelin]

I use a nursing schedule that shows all nursing staff's schedule for a whole
month. This is referred to as the master schedule.
From this schedule I need to be able to identify specific staff scheduled to
work on a specific day during a specific shift on a daily basis in a separate
worksheet.

So if the master schedule is:

A1 A2 A3 A4
April 1, 2008 April 2, 2008 April 3, 2008
NAME SHIFT SHIFT SHIFT
Debbie 7-3 7-3 Off
Trina 3-11 7-3 7-3
Sherry 7-3 Off 7-3
Lisa 3-11 3-11 3-11
Lewis 7-3 3-11 3-11
Paula Off 7-3 3-11
Sharon 11-7 11-7 11-7

I need a daily schedule for April 1. If I put in that date, it will return
everyone working that day and segregate it by shift. So everyone who is
working 7-3 on April 1 in the first 3 rows, 3-11 the next 3 and ll-7.

This is one way you may try:
Introduce a help column for each shift according to the table below

A1 A2 help 1 A3 help 2 A4 help 3
April 1, 2008 April 2, 2008 April 3, 2008
NAME SHIFT SHIFT SHIFT
Debbie 7-3 7-3_1 7-3 7-3_1 Off Off_1
Trina 3-11 3-11_1 7-3 7-3_2 7-3 7-3_1
Sherry 7-3 7-3_2 Off Off_1 7-3 7-3_2
Lisa 3-11 3-11_2 3-11 3-11_1 3-11 3-11_1
Lewis 7-3 7-3_3 3-11 3-11_2 3-11 3-11_2
Paula Off Off_1 7-3 7-3_3 3-11 3-11_3
Sharon 11-7 11-7_1 11-7 11-7_1 11-7 11-7_1

The formula of cell B4 is like:

=B4&"_"&COUNTIF(B$4:B4,B4) (note the $ in one but only one place)

Copy down to generate all these 7-3_1, 3-11_1, 7-3_2, etc

Assuming that there is no more than 17 nurses and you can use the
space from row 21 and below you can have the following table
generated:


April 2, 2008
7-3 3-11 11-7 Off
Debbie Lisa Sharon Sherry
Trina Lewis #N/A #N/A
Paula #N/A #N/A #N/A
#N/A #N/A #N/A #N/A
#N/A #N/A #N/A #N/A
#N/A #N/A #N/A #N/A
#N/A #N/A #N/A #N/A
#N/A #N/A #N/A #N/A
#N/A #N/A #N/A #N/A

The date (April 2, 2008 in this example) is in cell A21
The SHIFT is in cell A22

The formula in cell A23 is like:
=INDEX($A$4:$A$20,MATCH(A$22&"_"&ROW()-22,OFFSET($A$4:$A$20,0,MATCH($A$21,$A$2:$Z$2,0)),0))

Copy down as many rows as neeed.
If you don't like the #N/A's you can eliminate them by:
=IFERROR( the formula, "") (Excel 2007 only)
or
=IF(ISERROR( the formula ), "", the formula)

Hope this helps / Lars-Åke

 

[Max]

Slight errata ..
In x,
Amend the formula in AH3 to this:
=IF(OFFSET($A$3:$A$9,,MATCH(y!$A$2,$A$1:$AF$1,0)-1)="","",
IF(OFFSET($A$3:$A$9,,MATCH(y!$A$2,$A$1:$AF$1,0)-1)=AH$2,ROWS($1:1),""))
Then copy AH3 across/fill down to AK9

(the earlier "$Z$1" should be extended to "$AF$1")

 

[T. Valko]

Here's a small sample file that demonstrates this:

xSchedule.xls 19kb

http://www.freefilehosting.net/download/3ed4a

A single array formula is all that's needed.