Solved

Top 5 results using EXCEL pivot tables

Posted on 2012-03-13
6
167 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
  • 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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Checkbox Notification 3 23
Slight Modification to Macro 2 15
Recurring Excel Timelime for Veeam 2 34
TT Status Chang 3 31
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

705 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now