Excel formula needed or how to

[kir]

I have got a rota with E for Early, L for Late and N for Night.

For one particular date, e.g. in C3 there is the date, and C4 to C8 ar
the various E , L and N.

Now on the other side I want see who is actually working E, L or N at
quick glance and requires a formula to convert the E to the nam
allocated to that person and can it be done in such a way to omit th
person who is not doing the E from the column of names and avoid an
blank rows.

Attached is a brief example but there is about 35 staff on that.

4/7/04 5/7/04 6/7/04
John EL EL EL
Paul L O L
Charlie N N O
Jennife O L N
Charlene EL L EL


EARLY LATE NIGHT
04/07/2004 04/07/2004 04/07/2004
JOHN JOHN CHARLIE
CHARLENE pAUL
CHARLENE

Many thanks

Ki

 

[AlfD]

Hi!

Before you get stuck into formulae etc, try Data > Filter
Autofilter.

Selecting E in a given date column will show all the Early shift, etc

Al

 

[kir]

ALfd

The filter is not really applicable as I need the E for 7 differen
days at the same time.

I will also need the L and the N to have names of staff working o
these days

 

[AlfD]

Hi!

OK.

Then probably the simplest way is to sort your data by the column wit
the date at the top that you want to look at.

This will gather all the E's together and so on.

Returning to where you were can be achieved by using undo at thi
point. But there would be wisdom in putting in a column of sequentia
numbers (1 to 35 or whatever) so that you can always revert to wher
you started by sorting on this column. (Obvious: but usually _after
one has done the sort...)

For more analysis you can then use AutoFilter; or you can copy an
paste the bits that interest you.

Al