Pivot Table -

[Johnny_99]

Using Excel 2003, I'm having an issue in creating a Pivot table that exceeds
65K rows.

Starting data set is:
- 504 rows
- 78 columns, comprised of:
- 6 columns to go in "row" fields for Pivot
- 72 columns to go in "data" fields for Pivot

Here's the steps I go through:

1. select entire 78 x 504 range and create pivot in another blank sheet
2. add all 6 columns to "row" fields
3. With Grand-Total and Sub-Total rows included, the pivot now contains
1,948 rows.
4. When I remove all Grand-Total (Table Options, Uncheck Grand-Totals for
Rows & Columns) and Sub-Total rows (Field-Settings, SubTotal = None), the
pivot now contains 504 data rows (plus a few rows at top).
5. Next I start adding the 72 columns to the "data" section ... 1 is added
okay but the 2nd column causes the "too many rows" error ...
6. If I hide some of the 6 "row" fields (say 2 of 6), I can add more of the
"data" columns (perhaps 15) ... but I can't add all 72.

Obviously, some of the underlying data is exceeding the 65K limit (or that
is my guess).

Are there workarounds (eg. different sequence of steps or different steps)
or perhaps add-ins to assist? Or do I simply have to approach the data
differently to avoid this error?

Note: Ideally, as I see the Pivot, it should be 504 rows * 72 data fields =
36K rows ... obviously, there is more behind the scene that is limiting my
approach here.

Any technical or logical help is apprecaited. Thanks in advance,

John ...

 

[Roger Govier]

Hi Johnny

I responded a short while ago to a previous question of yours and provided
some code to programmatically add 60 columns to the data area.
In that post you were talking about a large data set of 20,000 rows and 100
columns.
Now you are talking about 504 rows and 78 columns.
I am confused.

Would you like to send me a copy of your file direct?
To mail direct
roger at technology4u dot co dot uk
Change the at and dot to make a valid email address

--
Regards
Roger Govier

Using Excel 2003, I'm having an issue in creating a Pivot table that
exceeds
65K rows.

Starting data set is:
- 504 rows
- 78 columns, comprised of:
- 6 columns to go in "row" fields for Pivot
- 72 columns to go in "data" fields for Pivot

Here's the steps I go through:

1. select entire 78 x 504 range and create pivot in another blank sheet
2. add all 6 columns to "row" fields
3. With Grand-Total and Sub-Total rows included, the pivot now contains
1,948 rows.
4. When I remove all Grand-Total (Table Options, Uncheck Grand-Totals for
Rows & Columns) and Sub-Total rows (Field-Settings, SubTotal = None), the
pivot now contains 504 data rows (plus a few rows at top).
5. Next I start adding the 72 columns to the "data" section ... 1 is added
okay but the 2nd column causes the "too many rows" error ...
6. If I hide some of the 6 "row" fields (say 2 of 6), I can add more of
the
"data" columns (perhaps 15) ... but I can't add all 72.

Obviously, some of the underlying data is exceeding the 65K limit (or that
is my guess).

Are there workarounds (eg. different sequence of steps or different steps)
or perhaps add-ins to assist? Or do I simply have to approach the data
differently to avoid this error?

Note: Ideally, as I see the Pivot, it should be 504 rows * 72 data fields
=
36K rows ... obviously, there is more behind the scene that is limiting my
approach here.

Any technical or logical help is apprecaited. Thanks in advance,

John ...


__________ Information from ESET Smart Security, version of virus
signature database 4738 (20100102) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4738 (20100102) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Johnny_99]

Roger - I sent you an e-mail with the sample file ... thanks for your help.

John ...

 

[Roger Govier]

Hi Johnny
I have been off line for a while.
File received, Pivot Table created without any problem and file returned to
you.

--
Regards
Roger Govier

Roger - I sent you an e-mail with the sample file ... thanks for your
help.

John ...



__________ Information from ESET Smart Security, version of virus
signature database 4738 (20100102) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4738 (20100102) __________

The message was checked by ESET Smart Security.

http://www.eset.com