Another (possibly) difficult 'top 10' question

[rob36]

Hey again, I have another question regarding the creation of a top 10 list. I want to create a list which will sum together the tickets if the bin number is the same, and then create a top 10 list of bins with the most tickets in them, displaying both the total ticket amount as well as the bin number.

I would also like to do this for the item number (the same item number I would like to sum the tickets together) but this would be a different list, and im sure the code would be similar to the above stated question.

Thanks!

here is some sample data

Excel Workbook
	B	C	D	E	F	G	H	I
3					LOAD
4	LOAD	TRIP	ZN	BIN	DOOR	DIV	ITM
5	DT	NO	NO	NO	NO	NO	NO	TICKETS
6	----------	----	---	------------	----	----	-------	-----------
7	7/19/2010	12	AC2	766540140	57	609	49960	2
8	7/19/2010	12	ALD	763010006	57	609	43816	1
9	7/19/2010	12	A09	765160056	57	609	10600	1
10	7/19/2010	12	A09	765162003	57	609	13287	1
11	7/19/2010	12	A09	781191606	57	649	88755	1
12	7/19/2010	12	A09	781210000	57	609	13288	1
13	7/19/2010	12	A09	781221858	57	642	80126	3
14	7/19/2010	12	A09	781230800	57	609	49089	1
15	7/19/2010	12	A09	781261854	57	642	32184	1
Query Info

Excel 2002

 

[rallcorn]

I'm an absolute pivot table addict - so I'd approach it that way. You can very easily set it to pick top 10 based on selected field & it will do so everytime you refresh.

 

[rob36]

I was hoping to not take the pivot table approach because thats an extra tab and im already using a lot of space for the tool, trying to make it as compact as possible. Is there a sumif type statement that could possbily be used?

If i were to do a pivot, i get an error whenever i try and select the entire worksheet

 

[rallcorn]

Can't select entire sheet unless every column in sheet has a heading in row 1. For pivot the data source columns selected must have a column heading, I sometime "cheat" when I know I'll be adding additional columns of data. I'll label the first blank column to right of my current with X & select all columns thru column labeled X. Then as I add columns I insert before the labeled X - that way the new column will automatically be included in pivot data range when I refresh & I don't have to manually change the range.