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

[#13]

@simont485 , I know that this is an old thread, but the new Excel Dynamic Arrays make the solution easy. If you have the latest update of Excel 365, this should work as you asked.