Excel Filtering/Grouping Of Project Activities

tomfarrar
tomfarrar used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Rob HensonFinance Analyst

Commented:
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

Author

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
Finance Analyst
Commented:
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.
Ensure you’re charging the right price for your IT

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

Author

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 Analyst

Commented:
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.

Author

Commented:
Well that is nice to know.  Thank you.  - Tom

Author

Commented:
Thanks, Rob.  I appreciate your help on this.  - Tom
Rob HensonFinance Analyst

Commented:
Happy to help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial