Pivot Table: Ranking Items of a Field

  • Thread starter Thread starter Tim
  • Start date Start date
T

Tim

Example: Lets say that I wanted to rank the performance
of a few salesmen that sell several products based on the
revenue that they generate. The pivot table is set up
with the products as the row field, the salesmen as column
field, and the data is the revenue that each salesman
generate. Is it possible for me to rank the performances
of each salesman based on the revenue that they generate
within the pivot table without changing the data?

Thanks for assistance,
Tim
 
You can select a cell in the Grand Total row, and click the Sort
Descending (Z-A) button on the standard toolbar.
 
I'm aware that I can sort the data, but my actual situation is much more complex than the example I gave. I'm looking for a formula or some other trick that will show a saleman's actual rank with respect to each individual product. For example, if Mark generated the most revenue selling tickets, I want the pivot that to show the number "1". I thought about using the calculate field function, but couldn't get it to work

Regards
Ti
----- Debra Dalgleish wrote: ----

You can select a cell in the Grand Total row, and click the Sort
Descending (Z-A) button on the standard toolbar

Tim wrote
 
AFAIK, you'd have use the RANK function to do that calculation outside
of the PivotTable.
 
I have also tried to solve my problem using the RANK function on the data, but I was unsuccessful. If there was only one product, I wouldn't have this problem. Unfortunately, there are many products involved. I can't think of a way to define the cell range for the "ref" section of the formula: RANK(number,ref,order). For example, lets say there are three salesmen (A, B, and C) and four products (1, 2, 3, and 4). What do I put as the cell range for the "ref" section if I wanted to rank the sales performance of A, B, and C for product 1 only, product 2 only, etc., formula-wise? There are too many products and salesmen involved to do it manually

Regards
Ti

----- Debra Dalgleish wrote: ----

AFAIK, you'd have use the RANK function to do that calculation outside
of the PivotTable

Tim wrote
 
You could create a new sheet for the rankings, and update them
programmatically when you refresh the pivot table. For example:

'======================================
Sub RefreshAndRank()
Dim wsP As Worksheet
Dim wsR As Worksheet
Dim wsD As Worksheet
Dim r As Integer
Dim c As Integer
Set wsP = Worksheets("Pivot")
Set wsR = Worksheets("Rankings")
Set wsD = Worksheets("Data")

wsP.PivotTables(1).PivotCache.Refresh

'clear old rankings and create headings
wsR.Cells.Clear
wsD.Columns("C:C").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=wsR.Range("A1"), Unique:=True
wsR.Range("A1").Clear
r = wsR.Cells(Rows.Count, 1).End(xlUp).Row
wsR.Range("A2:A" & r).Sort Key1:=wsR.Range("A2"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
wsD.Columns("D:D").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=wsR.Range("C1"), Unique:=True
c = wsR.Cells(Rows.Count, 3).End(xlUp).Row
wsR.Range("C1").Clear
wsR.Range("C1:C" & c).Sort Key1:=wsR.Range("C1"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
wsR.Range("C1:C" & c).Copy
wsR.Range("D1").PasteSpecial _
Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
wsR.Columns("B:C").Delete Shift:=xlToLeft

'enter Rank formula and Autofill
wsR.Range("B2").FormulaR1C1 = _

"=RANK(GETPIVOTDATA(""Total"",Pivot!R3C1,""Rep"",RC1,""Item"",R1C),Pivot!R5C:R15C)"
wsR.Range("B2").AutoFill Destination:=wsR.Range("B2:B" & r), _
Type:=xlFillDefault
wsR.Range("B2:B" & r).AutoFill _
Destination:=wsR.Range(wsR.Cells(2, 2), wsR.Cells(r, c)), _
Type:=xlFillDefault
'clear cells with errors
wsR.Cells.SpecialCells(xlCellTypeFormulas, 16).ClearContents
End Sub
'============================================
 
What version of Excel are you using?
Did you try adding a new sheet, and using the macro?
Where did you get stuck?
 
I use Excel 2000. I did add a new sheet and named it "Rankings." I copied and paste the subroutine you posted into the MS Visual Basic Editor, changed some of the references (i.e. wsD.Columns("D:D").AdvancedFilter _ to wsD.Columns("A:A").AdvancedFilter _) and tried to run the subroutine, but error messages kept popping up. In the Excel file, "Rankings" worksheet, I can see that the subroutine set up the column and row headings, but no data. Trying to fix it seemed like a lost cause since i don't understand anything after "'enter Rank formula and Autofill"

Question: Will the subroutine add the ranking to the pivot table or create a table in the "Rankings" worksheet
Is there suppose to be an empty line between "wsR.Range("B2").FormulaR1C1 = _" and " "=RANK(GETPIVOTDATA..."
In "=RANK(GETPIVOTDATA(""Total"",Pivot!R3C1,""Rep"",RC1,""Item"",R1C),Pivot!R5C:R15C)", what does the reference
refer to

Regards
Ti


----- Debra Dalgleish wrote: ----

What version of Excel are you using
Did you try adding a new sheet, and using the macro
Where did you get stuck

Tim wrote
 
The GetPivotData function has different arguments in Excel 2000, than it
has in Excel 2002, where I created the code. That may be what's
preventing the values from appearing.

The code should create a table in the Rankings worksheet. There's no
empty line after 'wsR.Range("B2").FormulaR1C1 = _'. That was probably
caused by line wrap in the message.

You can send the workbook to me, and I'll take a look at it (Remove the
capital letters from my email address.)
 
Back
Top