pivot table in Excel 2007 & 2000 (backward compatibility problem)

[gordom]

Hi everyone.
I created a pivot table in Excel 2007. It had 5 columns and over 1800
rows (almost 10000 cells all together) and use to work fine in both
Excel 2007 & 2000.

There was one feature that I didn’t like however. It use to sort data in
one of the columns (column no. 2). Unfortunately these data should be
presented exactly as they were entered (without any sorting). To
workaround I added 1 extra column. It contains numbers in ascending
order. This column was placed in front of the column no. 2 and hidden.
Although I got rid of sorting, a new problem occurred – pivot works OK
in Excel 2007 only. Excel 2000 doesn’t accept such pivot table and I get
a message that it’s incorrect. If I want to reconfigure the pivot I get
a message that there is to many columns or rows and some of them should
be removed from the pivot.
Any ideas how to solve the problem of sorting without adding a new
column? Or maybe there is a way to force Excel 2000 to work with pivot
in conjunction with larger amount of data? Thanks in advance for any help.

gordom

 

[Shane Devenshire]

Hi,

The problem is that 2000 was has a much weaker pivot table, it can handle
far fewer unique items and by adding a column of unique numbers in front you
have increase the complexity beyond Excel 2000's capacity.

 

[Guest]

You could try to concatenate sorme columns.
Example,
Ff you you have one column named Client ID and another column named Client
Name,
and the same Client ID always refers to the same Client Name,
you could go back to your data source and create a new field called Client
ID and Name
which would include
the ID
plus a space
plus a hyphen
plus a space
plus the Client Name.

Then modify your PT layout to display Client ID and Name.
So instead of having to display two columns in the Pivot Table you now have
only one.
(or one less.)
This is a workaround to avoid memory limits in loder versions of Excel.



+++++++++++ Alternatively, +++++++++++++++
say you need to display 5 columns.
Start by just showing just the first 2.
Then go back to the PT layout and add 1 more column.
See if it works.
Then go back to the PT layout and add 1 more column.
See if it works.
Then go back to the PT layout and add 1 more column.
See if it works.

Sometimes, adding in PT columns gradually 1 by 1 - instead of all together -
will allow Excel to avoid the "too many columns problem".
Apparently, Excel 2007 does not have this problems.

Rosalie

 

[gordom]

You could try to concatenate sorme columns.

Then modify your PT layout to display Client ID and Name.
So instead of having to display two columns in the Pivot Table you now have
only one.
(or one less.)

Thanks for your help. I concatenated these 2 columns (the first one
consisting supplementary numbers & second with actual data) and Excel
2000 accepted pivot of that size. What I don’t know however is how to
split these data inside the pivot table report? I have to get rid of
this prefix (it can’t be visible). Is there any way to do so? I tried
few things but with no success.

+++++++++++ Alternatively, +++++++++++++++

Sometimes, adding in PT columns gradually 1 by 1 - instead of all together -
will allow Excel to avoid the "too many columns problem".

That didn't work.


Regards,
gordom