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?

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?

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?

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
----