• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 89
  • Last Modified:

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.
0
David Bigelow
Asked:
David Bigelow
  • 6
  • 5
1 Solution
 
Blue Street TechLast KnightCommented:
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!
0
 
Rob HensonFinance AnalystCommented:
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.
0
 
David BigelowStaff Operations SpecialistAuthor Commented:
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?
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Rob HensonFinance AnalystCommented:
Look at using the Subtotal feature to apply the grouping. Select the whole data set and then the wizard is on the data group as well and will expect 3 inputs:
1 - column in which to look for change (Project Name). Only choose one.
2 - method of summarisation (sum, count, average etc). You can use Min or Max
3 - columns on which to apply the subtotal. Only choose one for now, maybe the start date column and use Min in option 2.
Other settings then choose whether the summary will be above or below each group. Click ok. If you have lots of data it might take a few seconds to run.

This will insert a row between each group and apply the subtital formula to the column you chose and put a Subtotal title in the previous column. It will also add the grouping in the rows margin. There will also be number buttons in the top left which will allow collapsing/expanding of all groups to the same level; probably just 1 & 2 at the minute.

The syntax of the subtotal formula will be:
=SUBTOTAL(#,Range)

where # is a number. This number determines the summarisation style. I don't  recall them all but the system help will list them; I recall 3 being count and 9 being sum. In this summary row you can copy the subtotals across and amend to the correct summarisation.

To do that do not use the collapse button just yet. Apply a filter to the column with the subtotal title and filter so that only rows containing total ate visible. You can then highlight the subtotal formulas and drag right to copy them across. Doing this while filtered will only affect the visible rows whereas doing the same when collapsed will affect the rows between as well. When you have decided which subtotal function you need in each column, select each column and use find and replace to adjust the formulas:

Find:  SUBTOTAL(#     where # is wrong number
Replace:  SUBTOTAL($   where $ is correct number

With all the data collapsed to the summary level you can now sort on the summary values and the detail data will move with its summary.

Hope that gets you where want to be.
0
 
David BigelowStaff Operations SpecialistAuthor Commented:
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.
0
 
Rob HensonFinance AnalystCommented:
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.
0
 
David BigelowStaff Operations SpecialistAuthor Commented:
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.
0
 
Rob HensonFinance AnalystCommented:
Thats the collapse all groups and sort that I was referring to.
0
 
David BigelowStaff Operations SpecialistAuthor Commented:
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.
0
 
Rob HensonFinance AnalystCommented:
Only other option that I can think of is to use a Pivot Table.
1
 
David BigelowStaff Operations SpecialistAuthor Commented:
Thanks for walking me through this.
0
 
David BigelowStaff Operations SpecialistAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now