Solved

Pivot Table "Not Enough Memory" error when using "Show Items with No Data"

Posted on 2010-11-13
10
2,307 Views
Last Modified: 2012-05-10
I created a pivot table in Excel 2003 that recap sales.  The pivot table is filtered using the sales rep ID in the PAGE section.  The ROW includes sales type (for example, grocery, meat, produce, etc.) and customer (ID and name) while the COLUMN includes period number.  The DATA recapped is sales dollars.

The problem is that I want the pivot table to display all of the sales rep's customers (by sales type) even if they do not have any sales.  I want to be able to include those customers that have no sales with those that do.

I tried to select the "Show items with no data" box in the PivotTable Field for the customer ID; however, I receive the error "There is not enough memory available to create the PivotTable report.  To make more memory available, try the following: Reduce the number of column or row fields you include in the PivotTable report.  Close files or programs you aren't using".

The solutions offered do not work for me as I  can't reduce the number of rows because I want to be able to display all of the customers for the respective sales rep and sales type.

I appreciate any help that you can provide.

Thanks,
Jack

P.S. In my current project, I link the pivot table to data in Access; however, I have also had this problem when the data is in the same Excel spreadsheet.

0
Comment
Question by:jack3_99
  • 4
  • 2
  • 2
  • +1
10 Comments
 
LVL 6

Expert Comment

by:J79123
ID: 34129948
you could be at the limits of the pivot table. These are the limits:
http://support.microsoft.com/kb/820742

Basically, what I gather from this article is that for your situation....
if you have let's say 100 unique customers, 12 unique period ids, 4 unique sales types, and 3 unique salesmen... that would be 100x12x4x3 = 14,400 column items.
Column items can't be more than 32,768, so if there were 10 salesmen for example, then you'd be over the limit because that's 48,000 (100x12x4x10)
I can't imagine you'd have too many row items, because the limit on that is 2.1 billion. So you probably have too many column items, because it seems pretty easy to get over the limit there.

I don't think excel 2007 has this issue, because it has the power-pivot addin which uses sql server express ... and you can manage a ton of data with that. So if you have access to excel 2007, that might be a option for you.

Otherwise, the article recommends reducing the column fields in your pivot table to only those fields that are absolutely necessary. ... I'm thinking that's salesmen and customers.  Then as long as (unique salesmen x unique customers) < 32,768 the report should run.
You can put the other fields (Sales type and period) in the page field.. You'd have to change the page field to get the information for each sales type and period, but it might keep you under the 32k memory limit if you don't have those fields as column items.


0
 
LVL 18

Expert Comment

by:Richard Daneke
ID: 34130947
More importantly, Excel 2007 (and 2010) has many more rows (255 vs 16,384) and columns(1,048,576) and NO PROGRAM LIMIT on worksheets (Excel 2003 was limited to 255 worksheets plus 1 for macros)   Some limits are now set by your computer and not just Excel.
0
 
LVL 18

Expert Comment

by:Richard Daneke
ID: 34130957
Two more quick notes:
1. Excel 2010 offers a new feature called Web Slices that offers dynamic filters for your data
2. Store your data in Access or a separate closed Excel file.  Since Pivot Tables work with the data in memory, should you also have your data in a worksheet, you are unneccessarily duplicating the data - reducing the available memory for the pivot table!
0
 
LVL 18

Expert Comment

by:Richard Daneke
ID: 34130976
Correction:
More importantly, Excel 2003 vs Excel 2007 (and 2010) has many more columns(255 vs 16,384) and rows(65,536 vs 1,048,576) and NO PROGRAM LIMIT on worksheets (Excel 2003 was limited to 255 worksheets plus 1 for macros)   Some limits are now set by your computer and not just Excel.
Weak excuse: as a former Multiplan users I still think Rows and Columns when references go Columns and Rows!!
SO, PivotTables cannot show more columns or rows than are available in a worksheet EVEN IF the pivot table field limits referenced earlier http://support.microsoft.com/kb/820742 are not exceeded.
0
 

Author Comment

by:jack3_99
ID: 34131994
I don't believe there is a problem with the number of rows or columns.  Attached is sample data with a corresponding pivot table.  I get the memory error when selecting the "Show items with no data" box in the CustID field even though this is only data sample.

Included also are a couple of mock-ups of how I would like to display the information. As an example, I would like to display Customer #10035297 even though there are no dollars for the customer.

The reason we want to do this is so that we can review customers with no sales along side customers that do have sales.

If this is beyond the realm of pivot tables, are there any alternatives?

Thank you for your help,
Jack

 PivotTableExample.xls
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34135656
If you select that option, then it will attempt to display all customers for each of your Slsperid and Type combinations, which is why it runs out of memory.

And FYI, XL2003 is *not* limited to 255 worksheets; like 2007, it is limited only by available memory.
0
 
LVL 18

Expert Comment

by:Richard Daneke
ID: 34135919
I am corrected in the limit on Excel 2003 worksheets.  Mea Culpa.

I have used the sample data in Excel 2010 without error.  You can download a free trial copy of 2010 from Microsoft.  Be warned, creating Pivot Tables looks different in the 2010 product.
0
 
LVL 6

Accepted Solution

by:
J79123 earned 300 total points
ID: 34136150
The name is directly related to the ID, plus it's informational. (and yes I'm assuming you actually have names in there on your original version, and its not the redundant "Customer ID:+ID" data you posted, so this takes that into account).
 
But I would think that the name field is what is putting you over the limit. Like customer IDxCustomer name is eating up the memory. So since you can get the info from one field by looking at the other maybe you can create a field in your data that combines these 2 fields into 1 field
Like add a new column called CUSTIDNAME with a formula of
=c2&" "c3
Then drop the cust ID field and customer name fields from your table and use CUSTIDNAME instead.
Unfortunately, I'm not sure if this will solve your issue because I don't have 2003 installed to check.
0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 200 total points
ID: 34136233
To clarify my earlier response:

Say you have data with three fields: Type, Customer and Value.
The Type field has two values, A and B
The customers field has Customers 1 to 10, with Customers 1 to 9 belonging to Type A and Customer 10 belonging to type B.
Now you create a pivot with type and customer as row fields - by default Customers 1 to 9 appear under A, and Customer 10 appears under B. Now change the Customer field to display items with no data. You will see that EVERY customer appears under BOTH types. (sample file attached). Now extrapolate this to your sample - for EVERY combination of Slsperid and Type, you will get EVERY customer listed. That's potentially a lot of data, and a lot more rows than you may be expecting!
PivotSamp.xls
0
 

Author Closing Comment

by:jack3_99
ID: 34144191
Thank you for all of your time and patience in explaining your answers.  I appreciate your help very much.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now