Imagine a two column dataset – Customer Code and Material Number (with alphanumeric data). The objective is to determine the second highest quantity sold per Customer code.
Since we will first have to determine the Customer wise and Material Number wise quantity sold, a good way to get started is to use a Pivot Table. One can drag Customer Code and Material Number to the Row labels and Material Number (again) to the Value area section. We can then sort the numbers in the Value area section in descending order.
Now comes the bit of filtering the Pivot Table to only view the Material Numbers per Customer code which rank second. To do so, one can try the following steps:
1. Add the Material Number field once again to the Value area section
2. Right click on any cell in the MaterialNumber2 column and choose the “Rank Largest to Smallest” in the “Show Values as” option. This option is available only in Excel 2010 and higher versions.
3. Click on the Filter drop down of the Row labels heading and select Material Number in the drop down there. Now go to Value Filters > Equal > Count of Material Number2 > 2
While the steps above sound very logical, the result is incorrect. The criteria gets applied on the Count column rather than the Rank column. So the result will be all rows where the Count is 2.
One can overcome this problem by using the PowerPivot. You may refer to my solution in this workbook.
Filter the Rank Field in a Pivot Table
{ 4 Comments }