Pivot Table - Copy All Data from a Specific Variable Data Field

[troy.litwiler]

Hi all. I have a question that seems like it would have an easy
solution, but several hours of searching through the message boards
hasn't solved it yet - so here it is:

I have a pivot table which is updated monthly from a database. I use
this pivot table to create a report based on the totals and
subtotals. One of the subtotals is based on a product ID, from which
there are thousands of possible options. The table itself typically
looks like:

A B C D
Group Type ID Count
MC D 123 12
MC D 124 15
MC N 167 2
MC N 198 9
SW D 109 3
SW N 108 5
SW N 105 9


So that the pivot table looks like:

A B C D
MC 38
D 27
123 12
124 15
N 11
167 02
198 09

Because the number of product ID's can change, the number of rows
beneath the "D" and "N" count varies. For my report, I need a total
of all the product IDs and their individual counts copied onto another
worksheet within the same workbook. I have tried using variable
ranges, offsets, if statements - but haven't even gotten close. Any
assistance with this would be appreciated. Thanks in advance.

 

[Herbert Seidenberg]

Here is a strategy, not all the details are shown.
Assume that part of your pivot table looks like this:

Group Type ID Count Gryp
MC D 35 67 MCD1
92 98 MCD2
93 40 MCD3
82 64 MCD4
N 61 87 MCN1
91 24 MCN2
SW D 93 31 SWD1
12 76 SWD2
N 97 30 SWN1

Next to the last column of the PT, add Gryp.
It is a text concatenation of Group, Type, Isblank() logic, and
the previous entry of Gryp. Sorry, I lost the formula, my PC hiccuped.
Select ID, Count and Gryp and
Insert > Name > Create > Right Column
Name a cell CopyGr and enter the name of the array
you want to copy, say MCD or SWD.
Select a suitably large array of cells and enter this array formula:
=INDIRECT(CopyGr&1,1):INDIRECT(CopyGr&MAX((LEFT
(Gryp,3)=CopyGr)*RIGHT(Gryp)),1)
If you had entered MCD in CopyGr, you would get this array:
35 67
92 98
93 40
82 64
It shrinks or expands as the PT changes.