# Excel

Highlighted
Occasional Contributor

# Excel

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
12 Replies
Highlighted

# Re: Excel

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

# Re: Excel

Hopefully attached!!

Highlighted

# Re: Excel

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

# Re: Excel

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

Highlighted

# Re: Excel

why this four person in this list?

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

# Re: Excel

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

# Re: Excel

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

Highlighted

# Re: Excel

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

Highlighted

# Re: Excel

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

Highlighted

# Re: Excel

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

Highlighted

# Re: Excel

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

Highlighted

# Re: Excel

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