Create a top 10 list based on multiple criterias sorted by summed values

[testytest]

Hi All ! I have a very large data set that I want to create a top 10 list of. For example in the data below, I want to sort by say highest Quantity sorted. I know I can do this in a pivot table but I'd like to refrain from doing that if i can just write out a formula so i don't need to keep refreshing the pivot table

so for example, I would like the output to be:
top ten product Sold (product Name) and then the sum of the Quantity of that said product. this is the output i would like to see below, and below that is the example table TIA!!!!

	Name	Dollars	Quantity
1​	A	21​	5491​
2​	G	124​	950​
3​	B	35​	589​

Prodcut Name	Date	Dollars	Quantity
A	September	5​	15​
B	October	10​	1​
C	January	12​	5​
D	September	1​	11​
E	October	15​	115​
F	January		151​
G	September	1​	515​
H	October	1​	15​
I	January	123​	435​
J	September	1​	134​
K	October	5​	52​
K	January	15​	45​
A	September	1​	5422​
K	October	16​	544​
A	January	15​	54​
B	September	10​	54​
B	October	15​	534​
C	January	16​	5​
D	February	16​	45​

 

[Peter_SSs]

Welcome to the MrExcel board!

Looks like your results are not drawn just from the sample data shown. With your Excel version, I would use some helper columns (G:I for me) which could be hidden if you want.

23 10 13.xlsm
	A	B	C	D	E	F	G	H	I
1		Name	Dollars	Quantity
2	1	A	21	5491
3	2	K	36	641
4	3	B	35	589
5	4	G	1	515
6
7
8
9
10	Prodcut Name	Date	Dollars	Quantity
11	A	September	5	15			A	21	5491
12	B	October	10	1			B	35	589
13	C	January	12	5			C	28	10
14	D	September	1	11			D	17	56
15	E	October	15	115			E	15	115
16	F	January		151			F	0	151
17	G	September	1	515			G	1	515
18	H	October	1	15			H	1	15
19	I	January	123	435			I	123	435
20	J	September	1	134			J	1	134
21	K	October	5	52			K	36	641
22	K	January	15	45
23	A	September	1	5422
24	K	October	16	544
25	A	January	15	54
26	B	September	10	54
27	B	October	15	534
28	C	January	16	5
29	D	February	16	45
Top n

Cell Formulas
Range	Formula
B2:C5	B2	=INDEX(G:G,AGGREGATE(15,6,ROW($I$11:$I$100)/($I$11:$I$100=$D2),COUNTIF($D$2:$D2,$D2)))
D2:D5	D2	=AGGREGATE(14,6,I$11:I$100,A2)
G11:G29	G11	=IFERROR(INDEX($A$11:$A$100,MATCH(0,INDEX(COUNTIF($G$10:G10,$A$11:$A$100)+(A$11:A$100=""),0),0)),"")
H11:I29	H11	=IF($G11="","",SUMIF($A$11:$A$100,$G11,C$11:C$100))

 

[alansidman]

Alternative solution with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Prodcut Name"}, {{"Qty", each List.Sum([Quantity]), type number}, {"Total $", each List.Sum([Dollars]), type nullable number}})
in
    #"Grouped Rows"

 

[testytest]

Hi thanks for your replies, @Peter_SSs do you have to create a helper table for this? there's over 600K rows of data and may not seem feasible.

 

[Peter_SSs]

Hi thanks for your replies, @Peter_SSs do you have to create a helper table for this? there's over 600K rows of data and may not seem feasible.

Especially with your version of Excel I don't see a feasible way without helpers.

• Have you tried Alan's Power Query option?
• Otherwise, your Pivot Table option may be the way to go.

 

[alansidman]

Have you tried the Power Query solution I provided? Comments?

 

[BigBeachBananas]

@Peter_SSs

I'm looking for something similar but for D365. Is it possible without any helper columns?

 

[Peter_SSs]

@Peter_SSs

I'm looking for something similar but for D365. Is it possible without any helper columns?

Please start your own thread, rather than hijack this current one. (You can reference this thread if required)