Hierarchical Table - how to include categories when filtering on details

GJWhite

New Member
Joined
Jun 18, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
I have a table that contains three levels of data - effectively "category", "sub category" and "item". Something like this:

Row 1: Category 1
Row 2: Sub category 1.1
Row 3: Data item 1
Row 4: Data item 2
Row 5: Sub Category 1.2
Row 6: Data item 3
Row 7: Data item 4
Row 8: Category 2
Row 9: Sub category 2.1
Row 10: Data item 5
Row 11: Data item 6
Row 12: Data item 7

Each row has columns such as "Status", "Owner" "Approver" etc (which only apply to the individual data items) and I have slicers so that I can easily filter on such columns. However, because the category and sub category rows do not have this data completed The category and sub-category rows associated with the filtered data items are lost.

Is there a way to show "hierarchical" or "parent" rows so that, for example, if a filter selects data item 3 in row 6 it also displays Row 1 and Row 5 in the example above. I realise this may be a coding requirement rather than a filtering requirement but I'm not sure where to start).

I have added a "mini sheet" of example data below. Note the three columns called Team, Family and Item - "Item" is always zero if a category or sub-category (all data items have a value >0) - not sure if this helps.

If anyone has any ideas please let me know...

Thanks,

Graham


01.01. OverallSystemsTracker - STATUS REPORT.xlsx
BCDEFGHIJK
2TitleTeamFamilyItemFamily TreePrime RespApproversPriorityPDCA StatusTarget Date
301 - SYSTEMS010000Systems
4Best Practice & Statutory Requirements010100Sys/Best Practice etc
5Notes from EMyth on System Design010101Sys/Best Practice etcGrahamghe, jau1I02/03/2022
6Notes from "This is Lean".010104Sys/Best Practice etcHelenajus,brs1P02/10/2021
7Organisation Design010200Sys/Org Design
8Systems & Policies Family Tree & Tracker010201Sys/Org DesignHelenaghe, jau1C04/07/2021
9Organogram010202Sys/Org DesignDavidjus,brs2A23/09/2021
10Systems Schematic010203Sys/Org DesignGrahamhju, tre3A04/07/2021
11Flowchart Sample and Template010204Sys/Org DesignDannyghe, jau2A23/09/2021
12Company Dictionary (Glossary, Abreviations, Definitions)010211Sys/Org DesignDavidjus,brs1P08/08/2021
13Security Protocols - Physical010600Sys/Sec Ptrotolcols (phys)
14Use of keys010601Sys/Sec Ptrotolcols (phys)Davidkiw1I23/09/2021
15Security gaurd protocols010602Sys/Sec Ptrotolcols (phys)Davidgla, net1D08/08/2021
1602 - BUSINESS AND PRODUCT DEVELOPMENT020000Bus/Prod Dev
17Marketing & Branding020200Bus/Prod Dev - Mktg
18Brand Structure020201Bus/Prod Dev - MktgGrahamthe, jab2D23/09/2021
19Brand Guidelines & Assets020202Bus/Prod Dev - MktgDannyhyw, nod2P04/07/2021
20Business Strategy and Development Templates020700Bus/Prod Dev - Bus Strat
21SWOT Template020701Bus/Prod Dev - Bus StratHelenakiw3A04/07/2021
22Business Model Canvass Template020702Bus/Prod Dev - Bus StratDannygla, net2A23/09/2021
2303 - OPERATIONS030000Operations
24Production030200Ops/ProductionDavidhju, tre1
25ProductionForecast&Plan030201Ops/ProductionDavidghe, jau2C04/07/2021
26Production Track & Report & LE030202Ops/ProductionDavidjus,brs1C23/09/2021
27Factory Space Management030203Ops/ProductionHelenajuh3I04/07/2021
28
mini sheet example
Cells with Data Validation
CellAllowCriteria
J3:J10List='Reference Info'!$B$20:$B$24
J12:J28List='Reference Info'!$B$20:$B$24
K7:K28Date>01/01/2021
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

severynm

Board Regular
Joined
Jan 8, 2021
Messages
220
Office Version
  1. 365
Platform
  1. Windows
Welcome!

You might try tweaking the structure of your data; what you did with columns C:E is a great start. What I would recommend doing is replacing your numerical codes in those columns with the actual names of the categories and subcategories, and then removing the rows with the subcategories altogether, as they no longer contain any useful information. This puts your data in a tabular data structure, where every row has the same number of columns in the same order (in your table above, row 1-2 has 5 columns and row 5 has 10 columns, for example).

If you do that and throw the data table into a Pivot Table, you can add slicers as required to "filter" to your desired specifications, for example showing all Titles with Approvers="ghe, jau" and Priority=1.

I'm not entirely clear on what you wanted to do, but hopefully this is in the right direction.

1624305610133.png
 
Solution

GJWhite

New Member
Joined
Jun 18, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
That's really helpfull! Thanks for taking the time to read and reply, much appreciated.
 

Forum statistics

Threads
1,141,051
Messages
5,703,960
Members
421,321
Latest member
blusky4

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top