+ Reply to Thread
Results 1 to 5 of 5

Rank items in Pivot table from high to low and low to high based on the filter selected

  1. #1
    Registered User
    Join Date
    08-22-2014
    Location
    San Diego, Ca
    MS-Off Ver
    2010
    Posts
    33

    Rank items in Pivot table from high to low and low to high based on the filter selected

    Good Afternoon,

    I want one chart from my pivot table to show me the ranking and scores. Problem is that not all categories in my filter have the same criteria. Some should be ranked high to low, and some should be ranked low to high.

    Any possible solutions to show on my chart what I want when a filter or slicer is selecting the different categories?

    "high to low" rank (Gem Rec. POP %, Rel Rate, Knowledge, Courtesy, Effort, Apps)
    "low to high" rank (ACW, Hold time)

    Jessica
    Attached Files Attached Files

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Rank items in Pivot table from high to low and low to high based on the filter selecte

    This took a bit of VB code. First I added a helper column to the data table to compute rank based on whether the sort is to be high-to-low or low-to-high.

    =IF([@Rank]="Low to High",SUMPRODUCT(([Category]=[@Category])*([Score]<[@Score])*([Month]=[@Month])*([Score]<>""))+1,SUMPRODUCT(([Category]=[@Category])*([Score]>[@Score])*([Month]=[@Month])*([Score]<>""))+1)

    I’ll dissect one of the SUMPRODUCTS for you.

    SUMPRODUCT(([Category]=[@Category])*([Score]<[@Score])*([Month]=[@Month])*([Score]<>""))+1

    This counts up the number of records where the category is equal to the row categeory, the score is less than the other scores for that category and the score isn’t blank.

    Add 1 since the smallest score is not smaller than itself.

    The other SUMPRODUCT is the same thing except it works in the opposite direction.

    The IF statement switches between the two depending on the value in the rank column.

    I built the dashboard off the table. I could not eliminate the Rank from the pivot chart since it is displayed in the pivot chart so I formatted the data series with no fill and no line.

    I added a new column to the List sheet to link the Category with the direction of sort. This is used by both the program and data entry sheet. Now when you select a category on the Data Entry sheet, the Rank is looked up and you do not have to enter it.

    The code is in two parts:

    Part one fires off the event when the value in cell B24 (Category Dropdown) on the Dashboard 1 page is changed. If you move the position of this cell, you will have to change the code.

    Part two gets called when you change cell B2. It applies the sort to the pivot table on this page.
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    08-22-2014
    Location
    San Diego, Ca
    MS-Off Ver
    2010
    Posts
    33

    Re: Rank items in Pivot table from high to low and low to high based on the filter selecte

    This looks amazing. It does exactly what I want it to do. Here are a couple questions I have for you.

    Data Entry Tab
    Do you think I still need column E "Rank" on the "Data Entry" tab if you correlated the direction of the ranking in the list tab?

    I added a new column to the List sheet to link the Category with the direction of sort. This is used by both the program and data entry sheet. Now when you select a category on the Data Entry sheet, the Rank is looked up and you do not have to enter it.


    Dashboard 1 Tab
    I would like to put all my pivots/charts on "one" dashboard tab to make it look nice, plus add some slicers. I added a buffer column to the "Dashboard 1" tab to incorporate these changes. The VB still worked. Would you suggest I changethe code from B24 to C24?
    The code is in two parts:

    Part one fires off the event when the value in cell B24 (Category Dropdown) on the Dashboard 1 page is changed. If you move the position of this cell, you will have to change the code.

    Part two gets called when you change cell B2. It applies the sort to the pivot table on this page.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.


    Why is my slicer duplicating the month of January?

    YTD Tab (Can't combine 2 pivots into one chart)
    I would also like to show the scores of a Team in a Category YTD. I have accomplishes this, however, to take it one step further it would be nice to see what the team members scores for the year vs the average of scores for all the teams. I believe the only way to do this is to create another column in my data entry tab. with a formula
    Attached Files Attached Files
    Last edited by jholiday78; 02-09-2017 at 09:45 PM.

  4. #4
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,910

    Re: Rank items in Pivot table from high to low and low to high based on the filter selecte

    You can keep the rank. It certainly doesn't hurt anything and it looks nice in the pivot tables. What I do suggest is that you format the plot so there is no fill and no line so it doesn't get charted. With some careful clicking, you should even be able to remove it from the legend. I did this on one of the pivot tables

    You can cut / paste the pivot table and the chart from the Overall page to the dashboard page and they should still work. I did this in the attachment.

    I've played with and have no idea why there is a second January on the slicer. The second January does not seem to be associated with any data. I'd have to pry into this using VB code. I even copied a "good" January and pasted it into all the other Januaries. It didn't work. I do suggest that instead of using month names as strings, that you use actual dates like 1/1/2017, 2/1/2017, etc. You can display them as a month name, but this will let you sort the months in order, and you can show a rolling 12-month period when 2018 comes: you'll know which January you want.
    Attached Files Attached Files
    Last edited by dflak; 02-14-2017 at 10:04 AM. Reason: Add attachment

  5. #5
    Registered User
    Join Date
    08-22-2014
    Location
    San Diego, Ca
    MS-Off Ver
    2010
    Posts
    33

    Re: Rank items in Pivot table from high to low and low to high based on the filter selecte

    This works perfect. Thanks so much for your help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] rank top 5 high
    By eccie in forum Excel General
    Replies: 6
    Last Post: 01-18-2016, 01:42 PM
  2. High Low Close Chart - How to add High & Low labels
    By stephenedwardbennett in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 10-23-2015, 06:45 AM
  3. [SOLVED] Sort data in pivot table high to low
    By ccastell88 in forum Excel General
    Replies: 2
    Last Post: 09-11-2015, 09:26 AM
  4. Replies: 1
    Last Post: 07-16-2015, 05:46 AM
  5. Pivot table not showing the selected items when filter added
    By ShaliniGomes in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 07-23-2014, 01:44 AM
  6. Replies: 9
    Last Post: 08-07-2012, 07:57 AM
  7. Change Pivot table Filter Based on Cell Value *Multiple Filter items* Possible?
    By Flydd in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-27-2012, 06:57 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1