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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.