Solved

# Auto sort on Pivots

Posted on 2011-10-11
Medium Priority
227 Views
Hi,

Im using AutoSort on a pivot and am getting the top ten of a datafield, if there are two instances of the 10 largest data point, i.e. if i was counting top ten clients holding cash in an account and number 10 had £200 and someone else had £200, my pivot will then expand to show 11 accounts. Can i make the pivot just show the first instance of this?

Thanks
Seamus
0
Question by:Seamus2626
• 4
• 3

LVL 93

Expert Comment

ID: 36948247
Seamus,

Please explain: do you only want to display one customer for any given balance, or is it that if ties at the bottom of the top 10 would case 11+ customers to be displayed, that you want to only display no more than 10 customers?

Regardless of which it is, how would you pick whom to include and whom to exclude?

Patrick
0

Author Comment

ID: 36948273
Thats my conumdrum Patrick, i was thinking alpahbetical order maybe? Can that be determined through a pivot though?
0

LVL 93

Expert Comment

ID: 36948299
It would be helpful to see a sample file, along with the output expected given that input.
0

Author Comment

ID: 36948350
Im away for half hour, il do that after, thanks, Seamus
0

Author Comment

ID: 36949003
Hey Patrick, i uploaded the file here and you can see my issue

Thanks
Seamus
test.zip
0

LVL 93

Accepted Solution

Patrick Matthews earned 2000 total points
ID: 36949403
There may well be a more elegant way to do this, but this appears to be working:

1) In Sheet1, use the Advanced Filter t generate a list of distinct "A/C no" values in Column AE

2) In AF2, enter this formula to get the averages:

=SUMIF(E:E,AE2,X:X)/COUNTIF(E:E,AE2)

3) In Ag2, use this formula to rank the averages:

=RANK(AF2,\$AF\$2:\$AF\$621)

4) In AH2, use this to adjust the rank in case of ties (using an alpha sort):

=AG2+SUMPRODUCT((\$AG\$2:\$AG\$621=AG2)*(\$AE\$2:\$AE\$621<=AE2))/1000

5) Copy those formulae in Af2:AH2 down as far as needed

=VLOOKUP(E2,AE:AH,4,FALSE)

Copy that formula down as far as needed

a) Change the source range to now include Col AA
c) In your sort options, sort ascending by "average of adj rank"
d) In your value filter, change your Top 10 filter to "Bottom 10 items by average of adj rank"

The attached file shows my results after doing this.

Q-27390408.xls
0

Author Closing Comment

ID: 36949621
Perfect, i can use that, thanks Patrick!!

Cheers,
Seamus
0

## Featured Post

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
###### Suggested Courses
Course of the Month16 days, 5 hours left to enroll