We help IT Professionals succeed at work.

Top 5 results using EXCEL pivot tables

model_un
model_un asked
on
Hello All.

Is there a way to use pivot tables or some other mechanisms to "get" the top five results for each year of a multiyear list.

The dataset includes in column A the date and in column B the item. The data set covers 10 years worth of items. I need to know the top five items for each year.

Using pivot tables, I know I can get the top five items for each year, but I would need to change the filter for each year in order to cover the ten years. And then cut and paste the information into another table to "create" the master table showing the ten years of data.

In the end I am looking for something like this:

2002   2003   2004   2005...
1         1         1         1
2         2         2         2
3         3         3         3
4         4         4         4
5         5         5         5

Additionally I will need to know the count of each item (to know the how many times an item appeared in each year)

Attached is a file for ONE of the criterias that I am analysing (countries) that appear in the dataset. I need to do the same for three other criterias.

Suggetions?

Many thanks,

Fernando
top5-countries-2002-2011.xlsx
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
I think you will need formulas rather than pivot tables realistically (Unless you have 2010 in which case you might be able to use PowerPivot to do it).

I won't comment on your choice of chart type... ;)

Author

Commented:
Indeed. Stuck with Excel 2007.

Fernando
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011
Commented:
So you'll basically need LARGE(data,1), LARGE(data, 2) and so on, then use INDEX and MATCH to get the city information. Failing that, it might be easier to use SQL to get the top 5 for each year individually. Does your data come from a database system?

Author

Commented:
Unfortunately the data is in a simple Excel dataset. One sheet with all the information in columns and rows.

Thanks,

FF

Explore More ContentExplore courses, solutions, and other research materials related to this topic.