PDA

View Full Version : Getting around Pivot Table 256 Col Limitation



Hamond
06-23-2008, 03:58 AM
Hi,

I'm accessing an external text file using a pivot table from excel but the number of columns is exceeding the 256 Column Limit in excel. Like many, I don't have the latest version.

Does anyone know a way to show more than 256 columns for example, showing pivot table or even a static copy of results of the pivot table in multiple sheets?

I've seen macros that let you import more than 65K row limit but importing the data across multiple sheets, was just wondering if there is anything similar for columns and results from a pivot table.

The columns/Fields in the source file are:

1) Date
2) Code
3) Price
4) Volume
5) Discount

And my pivot table is arranged as Row: Date, Column: Code, Main Body:Price. So my codes exceed 256, there are around 650 codes. Once I have the data in this format, I don't need to modify it anymore so I was thinking if there was a way to show say the first 256 codes, copy the results onto another sheet and then show then next 256 etc.

Hope someone can provide some advice.

Thanks

Hamond

marshybid
06-23-2008, 05:09 AM
Hi there,

Would it be easier to transpose your rows and columns;

have your current row headers as columns and the column data as rows.

Marshybid

Hamond
06-23-2008, 05:52 AM
Hi Marshybid,

I wish it was that easy but I have even more dates than codes (up to 10,000) so the number of columns would still exceed 256.

Hamond

marshybid
06-23-2008, 06:05 AM
Hi Marshybid,

I wish it was that easy but I have even more dates than codes (up to 10,000) so the number of columns would still exceed 256.

Hamond
Hi Hamond,

Can you post an example workbook. I have been doing a lot of work with Pivot Tables recently and may be able to suggest something, but I need to see what you are trying to sort.

Marshybid

Hamond
06-23-2008, 09:32 AM
Hi,

I've attached an example. The worksheet "Source" contains the raw data and the Pivot Sheet contains the pivot table with the feilds transposed currently showing the data for 255 codes only versus the 516 in the source sheet.