Can I create an ordered list within a second ordered list using formulas?

[Tarver]

The 6th grade has taken a count of everyone's pets by type, then grouped them into larger categories. I'm trying to take that data and produce automatically a report that shows the count of each type of pet within each group, ordered by the largest group to the smallest.

I have a table that shows the count of each pet type:

Book1
	A	B	C
1	Pet Type	Pet Group	Count
2	Mutt	Dog	38
3	Mixed Breed	Cat	28
4	Tabby	Cat	24
5	Terrier	Dog	12
6	Goldfish	Fish	6
7	Boxer	Dog	4
8	Guppy	Fish	4
9	Mastiff	Dog	3
10	Python	Reptile	3
11	Guinea Pig	Rodent	3
12	Siamese	Cat	2
13	Iguana	Reptile	2
14	Mouse	Rodent	2
15	Oscar	Fish	2
16	Mexican Hairless	Cat	1
17	Bullfrog	Reptile	1
Sheet1

I also have a summary table that adds up each group's total and finds their rank:

Book1
	E	F	G
1	Pet Group	Total Count	Rank
2	Dogs	57	1
3	Cats	55	2
4	Fish	12	3
5	Reptiles	6	4
6	Rodents	5	5
Sheet1

This is what my output should look like:

Book1
	I	J
1	Group/Type	Total Count
2	Dogs
3	Mutt	38
4	Terrier	12
5	Boxer	4
6	Mastiff	3
7	Cats
8	Mixed Breed	28
9	Tabby	24
10	Siamese	2
11	Mexican Hairless	1
12	Fish
13	Goldfish	6
14	Guppy	4
15	Oscar	2
16	Reptiles
17	Python	3
18	Iguana	2
19	Bullfrog	1
20	Rodents
21	Guinea Pig	3
22	Mouse	2
Sheet1

I'm trying to write a formula that starts with the Title of the top group, then lists (in order) the type of pets in that group, then displays the name of the second group, etc., etc. until all of the groups and types are listed, in order.

Can this be done formulaically, instead of with VBA?

 

[daregu]

Unless I'm missing something. I think you could just make a pivot table off the first table you listed and just group the items. I attached what I did on the pivot table and it's set to compact view. Then you can just add data to the table and refresh the pivot table for updates.

 

[Tarver]

There are a couple of issues with doing this via a pivot table. First, the pivot would create an additional column for the Group (or would offset as in your example) limiting what other formulas could do, and second, it would require updating a pivot manually and selecting new groups if (for example) an Aquatic Mammal category were created to add someone's dolphin.

 

[Fluff]

In what way is the pivot different to what you showed you were after?

 

[daregu]

There are a couple of issues with doing this via a pivot table. First, the pivot would create an additional column for the Group (or would offset as in your example) limiting what other formulas could do, and second, it would require updating a pivot manually and selecting new groups if (for example) an Aquatic Mammal category were created to add someone's dolphin.

The groups in that pivot table aren't custom, they are coded into the information from the table so you just have to add the new information and it will auto update on refresh. The indent is easy to remove in pivot table options as seen in the screen shot, I'm not sure what you mean by additional column from the pivot table. I edited the second screenshot so you can see there's only two columns. You have to change the view to Tabular and then Compact and it looks like what I have.

 

[Tarver]

In what way is the pivot different to what you showed you were after?

There are often new categories added to this data. When that happens, they are usually not included in the pivot. Someone has to go in an manually check "Aquatic Mammals" to add that new group (or whatever the new group might be.) That's not hard for you or me, but for a 6th grader or a CEO, it's often too much to do or too hard to do, and it leaves me reviewing the self-serve report and updating it manually. (Clearly the 6th grader is more capable, but not always the CEO.)

I could select all categories automatically on opening with a VBA macro, but VBA isn't allowed in this particular environment for security reasons.

 

[Eric W]

It can be done, but I don't know if you'd want to maintain a pretty complicated formula:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Pet Type	Pet Group	Count		Pet Group	Total Count	Rank		Group/Type	Total Count
2	Mutt	Dog	38		Dog	57	1		Dog
3	Mixed Breed	Cat	28		Cat	55	2		Mutt	38
4	Tabby	Cat	24		Fish	12	3		Terrier	12
5	Terrier	Dog	12		Reptile	6	4		Boxer	4
6	Goldfish	Fish	6		Rodent	5	5		Mastiff	3
7	Boxer	Dog	4						Cat
8	Guppy	Fish	4						Mixed Breed	28
9	Mastiff	Dog	3						Tabby	24
10	Python	Reptile	3						Siamese	2
11	Guinea Pig	Rodent	3						Mexican Hairless	1
12	Siamese	Cat	2						Fish
13	Iguana	Reptile	2						Goldfish	6
14	Mouse	Rodent	2						Guppy	4
15	Oscar	Fish	2						Oscar	2
16	Mexican Hairless	Cat	1						Reptile
17	Bullfrog	Reptile	1						Python	3
18									Iguana	2
19									Bullfrog	1
20									Rodent
21									Guinea Pig	3
22									Mouse	2
23
Sheet1

Cell Formulas
Range	Formula
E2:F6	E2	=SORT(UNIQUE(CHOOSE({1,2},B2:B17,SUMIF(B2:B17,B2:B17,C2:C17))),2,-1)
G2:G6	G2	=SEQUENCE(ROWS(E2#))
I2:I22	I2	=SORTBY(IFERROR(INDEX(A2:A17,SEQUENCE(ROWS(A2:A17)+ROWS(E2#))),INDEX(E2#,SEQUENCE(ROWS(A2:A17)+ROWS(E2#))-ROWS(A2:A17),1)),IFERROR(INDEX(C2:C17,SEQUENCE(ROWS(A2:A17)+ROWS(E2#)))+1000*(20-MATCH(B2:B17,INDEX(E2#,0,1),0)),1000*(21-(SEQUENCE(ROWS(A2:A17)+ROWS(E2#))-ROWS(A2:A17)))),-1)
J2:J22	J2	=IF(COUNTIF(B2:B17,I2#),"",VLOOKUP(I2#,A2:C17,3,0))
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I:J	Expression	=MATCH($I1,$B$2:$B$17,0)	text	NO

The I2 formula also contains some "magic" numbers you should be aware of. 1000 (used twice) is just a big number larger than any Count you'd expect to see. 20 and 21 are 2 numbers (which should always be 1 apart) that are bigger than the number of Pet Groups you have.