Home

Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-562736%22%20slang%3D%22en-US%22%3EExcel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-562736%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20Staff%20rota%20and%20I%20would%20like%20to%20create%20a%20list%20of%20staff%20availability%20based%20on%20date%20for%20staff%20who%20are%20not%20on%20shift%20or%20have%20booked%20holiday.%3C%2FP%3E%3CP%3EI%20NEED%20TO%20SHOW%20STAFF%20WHERE%20CELLS%20IN%20COLUMN%26nbsp%3BC%20are%20BLANK%26nbsp%3B.............%20COLUMNS%26nbsp%3BD%20TO%26nbsp%3BL%26nbsp%3B%26nbsp%3BARE%20BLANK%20or%20H%20.........AND%20IN%20NUMERICAL%20ORDER%20BASED%20ON%20COLUMN%20B%3C%2FP%3E%3CP%3EDate%20would%20be%20inputed%20in%20cell%20B19%20and%20the%20result%20displayed%20underneath%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EA%3C%2FTD%3E%3CTD%3EB%3C%2FTD%3E%3CTD%3EC%3C%2FTD%3E%3CTD%3ED%3C%2FTD%3E%3CTD%3EE%3C%2FTD%3E%3CTD%3EF%3C%2FTD%3E%3CTD%3EG%3C%2FTD%3E%3CTD%3EH%3C%2FTD%3E%3CTD%3EI%3C%2FTD%3E%3CTD%3EJ%3C%2FTD%3E%3CTD%3EK%3C%2FTD%3E%3CTD%3EL%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E01%2F01%2F2001%3C%2FTD%3E%3CTD%3E02%2F01%2F2001%3C%2FTD%3E%3CTD%3E03%2F01%2F2001%3C%2FTD%3E%3CTD%3E04%2F01%2F2001%3C%2FTD%3E%3CTD%3E05%2F01%2F2001%3C%2FTD%3E%3CTD%3E06%2F01%2F2001%3C%2FTD%3E%3CTD%3E07%2F01%2F2001%3C%2FTD%3E%3CTD%3E08%2F01%2F2001%3C%2FTD%3E%3CTD%3E09%2F01%2F2001%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPerson%201%3C%2FTD%3E%3CTD%3E12%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EW%3C%2FTD%3E%3CTD%3EW%3C%2FTD%3E%3CTD%3EW%3C%2FTD%3E%3CTD%3EW%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EW%3C%2FTD%3E%3CTD%3EW%3C%2FTD%3E%3CTD%3EW%3C%2FTD%3E%3CTD%3EW%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPerson%202%3C%2FTD%3E%3CTD%3E45%3C%2FTD%3E%3CTD%3Eepn%3C%2FTD%3E%3CTD%3ED%3C%2FTD%3E%3CTD%3ED%3C%2FTD%3E%3CTD%3ED%3C%2FTD%3E%3CTD%3ED%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3ED%3C%2FTD%3E%3CTD%3ED%3C%2FTD%3E%3CTD%3ED%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPerson%203%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3Ena%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPerson%204%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EE%3C%2FTD%3E%3CTD%3EE%3C%2FTD%3E%3CTD%3EE%3C%2FTD%3E%3CTD%3EE%3C%2FTD%3E%3CTD%3EE%3C%2FTD%3E%3CTD%3EE%3C%2FTD%3E%3CTD%3EE%3C%2FTD%3E%3CTD%3EE%3C%2FTD%3E%3CTD%3EE%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPerson%205%3C%2FTD%3E%3CTD%3E10%3C%2FTD%3E%3CTD%3ENA%3C%2FTD%3E%3CTD%3EH%3C%2FTD%3E%3CTD%3EH%3C%2FTD%3E%3CTD%3EH%3C%2FTD%3E%3CTD%3EH%3C%2FTD%3E%3CTD%3EH%3C%2FTD%3E%3CTD%3EH%3C%2FTD%3E%3CTD%3EH%3C%2FTD%3E%3CTD%3EH%3C%2FTD%3E%3CTD%3EH%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPerson%206%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EH%3C%2FTD%3E%3CTD%3EH%3C%2FTD%3E%3CTD%3ED%3C%2FTD%3E%3CTD%3ED%3C%2FTD%3E%3CTD%3ED%3C%2FTD%3E%3CTD%3ED%3C%2FTD%3E%3CTD%3EH%3C%2FTD%3E%3CTD%3EH%3C%2FTD%3E%3CTD%3EH%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPerson%207%3C%2FTD%3E%3CTD%3E24%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPerson%208%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EH%3C%2FTD%3E%3CTD%3EH%3C%2FTD%3E%3CTD%3EH%3C%2FTD%3E%3CTD%3EH%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPerson%209%3C%2FTD%3E%3CTD%3E15%3C%2FTD%3E%3CTD%3Ena%3C%2FTD%3E%3CTD%3ER%3C%2FTD%3E%3CTD%3ER%3C%2FTD%3E%3CTD%3ER%3C%2FTD%3E%3CTD%3ER%3C%2FTD%3E%3CTD%3ER%3C%2FTD%3E%3CTD%3ER%3C%2FTD%3E%3CTD%3ER%3C%2FTD%3E%3CTD%3ER%3C%2FTD%3E%3CTD%3ER%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPerson%2010%3C%2FTD%3E%3CTD%3E12%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3ER%3C%2FTD%3E%3CTD%3ER%3C%2FTD%3E%3CTD%3ER%3C%2FTD%3E%3CTD%3ER%3C%2FTD%3E%3CTD%3ER%3C%2FTD%3E%3CTD%3ER%3C%2FTD%3E%3CTD%3ER%3C%2FTD%3E%3CTD%3ER%3C%2FTD%3E%3CTD%3ER%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-562736%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-562778%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-562778%22%20slang%3D%22en-US%22%3Eplease%20create%20a%20sample%20excel%20with%20manual%20input%20(indicating%20your%20result)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-562801%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-562801%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHopefully%20attached!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-562846%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-562846%22%20slang%3D%22en-US%22%3Ewhat%20is%20the%20purpose%20of%20c%20column%2C%20how%20did%20you%20get%20the%20number%2012%2C%2045%2C%200%20....%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-562870%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-562870%22%20slang%3D%22en-US%22%3E%3CP%3EColumn%20C%20is%20an%20overtime%20count%20in%20hours.....so%20it%20will%20increase%20as%20more%20overtime%20is%20done.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-562887%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-562887%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F341220%22%20target%3D%22_blank%22%3E%40simont485%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewhy%20this%20four%20person%20in%20this%20list%3F%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3ESo%20for%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B02%2F01%2F2001%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPerson%206%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPerson%208%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPerson%2010%3C%2FTD%3E%3CTD%3E12%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPerson%207%3C%2FTD%3E%3CTD%3E24%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-562906%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-562906%22%20slang%3D%22en-US%22%3ELet%20me%20interpret%20your%20requirements%20this%20way%3A%3CBR%20%2F%3E1.%20You%20want%20to%20know%20which%20persons%20are%20available%20on%20a%20specific%20date%3B%3CBR%20%2F%3E2.%20A%20person%20is%20available%20if%20the%20entry%20as%20of%20such%20date%20is%20blank%2C%20%22H%22%2C%20or%20%22R%22%3B%3CBR%20%2F%3E3.%20Given%20the%20date%20in%20B19%2C%20the%20available%20persons%20are%20listed%20starting%20in%20B20%3B%20and%3CBR%20%2F%3E4.%20The%20list%20must%20be%20sorted%20in%20ascending%20order%20according%20to%20the%20corresponding%20value%20of%20the%20available%20persons%20in%20Column%20C.%3CBR%20%2F%3EPlease%20comment%20on%20my%20foregoing%20interpretation.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-562926%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-562926%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F130464%22%20target%3D%22_blank%22%3E%40Gourab%20Dasgupta%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGood%20point...there%20should%20only%20be%203%20people%20in%20that%20list!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPerson%2010%20should%20not%20be%20there!!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'd%20input%20the%20date%20in%20B19%20and%20the%20staff%20who%20have%20the%20lowest%20count%20from%20column%20C%20would%20appear%20when%20column%20D%20is%20blank%20and%20the%20corresponding%20column%20under%20the%20entered%20date%20is%20blank%20or%20contains%20H%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-562935%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-562935%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20is%20correct!%20Except%20point%202..........Blank%20and%20H%20only....not%20R.%20(my%20mistake.)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-563135%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-563135%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20been%20trying%20Index%2FMatch%2F%20aggregate......but%20I%20am%20having%20trouble%20with%20that%20approach.......%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-563313%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-563313%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F341220%22%20target%3D%22_blank%22%3E%40simont485%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20go%20through%20the%20excel%20file%20hope%20this%20will%20fulfill%20your%20criteria.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-563402%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-563402%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F130464%22%20target%3D%22_blank%22%3E%40Gourab%20Dasgupta%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20is%20amazing%20but%20not%20quite%20as%20I%20need%20it.......%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20Column%20D%20contains%20N%2FA%20and%20Columns%20E%20to%20M%20are%20Blank%20or%20H%20then%20that%20person%20is%20Not%20Available%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%20as%20columns%20D%20to%20M%20are%20updated%20I%20need%20to%20see%20the%20list%20update%20accordingly%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
simont485
Occasional Contributor

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

 

ABCDEFGHIJKL
   01/01/200102/01/200103/01/200104/01/200105/01/200106/01/200107/01/200108/01/200109/01/2001
Person 112 WWWW WWWW
Person 245epnDDDD  DDD
Person 30na         
Person 44 EEEEEEEEE
Person 510NAHHHHHHHHH
Person 60 HHDDDDHHH
Person 724          
Person 80   HHHH   
Person 915naRRRRRRRRR
Person 1012 RRRRRRRRR
11 Replies
please create a sample excel with manual input (indicating your result)

 

Hopefully attached!!

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

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

 

 

@simont485 

 

why this four person in this list?

So for 
 02/01/2001
Person 60
Person 80
Person 1012
Person 724
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.

@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

@Twifoo 

 

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

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

@simont485 

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

@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

 

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