Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning matches from mutiple rows
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning matches from mutiple rows
On Sat, 29 Mar 2008 19:18:00 -0700, Sunshine
wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning matches from mutiple rows
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(O FFSET(x!$AG$3:$AG$9,,MATCH(C$2,x!$AH$2:$AK$2,0)),R OWS($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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Sunshine" wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning matches from mutiple rows
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") -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Returning matches from mutiple rows
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. -- Biff Microsoft Excel MVP "Sunshine" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup for mutiple rows | Excel Worksheet Functions | |||
Mail Merge - combining mutiple rows of data on one mailer. | Excel Discussion (Misc queries) | |||
Copy rows of one sheet into mutiple sheets based on column value | Excel Discussion (Misc queries) | |||
Split cell data in mutiple rows | Excel Discussion (Misc queries) | |||
set number of rows equal in mutiple columns | Excel Discussion (Misc queries) |