Extract Top 5 value from a pivot table

  • Thread starter Thread starter Andre C
  • Start date Start date
A

Andre C

Hello,

I have a a pivot table listing by country and months of the number of times
a certain internet page has been reviewed.

I would like to have a formula (x5) which would extract the 5 highest pages
viewed for a given month and given country.

I tried the Getpivotdata and sumproduct functions, but I am getting nowhere.

Can you help?

Thank you
 
Hello


Here is example of data:

Dec 09 Jan 10
Feb 10


USA Page 18 56 75
100
Page 24 15 85
60
Page 3 67 18
40
Page 41 23 66
80
Page 52 11 89
30

Canada Page 13 56 75 100
Page 21 89 54
18
Page 32 67 23
78
Page 4 34 95
60
Page 5 67 83
31
Page 7 23 45
21

For the example above, I would like to have a formula where I would select
these

Input cells : USA Dec09


formula 1 & 2: highest number would have result PAGE 3 and 67
formula 3 & 4 second highest would have result PAGE 18 and 56
formula 5 &6 third highest would have resuly Page 41 and 23


If I was to change the input cell to CANADA and Jan 10

I would want to have these results (separate cells)

Page 4 95
Page 5 83
Page 13 75


I do have thousands of rows of data (can vary), and I need the page number
and the value of the say the 3 biggest values for an individual country and
particular month


Hope this makes it more clear

Thank you

Andre
 
Hello Herbert,

Thank you for your file. I learned more about the 2007 pivot table.
However, I need a report that will give me the top 3 pages for a series of
months.

The type of reports i am looking for (based on your example) is

TOP PAGE Dec Jan Feb
(Reference)
Greece 11 14 11
Hungary 26 27 26
Iceland 39 38 40
etc


Next to that, a report for the corresponding value

TOP PAGE Dec Jan Feb etc
(Value)
Greece 444 313 290
Hungary 115 136 110
Iceland 430 230 461
etc


And finally repeat the report for 2nd and 3rd highest page.

I could see using your pivot table, but I imagine having to create as many
pivot tables as I have different months.

Thanks for your help

André
 
Simply amazing!!!

Thank you for taking the time to help, you have gone over and above my
expectations!

André
a French Canadian living in Australia
 
Back
Top