# Make a top 20 list without using a pivot table

#### tonywatsonhelp

##### Well-known Member
Hi Everyone,

I'm looking for a formula solution to the following problem,

My sheet is called "Raw Data"
I need to give me the top 20 orders and show the Title and number
but it needs to be for a client, a country and a date

So Date is in "contriol" D3, Country D5, Client D7
so what i need is formula or formulas to

Give me a top 20 list for that client only

In Need the Order Value from Column L
The Title form Column G
The Number Column J

please help if you can, cant go out a celebrate new year until ive sent this report.

P.S. Please don't suggest a pivot Table, for reasons i can not explain it must be formula based,

Thanks

Tony

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Did you put this aside and go and celebrate New Year ?
Can you provide an XL2BB of sample your data ?

You can find quite a good starting point in this Excel is Fun video but you need some additional modifications for it to work for you.

Hi TonyWatsonHelp,

Here's an approach using a Worker column which must be copied down the maximum number of Raw Data rows. The formulae also only handle up to 9,999 rows.

I've added a Top n cell to limit test data but you can change n to 20.

Here's my Raw Data
TonyWatsonHelp5.xlsx
FGHIJKL
1CountryTitleClientOrder No.Order DateOrder Value
2LatviaThe Life and Times of Row 2Tom ReederXN107401-Jan-22\$ 1.00
3LatviaThe Life and Times of Row 3Tom ReederXN111101-Jan-22\$ 1.00
4LatviaThe Life and Times of Row 4Tom ReederXN114801-Jan-22\$ 3.00
5PeruThe Life and Times of Row 5Bob BookwormXN118502-Jan-22\$ 4.00
6PeruThe Life and Times of Row 6Tom ReederXN122201-Jan-22\$ 5.00
7PeruThe Life and Times of Row 7Tom ReederXN125901-Jan-22\$ 6.00
8PeruThe Life and Times of Row 8Tom ReederXN129601-Jan-22\$ 7.00
9PeruThe Life and Times of Row 9Bob BookwormXN133302-Jan-22\$ 8.00
10PeruThe Life and Times of Row 10Tom ReederXN137002-Jan-22\$ 9.00
11LatviaThe Life and Times of Row 11Tom ReederXN140702-Jan-22\$ 10.00
12LatviaThe Life and Times of Row 12Bob BookwormXN144401-Jan-22\$ 11.00
13LatviaThe Life and Times of Row 13Tom ReederXN148101-Jan-22\$ 22.00
14LatviaThe Life and Times of Row 14Tom ReederXN151801-Jan-22\$ 22.00
15LatviaThe Life and Times of Row 15Bob BookwormXN155501-Jan-22\$ 14.00
16LatviaThe Life and Times of Row 16Tom ReederXN159201-Jan-22\$ 15.00
17LatviaThe Life and Times of Row 17Bob BookwormXN162901-Jan-22\$ 16.00
18LatviaThe Life and Times of Row 18Bob BookwormXN166601-Jan-22\$ 17.00
Raw Data

Here's the Control sheet with results.

Cell Formulas
RangeFormula
F2:F12F2=IFERROR(IF(ROW()-ROW(\$G\$1)>\$D\$10,"",IFERROR(AGGREGATE(15,6,\$K\$2:\$K\$16/(('Raw Data'!\$H\$2:\$H\$9999=\$D\$7)*('Raw Data'!\$F\$2:\$F\$9999=\$D\$5)*('Raw Data'!\$K\$2:\$K\$9999=\$D\$3)),ROW()-ROW(\$G\$1)),"")+1),"")
G2:G12G2=IF(\$F2="","",INDEX('Raw Data'!\$L\$2:\$L\$9999,AGGREGATE(15,6,ROW(\$K\$2:\$K\$16)-ROW(\$K\$1)/(('Raw Data'!\$H\$2:\$H\$9999=\$D\$7)*('Raw Data'!\$F\$2:\$F\$9999=\$D\$5)*('Raw Data'!\$K\$2:\$K\$9999=\$D\$3)*(\$K\$2:\$K\$16=F2-1)),COUNTIF(\$F\$1:\$F1,F2)+1)))
H2:H12H2=IF(\$F2="","",INDEX('Raw Data'!\$G\$2:\$G\$9999,AGGREGATE(15,6,ROW(\$K\$2:\$K\$16)-ROW(\$K\$1)/(('Raw Data'!\$H\$2:\$H\$9999=\$D\$7)*('Raw Data'!\$F\$2:\$F\$9999=\$D\$5)*('Raw Data'!\$K\$2:\$K\$9999=\$D\$3)*(\$K\$2:\$K\$16=F2-1)),COUNTIF(\$F\$1:\$F1,F2)+1)))
I2:I12I2=IF(\$F2="","",INDEX('Raw Data'!\$J\$2:\$J\$9999,AGGREGATE(15,6,ROW(\$K\$2:\$K\$16)-ROW(\$K\$1)/(('Raw Data'!\$H\$2:\$H\$9999=\$D\$7)*('Raw Data'!\$F\$2:\$F\$9999=\$D\$5)*('Raw Data'!\$K\$2:\$K\$9999=\$D\$3)*(\$K\$2:\$K\$16=F2-1)),COUNTIF(\$F\$1:\$F1,F2)+1)))
K2:K12K2=COUNTIFS('Raw Data'!\$H\$2:\$H\$9999,\$D\$7,'Raw Data'!\$F\$2:\$F\$9999,\$D\$5,'Raw Data'!\$K\$2:\$K\$9999,\$D\$3,'Raw Data'!\$L\$2:\$L\$9999,">"&'Raw Data'!\$L2)

Replies
0
Views
147
Replies
2
Views
483
Replies
1
Views
300
Replies
0
Views
102
Replies
8
Views
179

1,202,990
Messages
6,052,961
Members
444,621
Latest member
MIKOLAJ_R

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

### Which adblocker are you using?

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

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