Excel Filtering/Grouping Of Project Activities

Excel is the tool I am using.  I need to group/filter project activities so to show summary at project level, and details for anyone project.  I have tried Excel functions but without any luck.  Need to accomplish this without VBA if possible.  Any thoughts would be helpful.  The attached spreadsheet shows what I am trying to do.  Let me know if you have questions.  Any thoughts would be helpful.  Thanks.  - Tom
EE.xlsx
LVL 7
tomfarrarAsked:
Who is Participating?
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.

Rob HensonFinance AnalystCommented:
One way that I can think of is using the row grouping feature, see attached.

I have applied the grouping manually  but there are ways of doing it automatically if the real data set is much bigger.

Use the buttons in the left hand margin to collapse/expand individual groups or the number buttons at the top of the left hand margin to collapse/expand all groups to a specific level.
EE.xlsx
tomfarrarAuthor Commented:
Hi Rob - Sorry for the delay.  Your solution seems to work, I was trying but had a hard time getting there.  Is there a particular way to sequence the grouping steps?  Thanks.  - Tom
Rob HensonFinance AnalystCommented:
As I mentioned, I did it manually using the grouping options on the data tab; you select the rows which you want to group and then select group from the menu option and confirm by selecting rows and click OK. If you click the bottom right corner of the Outline Group on the Data tab it will open the Outline settings. I chose to deselect the option for Summary Details below the data so that the top row became the summary row. When selecting the rows to group, do not select the row which you want left visible, ie your 1.0 or 2.0 rows.

With the 7 groups it was easy to do manually but I suspect your true data is much bigger.

To enable automatic grouping I would structure your data slightly differently. I would add a column for primary group so that for example all items in group 1.0 have 1.0 in the Primary Group column; then any automated application of grouping can detect the change in group.

There is an option in the Group menu to apply an Auto Outline but I have never had much luck with it; possibly due to incompatible data.

I have been known to use the Subtotal wizard to apply the grouping as that is one of the features of that wizard but again it does need to recognise a change in a data field to apply the grouping. With this method a row is inserted between each group to hold the subtotal and act as the summary row when collapsed. The subtotals can be deleted quite easily, however, if you want the grouping on your first row of each group then that method may not be suitable.

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
Price Your IT Services for Profit

Managed service contracts are great - when they're making you money. Yes, you’re getting paid monthly, but is it actually profitable? Learn to calculate your hourly overhead burden so you can master your IT services pricing strategy.

tomfarrarAuthor Commented:
Thanks, Rob.   The spreadsheet is not mine so I can't change it (though I can suggest changes).  The spreadsheet is not terribly large at this point, but could be someday.  I haven't done a lot of "grouping" in Excel so the process is a bit foreign to me.  My concern, and a major reason for the request, is what happens if you group 5 rows together under 1.0.  So there is 1.1, 1.2 &1.3.  But a week later there is 1.4 and 1.5.  Does the person then need to go back and group rows again?  Doesn't appear very functional when expansion is involved, which happens often.  Having said that I was hoping there might be some other Excel functionality I was not aware of

Your input has been very helpful, and I will be closing out this question as I get comfortable with working with the grouping.  I want to be sure I fulling understand how it works, and does not work, so there is no need to submit a followup question.  Thanks again Rob.  - Tom
Rob HensonFinance AnalystCommented:
If new rows are required within a group, when rows are inserted within or immediately below the group the new row(s) will be included in the group automatically.
tomfarrarAuthor Commented:
Well that is nice to know.  Thank you.  - Tom
tomfarrarAuthor Commented:
Thanks, Rob.  I appreciate your help on this.  - Tom
Rob HensonFinance AnalystCommented:
Happy to help
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 Excel

From novice to tech pro — start learning today.