Dynamic Top 10 and Bottom 10 List with unconventional dataset

[SBAlexanian]

I have a bit of a dilemma... Once again, upper management mistook my degree of mechanical engineering for software engineering and has assigned me a task a bit beyond the scope of my abilities. I've struggled through and figured out most of it, but I have hit a dead end on this last bit of functionality.

I am developing a workbook that captures all the data of used cars sold from a local dealership. I need to create a top 10 and bottom 10 list of the vehicles sold (I.E. They sell more used Chevy Malibus than Ford Rangers)

The raw data does not support conventional top 10 and bottom 10 list methods in excel. Here is a sample of the workbook data formatting:

Data Sold	Stock #	Vehicle Desc.
03/01/2014	1234	2008 Buick LaCrosse CXL
03/01/2014	2345	2007 Saturn Outlook
03/01/2014	3456	2008 Mazda Mazda3 i
03/01/2014	4567	1997 Ford Mustang GT

<tbody>
</tbody>

The table has much more data, and over 1000 vehicles.... But that's a sample of the data being looked at to illustrate the challenge. I need a the top 10 and bottom 10 to reflect the 10 best and worst sellers (based on how many of a particular model we have sold). This cannot be year dependent! So if they sold a 2009, 2010, and 2011 Mustang, I need the top 10 and bottom 10 lists to strictly see that they sold more mustangs than any other vehicle.

I don't know if I am verbalizing this the right way to make sense to everyone else...

The problem I run into here is that I need the lists to be dynamic, and on a different sheet within the workbook. I can't think of a good way to do this... VBA or not, any help would be appreciated.

 

[General Ledger]

SBAlexanian,

Here is one way:
1. Create a field/column of the data on which you want to summarize. If column C is a Vehicle Desc., you could use Text To Columns feature to break out model to column D.
2. Create a PivotTable based on your data. It has filters that you can set to list the Top or Bottom number of items.

Bonus. With a PivotTable and your data that has Date Sold, you can slice, dice, and summarize your data based on months and/or year of sale. Also, if in your data you have a column of the vehicle year (use Text To Columns feature to create), you can summarize on this field as well.

Best of luck,

G/L

 

[SBAlexanian]

Thanks G/L, I ended up getting it done with the pivot tables as you suggested!