(Repost) - Pivot table: Grouping first row field by fifth row fiel

G

Guest

Hi,

For you pivot table experts, I have five pivot table row-field categories:
IO,
CC, CC Mgr, RC, and DAC - all within one pivot table. I would like to group
(or sort) IOs (the first-order row category), by the items in the DAC row
field (fifth-order row category). There isn't such a feature in Excel 2003 to
group (sort) a first-order row field according to a subsequent-order field,
is there? Assuming that there isn't, I would need to use VBA to do this
grouping. I would also like to do another grouping (or sorting) of IOs (the
first-order row category) by the RC (the fourth-order row category).
Therefore, I would need more code to this secondary grouping. Any help would
be immensely appreciated!

Many thanks in advance,
 
G

Guest

does anyone know how to do this? I can't use the regular Sort tool on the
tool bar to do the sorting, it must be a pivot table function.... Anyone?
Anyone? :)
 
P

par_60056

Hi,

For you pivot table experts, I have five pivot table row-field categories:
IO,
CC, CC Mgr, RC, and DAC - all within one pivot table. I would like to group
(or sort) IOs (the first-order row category), by the items in the DAC row
field (fifth-order row category). There isn't such a feature in Excel 2003 to
group (sort) a first-order row field according to a subsequent-order field,
is there? Assuming that there isn't, I would need to use VBA to do this
grouping. I would also like to do another grouping (or sorting) of IOs (the
first-order row category) by the RC (the fourth-order row category).
Therefore, I would need more code to this secondary grouping. Any help would
be immensely appreciated!
Is there a reason that IO is your first row field? Do you need to
report grouped on IO or can you reorder your fields to put DAC first,
RC second and the IO (or IO last)

If you need groupings by all 3, you will probably need to write a
macro to do the totaling by RC and DAC. For this type of thing I use
dictionary objects for totalling and then print them out at the end of
the pivot table.

Peter
 
G

Guest

Hi Peter,

First of all, thank-you for your reply. I am reporting data for a
project-matrix organization. Therefore, my client needs to have data reported
by the smallest data field, namely the internal order. The last row field is
actually the largest grouping of data, and logically, you would think that
this would be the first grouping. However, since this is a matrix
organization and projects cross other responsibility centres (RCs), cost
centers (CCs), and deputy assistant commissioners (DACs), the driver in the
data is the internal order, and therefore, the row fields are organized in
reverse order to their parent-child relationship.

With in mind, how would I force the pivot table using VBA code to group in a
reverse fashion - i.e group DACs by the RC and group RCs by the cost centre.

You mentioned that I could use a dictionary object to accomplish this task.
I am unsure how this works. Any help would be very appreciated!

Thanks in advance,
 
G

Guest

I could do the re-ordering manually, but as soon as I refresh the data
underpinning the pivot tables, the any new internal orders would not be
placed in the proper sequence since Excel isn't that intelligent keep your
ordering system....
 
G

Guest

How about using the pivot table values as a source in a formula placed in
another fixed table. At this point, I can use Excel's sort function to sort
any way I wish subsequent to the refresh of data from the pt. I would also
have much more flexibility in formatting the table... but this would entail
much more templating work, etc.

What are people's thoughts on this pivot table reverse-grouping dilemma?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top