File size issue

normpam

Active Member
Joined
Oct 30, 2002
Messages
354
Working with a file of 65mb - a bit over 317,000 rows. Trying to get some Pivot Table data, and three times it is coming up with different results as to the number of items being reported. (Working with a 'Merch Number' - there are 1667 unique ones after removing duplicates, but the Pivot Table results are only showing 319, or 830, etc.

Is there an inherent limitation for Pivot Tables to work based on number of rows?
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
First, no. Some limitation differences between 32-bit Excel and 64-bit Excel do exist, but known that I can think of to generate any inconsistencies.
However, with any large set of data and seeking Unique Counts in a Pivot Table, you should be using the DataModel.
Also depending on your source data, there may be some subtle differences in values/text that seem to be the same. Using Get&Transform (formerly known as PowerQuery) you can clean your data much better.

Only 317k rows? I was hitting 5mil on a recent project.:cool:
 
Upvote 0
Thanks Spiller. Interesting. What I did was to use Remove Duplicates on the original file on the 'Merch Number' column, and came up with 1,667 records. That is how many were expected. However, the Pivot Table results for the data set showed only around 832 merch numbers. Very strange.
 
Upvote 0
just making sure it wasn't over 15.
For me, when a "number" is not really a number like credit card "numbers" I treat as Text and make sure the formatting remains or is converted to text. Or when you have account numbers, if you add to account numbers together you wouldn't necessarily get a valid account number back. In such case I treat/convert to Text.
You'll also find that such values are treated as different items. The value of 123 is different than the text string "123" and would be listed twice.

Take your original data and send to to a new PivotTable. During the step where you confirm the range and destination, there is a box to "add to the Data Model". check that.
Now, proceed to create the Pivot Table. Add "Merch Number" to the rows. Also drop "Merch Number" into the Values area. Right Click the one in the values area and change from SUM or COUNT to UNIQUE COUNT. It'll be the last one listed.
You should be able to a count of "Merch Number" by selecting the list of "Merch Number" and checking the COUNT in the status bar.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top