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!

 

[RasGhul]

Hi SF,

Can you post some sample data showing what you want the result to look like?

 

[Sf1802]

Hi, please find the sample file here - https://www.dropbox.com/s/x4ac34ch2hmcum6/Sample file - Utilisation.xls?dl=0
There are 3 tabs to the file, the 1st tab is the data source, 2nd tab is the list that currently I need to manually extract from the data source, by filtering each officer name with utilisation days and copy out the relevant info. The 3rd tab is the pivot table. Greatly appreciate your advice on how I can do this better. Thank you!

 

[RasGhul]

Hi Sf,

I've transposed the whole days range(matrix) into 1 column and then repeated that for your Project/Cost Centre/Source of Funding etc which in effect is transposing all of your columns into a ledger style database. This means no more copying as we're indexing the whole area into your pivot.

In the For Pivot tab enter number of rows & columns to calculate how many total rows you need to copy the formulas down as you add rows to "FY19". e.g your sample data is 10 rows x 46 columns = 460 total rows. Your actual file is 128 rows x 46 columns = 5888 total rows - so you'll need to copy the formulas down to here.

Also make sure that you increase INDEX('FY19'!$B$2:$B$129 this number if you add rows or you will get a ref error on the relevant formulas. You will also need to update the pivot data range with the select data option when applying to your sheet. As we're using formulas - as you update hours to officers this will be reflected in the pivot data, hit refresh all to update pivot table.

The pivot table Officers are sorted a-z and value greater than zero only, clear or adjust as needed.

Here's a link to my mock up if you can use it,

https://www.dropbox.com/s/4megusm59bysdum/Utilisation days w_criteria.xlsx?dl=0

 

[Sf1802]

Hi, thank you so much for your mock up! It works, but could you help me understand the formula? How does the Roundup (rows) and Mod (rows) works? I have tried to Google, but I still can’t understand. Hope you can help to enlighten me

 

[RasGhul]

You're welcome.

I saw the following from Excelisfun a while back and thought it could be useful on your sheet. Mike it explains it thoroughly

https://www.youtube.com/watch?v=uKeFMVwpSmQ&feature=youtu.be

 

[Sf1802]

Thank you! This is really helpful! I will go work on my actual workbook and hopefully I’m able to get the same results!

 

[RasGhul]

Excellent just be careful to adjust the "for pivot" formulas to suit your expanding range.

Thanks for the feedback.

 

[Sf1802]

Hi, I have tried out my actual spreadsheet with your formulas and it works! however, I have another datafile with a new list of programmes and the utilisation of the same list of officers. However, I can’t combined both files due to some differences in the tracking of expenses and revenue of the programmes. I now have an issue combining the 2 “For pivot” workbooks into 1 pivot table, in which they will combine the total no. of utilisation days of each officer from the 2 worksheets. I have tried using the pivot wizard and power query which I have search online, but it didn’t work, could you advise how I can do it please? Thank you!

 

[RasGhul]

Can you post a sample of the other workbook data?