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.

• Anil says:

Hi Ashish,
How do i do calculations from 2 separate Pivot tables. For eg. In PIVOT Table eg demand PIVOT which is computed from one sheet, I have

Skill Level1 Level2 Level3 … Total

Skill 1 4 8 18 …
Skill 2
Skill 3
Skill 4
Skill 6
In another pivot i have data from another sheet call Supply Pivot
Skill Level1 Level2 Level3

Skill 1
Skill 2
Skill 3
Skill 4
Skill 5
I want to get the difference between the Demand and Supply Pivot. Note that in both pivots, the same skill may not be present. For eg if Skill 5 is in Demand , it may not be in Supply Pivot. In the end, I want one Pivot with the differences of the all the skills between Demand and Supply or at times Supply and demand.

• Hi,

Use a VLOOKUP() and MATCH() combination. That should work well.

• Anil says:

Hi Ashish,
Why VLOOKUP and not Index function. ?? In fact i have done that and put the formulas for getting the differences. Is there a way where u can import these two data sheets into MS query and get the differences in a new pivot

• Hi,

You may very well use the INDEX() function if you so wish. by VLOOKUP(), I just meant to refer to lookup related functions.