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.
LVL 1
David BigelowStaff Operations SpecialistAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.