Make a top 20 list without using a pivot table

[tonywatsonhelp]

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

 

[Alex Blakenburg]

Did you put this aside and go and celebrate New Year ?
Can you provide an XL2BB of sample your data ?

 

[Alex Blakenburg]

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.

 

[Toadstool]

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
	F	G	H	I	J	K	L
1	Country	Title	Client		Order No.	Order Date	Order Value
2	Latvia	The Life and Times of Row 2	Tom Reeder		XN1074	01-Jan-22	$ 1.00
3	Latvia	The Life and Times of Row 3	Tom Reeder		XN1111	01-Jan-22	$ 1.00
4	Latvia	The Life and Times of Row 4	Tom Reeder		XN1148	01-Jan-22	$ 3.00
5	Peru	The Life and Times of Row 5	Bob Bookworm		XN1185	02-Jan-22	$ 4.00
6	Peru	The Life and Times of Row 6	Tom Reeder		XN1222	01-Jan-22	$ 5.00
7	Peru	The Life and Times of Row 7	Tom Reeder		XN1259	01-Jan-22	$ 6.00
8	Peru	The Life and Times of Row 8	Tom Reeder		XN1296	01-Jan-22	$ 7.00
9	Peru	The Life and Times of Row 9	Bob Bookworm		XN1333	02-Jan-22	$ 8.00
10	Peru	The Life and Times of Row 10	Tom Reeder		XN1370	02-Jan-22	$ 9.00
11	Latvia	The Life and Times of Row 11	Tom Reeder		XN1407	02-Jan-22	$ 10.00
12	Latvia	The Life and Times of Row 12	Bob Bookworm		XN1444	01-Jan-22	$ 11.00
13	Latvia	The Life and Times of Row 13	Tom Reeder		XN1481	01-Jan-22	$ 22.00
14	Latvia	The Life and Times of Row 14	Tom Reeder		XN1518	01-Jan-22	$ 22.00
15	Latvia	The Life and Times of Row 15	Bob Bookworm		XN1555	01-Jan-22	$ 14.00
16	Latvia	The Life and Times of Row 16	Tom Reeder		XN1592	01-Jan-22	$ 15.00
17	Latvia	The Life and Times of Row 17	Bob Bookworm		XN1629	01-Jan-22	$ 16.00
18	Latvia	The Life and Times of Row 18	Bob Bookworm		XN1666	01-Jan-22	$ 17.00
Raw Data

Here's the Control sheet with results.

TonyWatsonHelp5.xlsx
	D	E	F	G	H	I	J	K
1	Control		Rank	Order Value	Title	Order No.		Worker
2	Date		1	$ 22.00	The Life and Times of Row 13	XN1481		4
3	01-Jan-22		1	$ 22.00	The Life and Times of Row 14	XN1518		4
4	Country		3	$ 15.00	The Life and Times of Row 16	XN1592		3
5	Latvia		4	$ 3.00	The Life and Times of Row 4	XN1148		3
6	Client							3
7	Tom Reeder							3
8								3
9	Top							3
10	4							3
11								3
12								3
Control

Cell Formulas
Range	Formula
F2:F12	F2	=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:G12	G2	=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:H12	H2	=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:I12	I2	=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:K12	K2	=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)