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

#### Tarver

##### Board Regular
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
ABC
1Pet TypePet GroupCount
2MuttDog38
3Mixed BreedCat28
4TabbyCat24
5TerrierDog12
6GoldfishFish6
7BoxerDog4
8GuppyFish4
9MastiffDog3
10PythonReptile3
11Guinea PigRodent3
12SiameseCat2
13IguanaReptile2
14MouseRodent2
15OscarFish2
16Mexican HairlessCat1
17BullfrogReptile1
Sheet1

I also have a summary table that adds up each group's total and finds their rank:
Book1
EFG
1Pet GroupTotal CountRank
2Dogs571
3Cats552
4Fish123
5Reptiles64
6Rodents55
Sheet1

This is what my output should look like:
Book1
IJ
1Group/TypeTotal Count
2Dogs
3Mutt38
4Terrier12
5Boxer4
6Mastiff3
7Cats
8Mixed Breed28
9Tabby24
10Siamese2
11Mexican Hairless1
12Fish
13Goldfish6
14Guppy4
15Oscar2
16Reptiles
17Python3
18Iguana2
19Bullfrog1
20Rodents
21Guinea Pig3
22Mouse2
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?

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### daregu

##### New Member
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.

#### Attachments

• Sort.png
49.4 KB · Views: 7

#### Tarver

##### Board Regular
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

##### MrExcel MVP, Moderator
In what way is the pivot different to what you showed you were after?

#### daregu

##### New Member

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.

#### Attachments

• sort_2.png
31.9 KB · Views: 2
• sort_3.png
34.5 KB · Views: 2

#### Tarver

##### Board Regular
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

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

Book1
ABCDEFGHIJ
1Pet TypePet GroupCountPet GroupTotal CountRankGroup/TypeTotal Count
2MuttDog38Dog571Dog
3Mixed BreedCat28Cat552Mutt38
4TabbyCat24Fish123Terrier12
5TerrierDog12Reptile64Boxer4
6GoldfishFish6Rodent55Mastiff3
7BoxerDog4Cat
8GuppyFish4Mixed Breed28
9MastiffDog3Tabby24
10PythonReptile3Siamese2
11Guinea PigRodent3Mexican Hairless1
12SiameseCat2Fish
13IguanaReptile2Goldfish6
14MouseRodent2Guppy4
15OscarFish2Oscar2
16Mexican HairlessCat1Reptile
17BullfrogReptile1Python3
18Iguana2
19Bullfrog1
20Rodent
21Guinea Pig3
22Mouse2
23
Sheet1
Cell Formulas
RangeFormula
E2:F6E2=SORT(UNIQUE(CHOOSE({1,2},B2:B17,SUMIF(B2:B17,B2:B17,C2:C17))),2,-1)
G2:G6G2=SEQUENCE(ROWS(E2#))
I2:I22I2=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:J22J2=IF(COUNTIF(B2:B17,I2#),"",VLOOKUP(I2#,A2:C17,3,0))
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
I:JExpression=MATCH(\$I1,\$B\$2:\$B\$17,0)textNO

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.

Replies
4
Views
134
Replies
7
Views
114
Replies
5
Views
138
Replies
3
Views
266
Replies
7
Views
598

1,148,173
Messages
5,745,179
Members
423,931
Latest member
thangvan114

### 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.

### Which adblocker are you using?

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

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