CODEWITHSUNDEEP
google-sheets
Chris Y
Chris Y

Reputation: 121

Google Sheets Hierarchical Category/Sub-Category Display Formula

I have a table of data such as

A       B    C
Cat    Fur   25
Snake  Fur   10
       Fur   11
       Coat  9

In another sheet, I wanted Column A to factor in the categories above such that it outputs the following:

A        B
Fur      11
  Cat    25
  Snake  10
Coat     9

I can't use a Pivot Table to do this and am trying to avoid a custom function, but will do a custom function if I have to. I am a little lost on how I can have Column A appear using a formula and, even better, indents.

Upvotes: 0

Views: 3133

Answers (2)

teylyn
teylyn

Reputation: 35915

If your pivot table does not return the result you want, then your data source is not structured correctly.

Furr has the value of 11 as a sub category of animal, where it is NOT snake and NOT cat, but blank.

enter image description here

Upvotes: 0

Tom Sharpe
Tom Sharpe

Reputation: 34180

I did manage to achieve the appearance of your table with repeated sorting:

=ArrayFormula(filter(if(sort(A:A,B:B&A:A,true)="",sort(B:C,B:B&A:A,true),sort({"  "&A:A,C:C},B:B&A:A,true)),sort(B:B,B:B&A:A,true)<>""))

enter image description here

except that Coat appears before Fur because they are sorted alphabetically.

The issue here was that if I had sorted by column B then column A separately, the rows with a blank in column A would have appeared last in each group which is not what OP asked for. The workaround was to concatenate each cell in column B with the corresponding cell in column A and sort on the resulting values (FurCat, FurSnake, Fur and Coat) so that the list was in the correct order (Coat, Fur, FurCat and FurSnake).

Upvotes: 1

Related Questions