Solved

Top 5 results using EXCEL pivot tables

Posted on 2012-03-13
6
173 Views
Last Modified: 2013-07-22
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
0
Comment
Question by:model_un
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
6 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37715627
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
 

Author Comment

by:model_un
ID: 37716197
Indeed. Stuck with Excel 2007.

Fernando
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 37716794
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
 

Author Comment

by:model_un
ID: 37716980
Unfortunately the data is in a simple Excel dataset. One sheet with all the information in columns and rows.

Thanks,

FF
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question