Return Top 20 On Certain Criteria

[Lewzerrrr]

Hi,

I have a data dump in 1 sheet with 20+ departments that I need to pull the top 20 bestsellers for each.

I can do an array formula using INDEX SMALL IF ROW-ROW ROWS however that only pulls if the data is sorted so do I need to incorporate LARGE into this somehow?

Example based on top 3.

[TABLE="width: 512"]
<colgroup><col span="8"></colgroup><tbody>[TR]
[TD][/TD]
[TD="colspan: 2"]Finished Result[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Data Dump[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Dept1[/TD]
[TD]Line5[/TD]
[TD="align: right"]6121[/TD]
[TD][/TD]
[TD]Dept1[/TD]
[TD]Line1[/TD]
[TD="align: right"]5435[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Dept1[/TD]
[TD]Line4[/TD]
[TD="align: right"]5830[/TD]
[TD][/TD]
[TD]Dept1[/TD]
[TD]Line2[/TD]
[TD="align: right"]4675[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Dept1[/TD]
[TD]Line1[/TD]
[TD="align: right"]5435[/TD]
[TD][/TD]
[TD]Dept1[/TD]
[TD]Line3[/TD]
[TD="align: right"]4794[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Dept2[/TD]
[TD]Line8[/TD]
[TD="align: right"]9372[/TD]
[TD][/TD]
[TD]Dept1[/TD]
[TD]Line4[/TD]
[TD="align: right"]5830[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]Dept2[/TD]
[TD]Line6[/TD]
[TD="align: right"]7102[/TD]
[TD][/TD]
[TD]Dept1[/TD]
[TD]Line5[/TD]
[TD="align: right"]6121[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]Dept2[/TD]
[TD]Line10[/TD]
[TD="align: right"]5270[/TD]
[TD][/TD]
[TD]Dept2[/TD]
[TD]Line6[/TD]
[TD="align: right"]7102[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]Dept3[/TD]
[TD]Line14[/TD]
[TD="align: right"]7219[/TD]
[TD][/TD]
[TD]Dept2[/TD]
[TD]Line7[/TD]
[TD="align: right"]2965[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]Dept3[/TD]
[TD]Line12[/TD]
[TD="align: right"]5000[/TD]
[TD][/TD]
[TD]Dept2[/TD]
[TD]Line8[/TD]
[TD="align: right"]9372[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]Dept3[/TD]
[TD]Line13[/TD]
[TD="align: right"]3247[/TD]
[TD][/TD]
[TD]Dept2[/TD]
[TD]Line9[/TD]
[TD="align: right"]4447[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Dept2[/TD]
[TD]Line10[/TD]
[TD="align: right"]5270[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Dept3[/TD]
[TD]Line11[/TD]
[TD="align: right"]1181[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Dept3[/TD]
[TD]Line12[/TD]
[TD="align: right"]5000[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Dept3[/TD]
[TD]Line13[/TD]
[TD="align: right"]3247[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Dept3[/TD]
[TD]Line14[/TD]
[TD="align: right"]7219[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Dept3[/TD]
[TD]Line15[/TD]
[TD="align: right"]1745[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fennek]

Hallo,

try Autofilter

regards

 

[Aladin Akyurek]

Try to run a pivot table with the Top N option...

 

[63falcondude]

Here's one option using formulas:

Excel 2010
	A	B	C	D	E	F	G
1	Dept1	Line5	6121		Dept1	Line1	5435
2	Dept1	Line4	5830		Dept1	Line2	4675
3	Dept1	Line1	5435		Dept1	Line3	4794
4	Dept2	Line8	9372		Dept1	Line4	5830
5	Dept2	Line6	7102		Dept1	Line5	6121
6	Dept2	Line10	5270		Dept2	Line6	7102
7	Dept3	Line14	7219		Dept2	Line7	2965
8	Dept3	Line12	5000		Dept2	Line8	9372
9	Dept3	Line13	3247		Dept2	Line9	4447
10					Dept2	Line10	5270
11					Dept3	Line11	1181
12					Dept3	Line12	5000
13					Dept3	Line13	3247
14					Dept3	Line14	7219
15					Dept3	Line15	1745
Sheet1

Cell Formulas
Range	Formula
B1	{=INDEX(F$1:F$15,MATCH(A1&C1,E$1:E$15&G$1:G$15,0))}
C1	{=LARGE(IF(E$1:E$15=A1,G$1:G$15),COUNTIF(A$1:A1,A1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Eric W]

Another formula version:

	A	B	C	D	E	F	G	H	I
Department	Line	Amount	Department	Line	Amount	# of lines per department
Dept3	Line14	Dept3	Line13
Dept3	Line12	Dept1	Line2
Dept3	Line13	Dept2	Line8
Dept1	Line5	Dept3	Line12
Dept1	Line4	Dept3	Line14
Dept1	Line1	Dept2	Line9
Dept2	Line8	Dept1	Line3
Dept2	Line6	Dept2	Line10
Dept2	Line10	Dept2	Line7
		Dept1	Line4
		Dept2	Line6
		Dept1	Line1
		Dept1	Line5
		Dept3	Line15
	Dept3	Line11

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]7219[/TD]
[TD="align: right"][/TD]

[TD="align: right"]3247[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]5000[/TD]
[TD="align: right"][/TD]

[TD="align: right"]4675[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]3247[/TD]
[TD="align: right"][/TD]

[TD="align: right"]9372[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]6121[/TD]
[TD="align: right"][/TD]

[TD="align: right"]5000[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]5830[/TD]
[TD="align: right"][/TD]

[TD="align: right"]7219[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]5435[/TD]
[TD="align: right"][/TD]

[TD="align: right"]4447[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]9372[/TD]
[TD="align: right"][/TD]

[TD="align: right"]4794[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]7102[/TD]
[TD="align: right"][/TD]

[TD="align: right"]5270[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]5270[/TD]
[TD="align: right"][/TD]

[TD="align: right"]2965[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]5830[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]7102[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]5435[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]6121[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1745[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]1181[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A2[/TH]
[TD="align: left"]{=IFERROR(IF(MOD(ROW($A2)-ROW($A$2),$I$2),A1,INDEX(E:E,SMALL(IF(COUNTIF($A$1:$A1,$E$2:$E$16)=0,ROW($E$2:$E$16)),1))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]{=IF(A2="","",INDEX(F:F,SMALL(IF(($E$2:$E$16=A2)*($G$2:$G$16=C2),ROW($F$2:$F$16)),COUNTIFS($A$2:$A2,A2,$C$2:$C2,C2))))}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]{=IF(A2="","",LARGE(IF($E$2:$E$16=A2,$G$2:$G$16),MOD(ROW($C2)-ROW($C$2),$I$2)+1))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



I randomly sorted the values in E:G for testing. The formula in A2 finds the unique names from column E, and shows n copies of each, based on the value in I3. You can of course create the list yourself. The formula in B is designed to handle ties as well.