Hierarchical Table - how to include categories when filtering on details

[GJWhite]

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
	B	C	D	E	F	G	H	I	J	K
2	Title	Team	Family	Item	Family Tree	Prime Resp	Approvers	Priority	PDCA Status	Target Date
3	01 - SYSTEMS	01	00	00	Systems
4	Best Practice & Statutory Requirements	01	01	00	Sys/Best Practice etc
5	Notes from EMyth on System Design	01	01	01	Sys/Best Practice etc	Graham	ghe, jau	1	I	02/03/2022
6	Notes from "This is Lean".	01	01	04	Sys/Best Practice etc	Helena	jus,brs	1	P	02/10/2021
7	Organisation Design	01	02	00	Sys/Org Design
8	Systems & Policies Family Tree & Tracker	01	02	01	Sys/Org Design	Helena	ghe, jau	1	C	04/07/2021
9	Organogram	01	02	02	Sys/Org Design	David	jus,brs	2	A	23/09/2021
10	Systems Schematic	01	02	03	Sys/Org Design	Graham	hju, tre	3	A	04/07/2021
11	Flowchart Sample and Template	01	02	04	Sys/Org Design	Danny	ghe, jau	2	A	23/09/2021
12	Company Dictionary (Glossary, Abreviations, Definitions)	01	02	11	Sys/Org Design	David	jus,brs	1	P	08/08/2021
13	Security Protocols - Physical	01	06	00	Sys/Sec Ptrotolcols (phys)
14	Use of keys	01	06	01	Sys/Sec Ptrotolcols (phys)	David	kiw	1	I	23/09/2021
15	Security gaurd protocols	01	06	02	Sys/Sec Ptrotolcols (phys)	David	gla, net	1	D	08/08/2021
16	02 - BUSINESS AND PRODUCT DEVELOPMENT	02	00	00	Bus/Prod Dev
17	Marketing & Branding	02	02	00	Bus/Prod Dev - Mktg
18	Brand Structure	02	02	01	Bus/Prod Dev - Mktg	Graham	the, jab	2	D	23/09/2021
19	Brand Guidelines & Assets	02	02	02	Bus/Prod Dev - Mktg	Danny	hyw, nod	2	P	04/07/2021
20	Business Strategy and Development Templates	02	07	00	Bus/Prod Dev - Bus Strat
21	SWOT Template	02	07	01	Bus/Prod Dev - Bus Strat	Helena	kiw	3	A	04/07/2021
22	Business Model Canvass Template	02	07	02	Bus/Prod Dev - Bus Strat	Danny	gla, net	2	A	23/09/2021
23	03 - OPERATIONS	03	00	00	Operations
24	Production	03	02	00	Ops/Production	David	hju, tre	1
25	ProductionForecast&Plan	03	02	01	Ops/Production	David	ghe, jau	2	C	04/07/2021
26	Production Track & Report & LE	03	02	02	Ops/Production	David	jus,brs	1	C	23/09/2021
27	Factory Space Management	03	02	03	Ops/Production	Helena	juh	3	I	04/07/2021
28
mini sheet example

Cells with Data Validation
Cell	Allow	Criteria
J3:J10	List	='Reference Info'!$B$20:$B$24
J12:J28	List	='Reference Info'!$B$20:$B$24
K7:K28	Date	>01/01/2021

 

[severynm]

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.

 

[GJWhite]

That's really helpfull! Thanks for taking the time to read and reply, much appreciated.