Pivot Table Selecting ONLY the top 5 (not built in...)

[MTBChik]

Hi there,

I've been searching all over and can't find exactly what I'm looking for.

I have the following data:
Team Rank Points
CAN 5 11
SLO 2 20
GBR 6 10
KOR 1 25
CAN 3 15
CAN 3 15
CAN 7 9
CAN 8 8
USA 9 7
CAN 10 6
USA 11 5
JPN 12 4
KOR 13 3
USA 14 2
USA 15 1
USA 16 11
AUS 17 11
USA 18 11
USA 19 11
USA 20 11
AUS 21 11
USA 22 11
AUS 23 11
USA 24 11
USA 25 11
JPN 26 11
USA 27 11
USA 28 11
USA 29 11
AUS 30 11
USA 31 11
USA 32 11
USA 33 11
PHI 34 11
USA 35 11


I need to find the top 5 points for each team and add them together then rank that.

I have a pivot table going, but it adds all the points and not just the top 5 points.

Here's the pivot table at the moment:
Team Sum of Points
AUS 44
CAN 64
KOR 28
SLO 20
USA 169

But this is adding all of the points in the list.

Thank you in advance for your help.



__~o
`\<,
(_)/(_)
MTBChick
The day you stop riding is the day you die.

 

[BlueHorizon]

Hi,
If you use an AutoFilter instead of a PivotTable, you can easily find the Top 5 using the drop-down arrow on the points column, and then you can use the following to copy just the visible cells and do your ranking.

1. Select the cells that you want to copy.
2. Click on the Edit menu.
3. Select Go To.
4. In the Go To dialog box, click Special.
5. Under Select, click Visible cells only.
6. Click OK.
7. Back in the document, use your favorite Copy method.
8. Click in the first cell of the paste area.
9. Use your favorite Paste method.

HTH,


Best,
Blue Horizon

 

[GlennUK]

Hi BlueHorizon,

I believe the OP is talking about the top 5 within each Team category. They'd have to do this for each team then, surely?

In the meantime I have a question for MTBchick ... the points and ranking in the data do not look correct. Anything ranked lower than 16 all have 11 points, which doesn't make sense. Is this real data? If so, can you explain how the ranking is achieved?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[Loomah]

ok, this has been annoying the hell out of me!

formulas/calculated fields in pivot tables can't include arrays which scuppered my first plan so it came down to ignoring the pivot option and aiming for a formula based solution (desperately trying to avoid the vba route too)

the following is based on the data example provided pasted into a1 on a sheet
headers are included
the named ranges refer to the headers but don't include them
a list if unique teams is copied to another section of the worksheet and the formula entered next to them
the data MUST be sorted by team order

i offer this as a 'starting' point!

=SUM(LARGE(INDIRECT("C"&MATCH(G1,team,0)+1&":C"&MATCH(G1,team,0)+COUNTIF(team,G1)),ROW(INDIRECT("1:"&MIN(5,COUNTIF(team,G1))))))

this is entered as an array formula using ctrl+shift+enter

column C is where the points column is
G1 is where the first of the unique team names is held

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​