Top 5 results using EXCEL pivot tables

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
model_unAsked:
Who is Participating?
 
Rory ArchibaldCommented:
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?
0
 
Rory ArchibaldCommented:
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... ;)
0
 
model_unAuthor Commented:
Indeed. Stuck with Excel 2007.

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

Thanks,

FF
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.