Excel

[simont485]

Hi

I have a Staff rota and I would like to create a list of staff availability based on date for staff who are not on shift or have booked holiday.

I NEED TO SHOW STAFF WHERE CELLS IN COLUMN C are BLANK ............. COLUMNS D TO L  ARE BLANK or H .........AND IN NUMERICAL ORDER BASED ON COLUMN B

Date would be inputed in cell B19 and the result displayed underneath

A	B	C	D	E	F	G	H	I	J	K	L
			01/01/2001	02/01/2001	03/01/2001	04/01/2001	05/01/2001	06/01/2001	07/01/2001	08/01/2001	09/01/2001
Person 1	12		W	W	W	W		W	W	W	W
Person 2	45	epn	D	D	D	D			D	D	D
Person 3	0	na
Person 4	4		E	E	E	E	E	E	E	E	E
Person 5	10	NA	H	H	H	H	H	H	H	H	H
Person 6	0		H	H	D	D	D	D	H	H	H
Person 7	24
Person 8	0				H	H	H	H
Person 9	15	na	R	R	R	R	R	R	R	R	R
Person 10	12		R	R	R	R	R	R	R	R	R

[#2]

please create a sample excel with manual input (indicating your result)

[#3]

Hopefully attached!!

[#4]

what is the purpose of c column, how did you get the number 12, 45, 0 ....

[#5]

Column C is an overtime count in hours.....so it will increase as more overtime is done.

[#6]

@simont485 

why this four person in this list?

So for
02/01/2001
Person 6	0
Person 8	0
Person 10	12
Person 7	24

[#7]

Let me interpret your requirements this way:
1. You want to know which persons are available on a specific date;
2. A person is available if the entry as of such date is blank, "H", or "R";
3. Given the date in B19, the available persons are listed starting in B20; and
4. The list must be sorted in ascending order according to the corresponding value of the available persons in Column C.
Please comment on my foregoing interpretation.

[#8]

@Gourab Dasgupta 

Good point...there should only be 3 people in that list!

Person 10 should not be there!!

I'd input the date in B19 and the staff who have the lowest count from column C would appear when column D is blank and the corresponding column under the entered date is blank or contains H

[#9]

@Twifoo 

That is correct! Except point 2..........Blank and H only....not R. (my mistake.)

[#10]

I have been trying Index/Match/ aggregate......but I am having trouble with that approach.......

[#11]

@simont485 

Please go through the excel file hope this will fulfill your criteria. 

[#12]

@Gourab Dasgupta 

That is amazing but not quite as I need it.......

If Column D contains N/A and Columns E to M are Blank or H then that person is Not Available

Also as columns D to M are updated I need to see the list update accordingly