• 465K Members
• 10.8K Online
• 563K Conversations

## Excel

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

# Re: Excel

Hopefully attached!!

# Re: Excel

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

# Re: Excel

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

# 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

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

# 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

# Re: Excel

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

# Re: Excel

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

# Re: Excel

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

# 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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies