Ranking Groups and Members

[Tarver]

Hey, Excel experts!

Can you help me create a report similar to the one below? I'm trying to report the top 10 businesses, grouped by their channel.

Book1
	A	B	C
1	Club Channel
2	1	Sam's Club	$ 67,844,223
3	5	Costo	$ 176,719
4	Big Box Channel
5	2	Target	$ 36,221,321
6	3	Walmart	$ 1,230,069
7	Convenience Channel
8	4	Starvin' Marvin	$ 1,020,743
9	7	7-Eleven	$ 92,829
10	9	Little General	$ 39,013
11	10	QuikTrip	$ 24,536
12	Grocery Channel
13	6	Safeway	$ 102,187
14	8	Safeway	$ 102,187
Sheet1

This report shows the top 10 buinesses. Note that the channels (Club, Big Box, etc.) are shown according to their rank (Big Box has highest sales,) and the individual business' ranking is displayed next to their name at left. The Pharmacy Channel doesn't appear on the report because none of its businesses are ranked in the top 10.

I have two tables that provide the data for this report.

The sales data table:

Book1
	A	B	C	D
1	Channel	Merchant	Sales	Rank
2	Big Box	Walmart	$ 1,230,069	3
3	Convenience	Starvin' Marvin	$ 1,020,743	4
4	Big Box	Target	$ 36,221,321	2
5	Convenience	Little General	$ 39,013	9
6	Convenience	7-Eleven	$ 92,829	8
7	Convenience	QuikTrip	$ 24,536	10
8	Club	Sam's Club	$ 67,844,223	1
9	Club	Costco	$ 176,719	5
10	Convenience	Cumberland Farms	$ -	17
11	Club	BJ's Wholesale	$ 12,310	11
12	Pharmacy	Rite Aid	$ 5,240	13
13	Pharmacy	Walgreen's	$ 4,250	15
14	Grocery	Safeway	$ 102,187	6
15	Pharmacy	CVS Pharmacy	$ 4,315	14
16	Grocery	Kroger	$ 7,033	12
17	Grocery	HEB	$ 1,125	16
18	Grocery	Safeway	$ 102,187	6
Sheet2

The channel rank table:

Book1
	A	B	C
1	Channel	Sales	Rank
2	Big Box	$ 37,451,390	2
3	Convenience	$ 1,177,121	3
4	Club	$ 68,033,252	1
5	Pharmacy	$ 13,805	5
6	Grocery	$ 212,532	4
Sheet3

What's the best way to combine the data to produce the output I'm trying to achieve? I've gone down the road of trying to use dynamic array formulas on the sales data table, but I'm not having luck. My ideal solution will NOT use pivot tables or VBA code, though I'm open to those if there's no simpler way.

Thanks for your help!

(And by way of a disclaimer, the data above is not real data; it is for illustrative purposes only.)

 

[alansidman]

If the following presentation works for you then, the solution is with power query and the Mcode follows.

Book1
	F	G	H	I
1	Channel	Merchant	Sales	Rank
2	Club	Sam's Club	67844222.95	1
3	Club	Costco	176719.35	5
4	Big Box	Target	36221320.72	2
5	Big Box	Walmart	1230069.19	3
6	Convenience	Starvin' Marvin	1020743.15	4
7	Convenience	7-Eleven	92828.74	8
8	Convenience	Little General	39012.86	9
9	Convenience	QuikTrip	24535.85	10
10	Grocery	Safeway	102186.81	6
11	Grocery	Safeway	102186.81	6
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Channel", type text}, {"Merchant", type text}, {"Sales", type number}, {"Rank", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Rank", Order.Ascending}}),
    #"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each ([Rank] <> 11 and [Rank] <> 12 and [Rank] <> 13 and [Rank] <> 14 and [Rank] <> 15 and [Rank] <> 16 and [Rank] <> 17)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Channel"}, {{"Data", each _, type table [Channel=nullable text, Merchant=nullable text, Sales=nullable number, Rank=nullable number]}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Merchant", "Sales", "Rank"}, {"Merchant", "Sales", "Rank"})
in
    #"Expanded Data"