Ordered list within an ordered list

Tarver

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

ss1.JPG


Data for the merchant rankings is in this table:
ss2.JPG


And rankings of the major categories are in this table:
ss3.JPG


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
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

kweaver

Well-known Member
Joined
May 8, 2018
Messages
2,134
Office Version
  1. 365
  2. 2010
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

Board Regular
Joined
Nov 15, 2012
Messages
85
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Updated with XL2BB to post data:


Consolidated Executive Summary - 04-2021 Apr - Working Copy.xlsx
ABCD
1RankCategory/MerchantApril 2021% Change
2Meats$ 293,888,00821.3%
32Smithfield$ 156,728,63127.3%
43Thurgood$ 105,230,88022.6%
56Tyson$ 29,921,605-5.6%
615ChickenCo$ 19,125,486-2.1%
7Dry Goods$ 210,373,21022.0%
81International$ 181,696,51440.7%
910Mahatma$ 15,328,540-23.2%
1013Beanco$ 4,433,783-40.5%
1114Uncle Ben's
12Canned Goods$ 103,948,30391.1%
134Del Monte$ 77,204,77687.6%
148House Brand$ 23,591,122135.9%
1516Green Giant$ 2,320,63222.0%
16Frozen Foods$ 52,355,428-14.6%
175Birdseye$ 31,542,813-20.6%
189Marie Callendar's$ 16,670,488-4.8%
1917Stouffer's$ 1,054,7229.1%
20Convenience$ 55,768,88666.0%
217Candy$ 26,089,79769.6%
2211Personal Care$ 13,745,99171.3%
2312Periodicals$ 2,696,73247.3%
Sheet1



Consolidated Executive Summary - 04-2021 Apr - Working Copy.xlsx
QRS
1RankMerchantCategory
21InternationalDry Goods
32SmithfieldMeats
43ThurgoodMeats
54Del MonteCanned Goods
64InternationalDry Goods
75BirdseyeFrozen Foods
86TysonMeats
96MahatmaDry Goods
107CandyConvenience
118House BrandCanned Goods
129Marie Callendar'sFrozen Foods
1310MahatmaDry Goods
1411Personal CareConvenience
1512PeriodicalsConvenience
1613BeanCoCanned Goods
1714Uncle Ben'sConvenience
1815ChickenCoMeats
1916Green GiantFrozen Foods
2017Stouffer'sFrozen Foods
Sheet1



Consolidated Executive Summary - 04-2021 Apr - Working Copy.xlsx
YZ
1CategoryRanking
2Canned Goods3
3Convenience5
4Dry Goods2
5Frozen Foods4
6Meats1
Sheet1
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
2,134
Office Version
  1. 365
  2. 2010
Maybe I'm missing something but why is ChickenCo ranked 15 rather than 9?
 

Tarver

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

Forum statistics

Threads
1,147,748
Messages
5,742,976
Members
423,769
Latest member
LongToast

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