Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table -
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 ... |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table -
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 "Johnny_99" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table -
Roger - I sent you an e-mail with the sample file ... thanks for your help.
John ... "Roger Govier" wrote: 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 "Johnny_99" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table -
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 "Johnny_99" wrote in message ... Roger - I sent you an e-mail with the sample file ... thanks for your help. John ... "Roger Govier" wrote: 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 "Johnny_99" wrote in message ... 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 __________ 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to create pivot table from existing pivot table in excel 2007 | Excel Discussion (Misc queries) | |||
Pivot Table and Pivot Table dates are not in correct order | Charts and Charting in Excel | |||
Copying values from pivot table to cells outside pivot table | Excel Discussion (Misc queries) | |||
Filter lines containing pivot table and non pivot table data | Excel Worksheet Functions | |||
How do I create a pivot table if the pivot table icon or menu ite. | Charts and Charting in Excel |