?
Solved

Auto sort on Pivots

Posted on 2011-10-11
7
Medium Priority
?
227 Views
Last Modified: 2012-05-12
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
Comment
Question by:Seamus2626
  • 4
  • 3
7 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
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

by:Seamus2626
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

by:Patrick Matthews
ID: 36948299
It would be helpful to see a sample file, along with the output expected given that input.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

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

Author Comment

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

Thanks
Seamus
test.zip
0
 
LVL 93

Accepted Solution

by:
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

6) Adjacent to your original data, in AA1 enter a heading such as "adjusted rank", and in AA2 the formula:

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

Copy that formula down as far as needed

7) Switching to your PivotTable...

a) Change the source range to now include Col AA
b) Add "average of adj rank" as a data field
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

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

Cheers,
Seamus
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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…

850 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