Extract Data from a Pivotable

[pjmorris]

Dear All,

Having tried to do this with GETPIVOTDATA I now believe that that function only extracts totals and subtotals. I'm trying to extract the heirarchical data and I wonder if this is possible (I suppose I could go back to the original data, but I want to know if I can extract it from the pivot).

The source data is as follows:

[TABLE="class: grid, width: 272"]
<tbody>[TR]
[TD="width: 68"]company[/TD]
[TD="width: 68"]unit[/TD]
[TD="width: 68"]name[/TD]
[TD="width: 68"]worker[/TD]
[/TR]
[TR]
[TD]Company[/TD]
[TD]AA[/TD]
[TD]Harry[/TD]
[TD]Buggins[/TD]
[/TR]
[TR]
[TD]Company[/TD]
[TD]AA[/TD]
[TD]Harry[/TD]
[TD]Arold[/TD]
[/TR]
[TR]
[TD]Shop[/TD]
[TD]NN[/TD]
[TD]Julie[/TD]
[TD]Ken[/TD]
[/TR]
[TR]
[TD]Shop[/TD]
[TD]NN[/TD]
[TD]Julie[/TD]
[TD]Lora[/TD]
[/TR]
[TR]
[TD]Company[/TD]
[TD]CC[/TD]
[TD]Martin[/TD]
[TD]Nick[/TD]
[/TR]
[TR]
[TD]Company[/TD]
[TD]AA[/TD]
[TD]Harry[/TD]
[TD]Cooper[/TD]
[/TR]
[TR]
[TD]Company[/TD]
[TD]AA[/TD]
[TD]Harry[/TD]
[TD]Denis[/TD]
[/TR]
[TR]
[TD]Company[/TD]
[TD]BB[/TD]
[TD]Ian[/TD]
[TD]Eric[/TD]
[/TR]
[TR]
[TD]Company[/TD]
[TD]AA[/TD]
[TD]Harry[/TD]
[TD]Frank[/TD]
[/TR]
[TR]
[TD]Company[/TD]
[TD]BB[/TD]
[TD]Ian[/TD]
[TD]Gertrude[/TD]
[/TR]
</tbody>[/TABLE]


The Pivot Table output looks like this:

[TABLE="class: grid, width: 383"]
<tbody>[TR]
[TD]company[/TD]
[TD]unit[/TD]
[TD]name[/TD]
[TD]worker[/TD]
[/TR]
[TR]
[TD]Company[/TD]
[TD]AA[/TD]
[TD]Harry[/TD]
[TD]Arold[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Buggins[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Cooper[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Denis[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Frank[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]BB[/TD]
[TD]Ian[/TD]
[TD]Eric[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Gertrude[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]CC[/TD]
[TD]Martin[/TD]
[TD]Nick[/TD]
[/TR]
[TR]
[TD]Shop[/TD]
[TD]NN[/TD]
[TD]Julie[/TD]
[TD]Ken[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Lora[/TD]
[/TR]
</tbody>[/TABLE]


I would like to be able to refer to say 'Frank' and return that he works for the 'Company' company , in the 'AA' Unit, with a boss named 'Harry'. If I hover my mouse of the name Frank I get a popup that provides just that information, but I can't find a way of doing it either with code or a function; as noted above I cant make GETPIVOTDATA achieve this result.

Grateful for any advice.

Regards

 

[mart37]

When you repeat all itemlables you can use INDEX and MATCH to extract data from your Pivot Table.

 

[pjmorris]

Hi Mart,

How simple! I assumed I could do it, just didn't know how.

Many thanks, much appreciated.

Regards

Peter