File size issue

[normpam]

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?

 

[SpillerBD]

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.

 

[normpam]

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.

 

[SpillerBD]

Merch Number. How many characters in this number?

 

[normpam]

maybe 10 or 12 - does it make a big difference?

 

[SpillerBD]

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.