TOP 25 by Branch

[Mubz12]

Hi All,

Am hoping for some assistance. Am sure this is a simple question - but cannot seem to get it to work - and have been researching for some time now.

I have a Table (SalesData). I am trying to write a query that will return the top 25 sale lines by branch.

There are three columns within this table - Branch, Line Description and Sales.

How can I return the top 25 selling Lines for each branch?

Any advice would be appreciated.

Kind Regards

 

[Aladin Akyurek]

1) Try to run a pivot table.

2) See for an effective set up: http://www.mrexcel.com/forum/excel-questions/342541-top-5-numbers.html#post1690421

 

[AlanY]

something like this?

Excel 2012
	A	B	C	D	E	F	G	H
1	Branch1	Branch2	Branch3		top25	Branch1	Branch2	Branch3
2	102	51	105		1	196	191	197
3	58	74	125		2	195	180	190
4	157	86	108		3	194	178	187
5	50	68	69		4	191	176	186
6	77	150	79		5	191	174	181
7	68	115	100		6	189	164	171
8	165	50	148		7	184	158	170
9	191	107	69		8	183	153	161
10	196	101	77		9	180	150	155
11	82	117	181		10	177	143	153
12	75	158	155		11	173	143	150
13	180	57	153		12	172	138	148
14	72	106	95		13	165	137	145
15	130	87	65		14	164	123	135
16	130	178	83		15	157	117	134
17	122	174	126		16	154	115	130
18	51	164	190		17	151	114	126
19	117	180	108		18	151	113	125
20	82	114	134		19	142	107	125
21	173	74	135		20	135	106	108
22	172	81	97		21	130	101	108
23	135	143	90		22	130	94	107
24	106	143	170		23	130	88	105
25	127	153	145		24	127	87	100
26	184	176	187		25	122	87	99
27	191	73	58
28	195	75	125
29	151	137	98
30	183	123	84
31	154	138	70
32	151	53	197
33	177	87	130
34	115	113	68
35	130	191	99
36	189	94	186
37	164	60	161
38	142	86	150
39	111	55	107
40	194	88	171
Sheet6

Cell Formulas
Range	Formula
F2	=LARGE(A:A,$E2)

 

[Mubz12]

Thanks for the swift response.

I must say I have never attempted to use a Pivot Table - within Access - I will give this a try and see what I can pull out.

I will review the set up on - the link you attached - and see what I can do!

Thanks

 

[Mubz12]

Hi All,

This is not quite what I am looking for - my fault - I have not explained myself clearly enough!

I am attempting to extract the following from Access:

Column 1 Column 2 Column 3

Branch Line Description Sales (sorted largest to smallest)
xxxx
xxxx
xxxx
25 instances
yyyy
yyyy
yyyy
25 instances

and so on


So that way I have 25 rows per branch sorted by the largest amount of sales per line description for that branch.

Kind Regards

 

[Joe4]

I don't think the first two respondents realized that this question was posted in the Access forum. There is a "Zero Reply Posts" listing that people use to look for unanswered questions, and it shows questions from all Forums. It lists which forum the question is in all the way over to the right, but people sometimes miss that.

Take a look here, as this seems to do address what you are attempting to do: How to get Top 25 rows by each group | Windows Secrets Lounge