Ordered list within an ordered list

[Tarver]

I'm trying to automate an existing manual report like the one below, that shows major categories ranked by their sales volume, and then ranks individual merchants within those major categories.
Note that:

• The five major categories are always consistent, but their sales volume may change (i.e., Meats may be ranked second next month, and Dry Goods should replace it at the top of the list.)
• The number of merchants shown within each category may change from month to month (i.e., Meats may have only two of the top 17 merchants next month, and the list needs to dynamically resize.)
• There will always be five major categories reported, and always the top 17 merchants reported, but the output has to resize dynamically. If there is no top 17 performer in a major category, the major category is still reported but no members are reported.

This is the report output:

Data for the merchant rankings is in this table:

And rankings of the major categories are in this table:

I'm using SUMIFS to get the totals by merchant, so I don't need to include those in the formulas. I'm looking for how to identify when a new category starts, which category that is, how many merchants are included, and which merchants those are (ranked).

Because of security policies, I can't do this with VBA. I'd like to avoid pivot tables. All of the rest of the report has been updated with dynamic array functions. Can you find a way to do this entirely with formulas?

Thanks in advance for even taking a look! I think this is a complicated one.

BT

 

[kweaver]

Please modify your profile to reflect the version of Excel you are running. Also, if you use XL2BB it will make it easier for someone to use your data rather than to have to retype it or create fake data.

 

[Tarver]

Updated with XL2BB to post data:

Consolidated Executive Summary - 04-2021 Apr - Working Copy.xlsx
	A	B	C	D
1	Rank	Category/Merchant	April 2021	% Change
2		Meats	$ 293,888,008	21.3%
3	2	Smithfield	$ 156,728,631	27.3%
4	3	Thurgood	$ 105,230,880	22.6%
5	6	Tyson	$ 29,921,605	-5.6%
6	15	ChickenCo	$ 19,125,486	-2.1%
7		Dry Goods	$ 210,373,210	22.0%
8	1	International	$ 181,696,514	40.7%
9	10	Mahatma	$ 15,328,540	-23.2%
10	13	Beanco	$ 4,433,783	-40.5%
11	14	Uncle Ben's
12		Canned Goods	$ 103,948,303	91.1%
13	4	Del Monte	$ 77,204,776	87.6%
14	8	House Brand	$ 23,591,122	135.9%
15	16	Green Giant	$ 2,320,632	22.0%
16		Frozen Foods	$ 52,355,428	-14.6%
17	5	Birdseye	$ 31,542,813	-20.6%
18	9	Marie Callendar's	$ 16,670,488	-4.8%
19	17	Stouffer's	$ 1,054,722	9.1%
20		Convenience	$ 55,768,886	66.0%
21	7	Candy	$ 26,089,797	69.6%
22	11	Personal Care	$ 13,745,991	71.3%
23	12	Periodicals	$ 2,696,732	47.3%
Sheet1

Consolidated Executive Summary - 04-2021 Apr - Working Copy.xlsx
	Q	R	S
1	Rank	Merchant	Category
2	1	International	Dry Goods
3	2	Smithfield	Meats
4	3	Thurgood	Meats
5	4	Del Monte	Canned Goods
6	4	International	Dry Goods
7	5	Birdseye	Frozen Foods
8	6	Tyson	Meats
9	6	Mahatma	Dry Goods
10	7	Candy	Convenience
11	8	House Brand	Canned Goods
12	9	Marie Callendar's	Frozen Foods
13	10	Mahatma	Dry Goods
14	11	Personal Care	Convenience
15	12	Periodicals	Convenience
16	13	BeanCo	Canned Goods
17	14	Uncle Ben's	Convenience
18	15	ChickenCo	Meats
19	16	Green Giant	Frozen Foods
20	17	Stouffer's	Frozen Foods
Sheet1

Consolidated Executive Summary - 04-2021 Apr - Working Copy.xlsx
	Y	Z
1	Category	Ranking
2	Canned Goods	3
3	Convenience	5
4	Dry Goods	2
5	Frozen Foods	4
6	Meats	1
Sheet1

 

[kweaver]

Maybe I'm missing something but why is ChickenCo ranked 15 rather than 9?

 

[Tarver]

Maybe I'm missing something but why is ChickenCo ranked 15 rather than 9?

Human error. That's why this report needs to be automated. It's not my design; I'm trying to take it over, and I've done 95% of the rest of the report, but can't seem to figure out how to get this ordered rank within the ordered rank completed.

Thanks for taking a look and for any help you can offer.

BT