Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Data manipulation
I am trying to manipulate a fairly simple weekly schedule.
On Sheet 1, column A contains names, Row 1 contains Days of the week. The cells in column B contain a number indicating the shift that the person is supposed to work. It looks like this: Sun Joe 6 Bill 7 Bob 7 Frank 11 On Sheet 2, I want to turn this data around so that the column headings are the shift time and underneath there is a list of people on each shift. It should look like: 6 7 11 Joe Bill Frank Bob Unfortunately, there may be a different number of people on each shift each day, so a simple sort isn't enough. Note that each day will have it's own sheet (2-8) drawing its' data from the proper column. How do I do it? |
#2
|
|||
|
|||
Here's one set-up to try ..
Assume there are 11 shifts, numbered: 1,2,3 ... 11 Let's take the sample table below, assumed in Sheet1, A1:H5 ----Sun Mon Tue Wed Thu Fri Sat Joe 6 1 9 8 1 2 7 Bill 7 2 9 5 8 6 8 Bob 7 4 8 1 9 8 8 Frank 11 10 6 5 10 2 9 Name the range B1:H1 (i.e.: Sun, Mon, Tues ... Sat) as : WDay (this named range will be used in a DV cell we're creating in Sheet2's A1 later) Put in J1: =Sheet2!A1 List across in K1:U1, the 11 shift #s: 1, 2, 3 ... 11 Put in K2: =IF(ISNA(MATCH(K$1,OFFSET($A2,,MATCH($J$1,$B$1:$H$ 1,0)),0)),"",ROW()) Copy K2 across to U2, fill down to say U100 to cover the max expected number of staff in col A (can copy down ahead of expected data input) In Sheet2 --------- Let's create a data validation list in A1 to select the day Select A1, click Data Validation Make the settings as: Allow: List, Source: =WDay Click OK List across in A2:K2, the 11 shift #s: 1, 2, 3 ... 11 Put in A3: =IF(ISERROR(SMALL(Sheet1!K$2:K$100,ROWS($A$1:A1))) ,"",IF(INDEX(Sheet1!$A$2:$ A$100,MATCH(SMALL(Sheet1!K$2:K$100,ROWS($A$1:A1)), Sheet1!K$2:K$100,0))=0,"", INDEX(Sheet1!$A$2:$A$100,MATCH(SMALL(Sheet1!K$2:K$ 100,ROWS($A$1:A1)),Sheet1! K$2:K$100,0)))) Copy A3 across to K3, fill down to K101 (cover the same range as in Sheet1) Test it out. Select in A1: Sun (say). You'll get: 6 7 11 Joe Bill Frank Bob Try select in A1: Tues, and you'll get 6 8 9 Frank Bob Joe Bill (below Joe) And so on .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- "BW" wrote in message ... I am trying to manipulate a fairly simple weekly schedule. On Sheet 1, column A contains names, Row 1 contains Days of the week. The cells in column B contain a number indicating the shift that the person is supposed to work. It looks like this: Sun Joe 6 Bill 7 Bob 7 Frank 11 On Sheet 2, I want to turn this data around so that the column headings are the shift time and underneath there is a list of people on each shift. It should look like: 6 7 11 Joe Bill Frank Bob Unfortunately, there may be a different number of people on each shift each day, so a simple sort isn't enough. Note that each day will have it's own sheet (2-8) drawing its' data from the proper column. How do I do it? |
#3
|
|||
|
|||
Assuming that on Sheet2...
A1: contains the day of interest, such as Sun A2: contains 6 B2: contains 7 C2: contains 11 Enter the following formula in A3, copied down and across: =INDEX(Sheet1!$A:$A,SMALL(IF(INDEX(Sheet1!$B$2:$H$ 5,0,MATCH(Sheet2!$A$1,S heet1!$B$1:$H$1,0))=Sheet2!A$2,ROW(INDEX(Sheet1!$B $2:$H$5,0,MATCH(Sheet2! $A$1,Sheet1!$B$1:$H$1,0)))),ROWS($A$3:A3))) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Then repeat the process for each sheet representing the day of the week, making sure that the value for A1 changes accordingly. Also, make sure that the references to Sheet2 change accordingly. Then, use Conditional Formatting to hide error values... 1) Select your data on Sheet2, for example A3:C4 2) Format Conditional Formatting Formula Is 3) Enter the following formula: =ISERR(A3) 4) Choose 'White' as your font color 5) Click Ok 6) Repeat the process for each sheet representing the day of the week. Hope this helps! In article , "BW" wrote: I am trying to manipulate a fairly simple weekly schedule. On Sheet 1, column A contains names, Row 1 contains Days of the week. The cells in column B contain a number indicating the shift that the person is supposed to work. It looks like this: Sun Joe 6 Bill 7 Bob 7 Frank 11 On Sheet 2, I want to turn this data around so that the column headings are the shift time and underneath there is a list of people on each shift. It should look like: 6 7 11 Joe Bill Frank Bob Unfortunately, there may be a different number of people on each shift each day, so a simple sort isn't enough. Note that each day will have it's own sheet (2-8) drawing its' data from the proper column. How do I do it? |
#4
|
|||
|
|||
Just a bit of clarification wrt the OP's ..
. Note that each day will have it's own sheet (2-8) drawing its' data from the proper column. The suggested set-up implicitly does away with the need to have multiple sheets for each day, since the desired results for each/any day could be easily retrieved via selecting the day from the DV in cell A1 (Sheet2). And if necessary, the results in Sheet2 could also be copied and paste special as values elsewhere. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with data not getting plotted | Excel Discussion (Misc queries) | |||
Help with data not getting plotted | Charts and Charting in Excel | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions | |||
Charting data ranges that change | Charts and Charting in Excel | |||
Running Data Table using an input that triggers DDE linked data | Excel Discussion (Misc queries) |