(reposte X 3) Anyone???? Have I stumped the Excel community?

[Guest]

Hi,

For you pivot table experts (and project managers), I have five pivot table
row-field categories: IO (Internal Order), CC (Cost Centre), CC Mgr, RC
(Responsibility Centre), and DAC (District Assistant Commissioner's Office) -
all within one pivot table. I would like to group (order) my data starting
with the items in the DAC row field (fifth-order row category) and then group
(order) items in the RC row field (fourth-order row category) within the DAC
categories.

Assuming that there isn't such a tool, I would need to use VBA to do this
grouping (or ordering if you prefer).

Conventionally, the smallest child field should be at the end of row fields
with the largest grouping at the beginning. This would make
sorting/ordering/grouping easy. However, my client has a project-matrix
organization with projects crossing many divisions. Therefore, the smallest
grouping (i.e. the Internal order - IO) is the driver of the data and resides
at the beginning of the row fields with the largest grouping (the District
Assistant Commissioner's office - DAC) residing at the end of my row fields.
I believe that I need VBA code to do the ordering since this ordering is
starting at the end of my row fields and not at the beginning...

Please help

Many thanks in advance,

 

[par_60056]

Hi,

For you pivot table experts (and project managers), I have five pivot table
row-field categories: IO (Internal Order), CC (Cost Centre), CC Mgr, RC
(Responsibility Centre), and DAC (District Assistant Commissioner's Office) -
all within one pivot table. I would like to group (order) my data starting
with the items in the DAC row field (fifth-order row category) and then group
(order) items in the RC row field (fourth-order row category) within the DAC
categories.

Assuming that there isn't such a tool, I would need to use VBA to do this
grouping (or ordering if you prefer).

Conventionally, the smallest child field should be at the end of row fields
with the largest grouping at the beginning. This would make
sorting/ordering/grouping easy. However, my client has a project-matrix
organization with projects crossing many divisions. Therefore, the smallest
grouping (i.e. the Internal order - IO) is the driver of the data and resides
at the beginning of the row fields with the largest grouping (the District
Assistant Commissioner's office - DAC) residing at the end of my row fields.
I believe that I need VBA code to do the ordering since this ordering is
starting at the end of my row fields and not at the beginning...

Please help

Sorry, I've been a bit tied up with a hardware failure which is
causing everything to fall behind.

I have several reports that have to do similar things. To get access
to the dictionary object, you need to add a reference (in the VBA
editor, tools->references) to "Microsoft Scripting Runtime". What I
do is create the pivot table in the most useful form (which you
already have), copy the entire table (pastespecial values only) to a
blank sheet. Create a dictionary for each point of data you want to
total (cost, hours ...) Then loop through the data create a
dictionary key based on the fields you want to total by
("DAC"+currDACValue, "CC"+currCCvalue) and add the current row values
to the dictionary.

Unfortunately, the key order in the dictionary is the order created so
when you get to the end of the data, you will need to read all the
keys out of the dictionary, sort them and then put out the information
by referening the keys in the sorted order.

Hope this helps.

If you still need more information, leave me a note and I'll try to
pull together some sample code outside the group.

Peter

 

[Guest]

Wow Peter.

Thanks so much! This is a fresh new tangent of Excel functionality for me.
I'll try follow in this vein of data management, but I'm sure that I will
need to reposte with a question or two when I inevitably run up against
roadblocks. You seem to be in the minority of Excel users that are having to
working with dictionary objects.

Cheers,

 

[par_60056]

Wow Peter.

Thanks so much! This is a fresh new tangent of Excel functionality for me.
I'll try follow in this vein of data management, but I'm sure that I will
need to reposte with a question or two when I inevitably run up against
roadblocks. You seem to be in the minority of Excel users that are having to
working with dictionary objects.

Cheers,
--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.948-9557

As you have found, some times pivot tables do exactly what you need
them to and sometimes you need it summarized in different ways. Many
people would make a different pivot report for the other way using the
same data but sometimes the people that you need to make the report
for want all the numbers on the same page.

Peter

 

[Guest]

Indeed, and clients keep wanting more data crammed into tighter spaces, which
makes printing and formatting challenging. Changes are continuously being
made on the fly. I guess that keeps people like you and me employed. This is
good thing, no?

If you have spare time between handling hardware problems at work, would you
mind showing me an example of creating dictionary objects and using them to
do custom sorting functions on pivot table paste-values? I'm a little
stumped.

I appreciate any help that you could offer....

 

[Guest]

Thanks for the call Peter! Just to confirm, my e-mail at work is
"(e-mail address removed)", and I've taken the "donotspam" off my personal
e-mail, "(e-mail address removed)".

Cheers!
--

Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.948-9557