Hello experts,

I have a excel sheet of about 600 rows. Column B is the description of the row, which are divided into categories, also handled in the same column. Meaning Column B has a structure of:
CategoryName1 -> ActivityGroup1 -> a set of activites over several rows, ActivityGroup2 -> a set of activities over several rows -> CategoryName2 -> ActivityGroup3-> Activites .... (see bottom of post)

All Activities in ActivityGroups are grouped using the Excel group feature. At full collapse only CategoryNames and Activity Groups are visible.

Now! Column D has a status for each row. The one i'm interested in is "expired". I have a formula in D for each ActivityGroup which sets its status to "expired" if all activites in that group is expired. Else = i just leave it as "category" in D. Same goes for CategoryName.

The trick is I want to be able to filter out all "expired" Activites, ActivityGroups, and CategoryName. E.g. If a all activites in a group are expired that entire activity group should be filtered out. If 1 activity in an activity group has a different status, then the activitygroup name should be left visible along with just that 1 one activity.

Filtering and groups dont work well together.. If i try filtering out something the groups automatically expands, and when i collpase them i overwrite the filtering process again. Also, its a bit buggy when dealing with a category where all activites arent the same status.

Obviously this is not a good approach, just wanted to throw it out here to see if anyone of you have an idea of how to better approach this?

Thanks, Glenn

CategoryName
ActivityGroup
Activity
Activity
Activity
ActicityGroup
Activity
Activity
Activity
CategoryName
ActivityGroup
Activity
...