# How to create a three level hierarchical view of relational columns

I've been struggling creating the following view but I'm so close.

• I have a dataset with three columns of data where each level is broader than the other
• I want to be able to filter the data dynamically by two of the columns
• I want to create a visual where you can see the grouping clearly. I managed to create the two level hierarchy but I'm struggling to get the third.
• As you will see I'm using QUERY in order to use only one formula to power everything

Here's the formula I'm using for the two level view, but how can I modify it so the output includes the three levels? Here's the spreadsheet that you can use as example, with my own function. Appreciate any input!

``````=ArrayFormula({
IF(

QUERY(SUBSTITUTE(SORT({SPLIT(UNIQUE(
FILTER(B6:B,B6:B<>"",
IF(F5="",A6:A<>"",B6:B=F5),
IF(F6="",A6:A<>"",C6:C=F6)))&"|#%","|",0,0);

FILTER({B6:B,A6:A},B6:B<>"",
IF(F5="",A6:A<>"",B6:B=F5),
IF(F6="",A6:A<>"",C6:C=F6))},1,1),"#%",""),"Select Col2")="",

QUERY(SUBSTITUTE(SORT({SPLIT(UNIQUE(
FILTER(B6:B,B6:B<>"",
IF(F5="",A6:A<>"",B6:B=F5),
IF(F6="",A6:A<>"",C6:C=F6)))&"|#%","|",0,0);

FILTER({B6:B,A6:A},B6:B<>"",
IF(F5="",A6:A<>"",B6:B=F5),
IF(F6="",A6:A<>"",C6:C=F6))},1,1),"#%",""),"Select Col1"),""),

QUERY(
SUBSTITUTE(SORT({SPLIT(UNIQUE(
FILTER(B6:B,B6:B<>"",
IF(F5="",A6:A<>"",B6:B=F5),
IF(F6="",A6:A<>"",C6:C=F6)))&"|#%","|",0,0);

FILTER({B6:B,A6:A},B6:B<>"",
IF(F5="",A6:A<>"",B6:B=F5),
IF(F6="",A6:A<>"",C6:C=F6))},1,1),"#%",""),"Select Col2")})
``````
• Try Pivot table (columns L:N in your file) in case using formula isn't strict requirement. – Oleg_S Oct 1 at 8:02
• Almost a duplicate - How would you create a dynamic visualization with two hierarchical categories? on Stack Overflow. Read this post and then comment here if you're still not sure how to proceed, BTW, is this homework? – Tedinoz Oct 1 at 8:22
• OlegS using a formula is a strict requirement but thank you for the suggestion! @tedinoz I read that post and kind of used their approach, but I need a three level vs the two level they have. It's not homework but I'm helping someone. Would really appreciate any help, thanks! – Bob Oct 1 at 17:47
• @Mel from StackOverflow seems to be using the exact same data as you - quite a co-incidence, don't you think. I wonder whether Mel has any thought on creating a third tier. – Tedinoz Oct 2 at 3:14
• @tedinoz I have different data, but copied the sheet in the other post to illustrate what I need – Bob Oct 2 at 9:58