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