Extract Top 5 value from a pivot table

C@discussions.microsoft.com Andre C

unread,

Apr 22, 2010, 7:47:01 PM4/22/10

to

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

Jarek Kujawa

unread,

Apr 23, 2010, 4:55:39 AM4/23/10

to

Could you provide a sample of your data?

On 23 Kwi, 01:47, Andre C <Andre C...@discussions.microsoft.com>
wrote:

Andre C

unread,

Apr 23, 2010, 7:10:01 AM4/23/10

to

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

Herbert Seidenberg

unread,

Apr 23, 2010, 9:44:05 PM4/23/10

to

Excel 2007 PivotTable
Top 3
No code, no formulas.
http://c0718892.cdn.cloudfiles.rackspacecloud.com/04_23_10.xlsx
Pdf preview:
http://www.mediafire.com/file/zz2zj3zyznj/04_23_10.pdf

Andre C

unread,

Apr 24, 2010, 12:55:01 AM4/24/10

to

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é

"Herbert Seidenberg" wrote:

> .
>

Herbert Seidenberg

unread,

Apr 24, 2010, 12:04:19 PM4/24/10

to

Excel 2007 PivotTable
Top One
No code, no formulas.
Other configurations w/o PTs, but monster formulas, on request.
http://c0718892.cdn.cloudfiles.rackspacecloud.com/04_23_10.xlsx
Pdf preview:
http://www.mediafire.com/file/xukndqm3nho/04_23_10.pdf

Andre C

unread,

Apr 24, 2010, 6:18:01 PM4/24/10

to

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

"Herbert Seidenberg" wrote:

> .
>