Extract all rows from a range that meet criteria in columns

[Sf1802]

Hi, i have a list of dataset from A1 to AZ200.
The row A1 to A200 are list of projects that company has. Columns are name of the officers in the company and also the months of each year (Jan-Dec18).
The excel spreadsheet keep tracks of the monthly revenue and expenses of each project as well as the no. of utilisation days of each officer. However, different officers are involved in different projects so some of the columns cells will be empty.
I wanted have a list that shows the total utilisation days of each officer with breakdown of the no. of days and the project title. Also each officer will have different minimum utilisation days that they need to meet, so with the list I can do comparison and do up a pivot table.
Currently I have to manually filter the name of each officer with the utilisation days and copy the project title and days into another sheet. Would like to check if there is a formula that I can auto list out the projects with the breakdown utilisation days that they are involved in? Because every month the no. of utilisations days and list of projects will be updated. I hope there is something I can do to automate this. Any advise will be greatly appreciated. Thank you!

 

[Sf1802]

Hello, please find the sample data file here. I have incorporate it into the sample file which you have helped to create previously. The file can be found in this link - https://www.dropbox.com/s/pjm3q0wbhg7puv7/Utilisation days w_criteria.xlsx?dl=0
In the “Programmes” tab, I was hoping that once the officer update the run(s) in column AJ5 to BG46, it will automatically calculate the no. of utilisation days based on the no. of run for the month*BP (no. Of man efforts hrs per run).
There is also an internal trainer column, where I would like to calculate the no. Of utilisations days based on who is the trainer for the run. But the challenging part is that the trainer may be different for each run. I’m not sure how I can improve my excel to make this work better. Hope you can advise. If not I will then have to update the days manually.

Most critical one, is I would like to combine the utilisation days for both worksheets - “For Pivot” and “For Pivot Prog” into one pivot table. Hope you can let me know how I can can do it. Thanks so much

 

[RasGhul]

Hi Sf,

I'm not sure how to achieve utilization days based on month & trainer.

Unfortunately there's no formula solution for combining these 2 sheets, but you could stack both tables onto a new sheet and then create a pivot that looks at that combined data set.

You maybe need to consider a VBA solution to merge both sheets into one for you.

 

[Sf1802]

Hi, My office’s excel version can’t run VB. So is okie, I guess I will just stack the 2 tables and create a new pivot table using the combined dataset. Thank you for your advice once again