Grouping projects and sorting by date
I'd like to be able to auto-sort these tasks based on:
Grouped by project with the task of the following criteria indicating which group of projects is listed first.
Priority
Then by oldest dates of: Actual Start, Actual Finish, Planned Start, Planned Finish
Tasks within the project should be sorted in the same order.
When I add or change any of the dates, I want the spreadsheet to re-sort (I suppose it's similar to requery).
I am comfortable with this being VBA or whichever method the expert chooses to sort them.
Grouped by project with the task of the following criteria indicating which group of projects is listed first.
Priority
Then by oldest dates of: Actual Start, Actual Finish, Planned Start, Planned Finish
Tasks within the project should be sorted in the same order.
When I add or change any of the dates, I want the spreadsheet to re-sort (I suppose it's similar to requery).
I am comfortable with this being VBA or whichever method the expert chooses to sort them.
Rob Henson
Which software are you using to keep the project lines?
Hi David,
In Excel you can use Sorting by going to Home > Editing > Sort & Filter > Filter on the top column header row. This will allow you to filter a single or group of projects and then sort those filtered project the particular column header you wish to select such as Actual Finish or Planned Start in ascending or descending order. This is all native to Excel. If you need more advanced functionality you should probably use VBA to do so.
Let me know if you have any questions!
In Excel you can use Sorting by going to Home > Editing > Sort & Filter > Filter on the top column header row. This will allow you to filter a single or group of projects and then sort those filtered project the particular column header you wish to select such as Actual Finish or Planned Start in ascending or descending order. This is all native to Excel. If you need more advanced functionality you should probably use VBA to do so.
Let me know if you have any questions!
ASKER
Hello Rob, thanks for requesting clarification. I'm using Excel 2013.
To Blue Street Tech's comment, Excel filtering is the basic functionality I'm looking for, however, not the complete functionality. When a task is first on the list, I want that whole project with all it's tasks to be the first group. And so on for each unique group of projects. When that task "Status" is marked complete, then the project with the next task in criteria order should be the first project in the list. And yes, I suspect VBA will be the method of resolution.
To Blue Street Tech's comment, Excel filtering is the basic functionality I'm looking for, however, not the complete functionality. When a task is first on the list, I want that whole project with all it's tasks to be the first group. And so on for each unique group of projects. When that task "Status" is marked complete, then the project with the next task in criteria order should be the first project in the list. And yes, I suspect VBA will be the method of resolution.
Take a look at the Group/Outline feature.
You can select rows to be grouped and it will add an expand/collapse icon in the left margin.
One additional advantage with this feature which I think you're maybe looking for, if you have all groups collapsed and then apply a sort to the data set it will only look at the visible rows to apply the sort and each group will move as a block.
So your Task summary row which will be visible when collapsed could have a Start Date (Min date of all sub-tasks), Finish Date (Max Date of all sub-tasks); Status, no doubt you could create the Status options so that they sort correctly (custom sorts are available if required) eg 1-Open, 2-In Progress, 3-Closed; those would sort in numerical order because of the first character.
You can select rows to be grouped and it will add an expand/collapse icon in the left margin.
One additional advantage with this feature which I think you're maybe looking for, if you have all groups collapsed and then apply a sort to the data set it will only look at the visible rows to apply the sort and each group will move as a block.
So your Task summary row which will be visible when collapsed could have a Start Date (Min date of all sub-tasks), Finish Date (Max Date of all sub-tasks); Status, no doubt you could create the Status options so that they sort correctly (custom sorts are available if required) eg 1-Open, 2-In Progress, 3-Closed; those would sort in numerical order because of the first character.
ASKER
Hello Rob,
As I sort the Projects by name, select a group of project task rows, and apply Group, when I select the next group of tasks by themselves, it doesn't see them as different rows and applies the same grouping, making one larger group.
Should I be using the Group feature differently?
As I sort the Projects by name, select a group of project task rows, and apply Group, when I select the next group of tasks by themselves, it doesn't see them as different rows and applies the same grouping, making one larger group.
Should I be using the Group feature differently?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hello Rob,
Without the click here, select that, I don't know enough about the menus to figure out where the wizard you mention and the subtotal menu are. I feel like you're using a version of Excel different from 2013, but that may not be. It's like I'm invited into the middle of the instructions, without the starting point.
Without the click here, select that, I don't know enough about the menus to figure out where the wizard you mention and the subtotal menu are. I feel like you're using a version of Excel different from 2013, but that may not be. It's like I'm invited into the middle of the instructions, without the starting point.
Apologies for not being clear.
After selecting all of your data select the Data tab. Towards the right hand end there is a button for Subtotal. Clicking this will start the wizard. Continue as described before.
After selecting all of your data select the Data tab. Towards the right hand end there is a button for Subtotal. Clicking this will start the wizard. Continue as described before.
ASKER
Interesting, thanks. I'm not seeing where it moves the project group to the top of the list, based on it having the oldest task of all the project groups.
Thats the collapse all groups and sort that I was referring to.
ASKER
Thanks Rob, It wasn't working because I had formatted the spreadsheet as a table. Once I removed the table range, the sorting worked, in simple format. I'd much rather be able to use the advanced sorting feature. It was my original request, maybe not stated. Is there is another way to sort by the criteria I listed? When I try to use advanced sorting, it wants to break up the subtotal groups.
Update... Maybe not as requested, but the outcome is as desired. That is, if I collapse the project groups I can use advanced sorting.
Update... Maybe not as requested, but the outcome is as desired. That is, if I collapse the project groups I can use advanced sorting.
Only other option that I can think of is to use a Pivot Table.
ASKER
Thanks for walking me through this.
ASKER
Didn't think of a pivot table. My use of Excel can be somewhat advanced, but my need to build things is far between and so I get rusty.