Another (possibly) difficult 'top 10' question

rob36

Board Regular
Joined
Jul 12, 2010
Messages
57
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
BCDEFGHI
3LOAD
4LOADTRIPZNBINDOORDIVITM
5DTNONONONONONOTICKETS
6-------------------------------------------------------
77/19/201012AC276654014057609499602
87/19/201012ALD76301000657609438161
97/19/201012A0976516005657609106001
107/19/201012A0976516200357609132871
117/19/201012A0978119160657649887551
127/19/201012A0978121000057609132881
137/19/201012A0978122185857642801263
147/19/201012A0978123080057609490891
157/19/201012A0978126185457642321841
Query Info
Excel 2002
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

rallcorn

Well-known Member
Joined
Nov 11, 2008
Messages
1,027
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

Board Regular
Joined
Jul 12, 2010
Messages
57
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

Well-known Member
Joined
Nov 11, 2008
Messages
1,027
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,521
Messages
5,596,645
Members
414,083
Latest member
Mrsash

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top