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

Tarver

Board Regular
Joined
Nov 15, 2012
Messages
85
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

daregu

New Member
Joined
Jun 16, 2021
Messages
24
Office Version
  1. 365
Platform
  1. Windows
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
    Sort.png
    49.4 KB · Views: 7

Tarver

Board Regular
Joined
Nov 15, 2012
Messages
85
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
Joined
Jun 12, 2014
Messages
64,100
Office Version
  1. 365
Platform
  1. Windows
In what way is the pivot different to what you showed you were after?
 

daregu

New Member
Joined
Jun 16, 2021
Messages
24
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
    sort_2.png
    31.9 KB · Views: 2
  • sort_3.png
    sort_3.png
    34.5 KB · Views: 2

Tarver

Board Regular
Joined
Nov 15, 2012
Messages
85
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
Joined
Aug 18, 2015
Messages
11,553
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.
 

Forum statistics

Threads
1,144,583
Messages
5,725,132
Members
422,590
Latest member
Mikeyyy

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top