Pivot filter sees duplicates when table doesn't

I have a table that is updated each month. When we add a new month the table sees the departments correctly. In the pivot table, it somehow sees duplicates. It sees all of the departments in the old months as one set, and then all of the departments in the new month as a new set of departments, even though most are the same thing. You can see for example in the attached file that the pivot table filter has two of the department 62, while the table filter only has one 62.

I can see that some of the departments in the raw data tab have an error on them where it says they are numbers stored as text. I don't know why that is there because the format for the whole column is set to number even though some of the cells have numbers and letters in them.

I have found two ways to fix this but need help finding one that works quickly all of the time, possibly a VBA solution.

The ways that fix this are to select the cells with the number as text error and let it convert them to numbers. This is great but there are almost 45k rows with more added each month. For some reason the error triangle doesn't show up sometimes and other times selecting all of the cells and letting it fix all of the cells will crash the program because there are too many rows.

The other way is to replace the value in the cell with itself. so if I find and replace 62 with 62, then suddenly the pivot table sees all of the 62's as one thing.

I attached the file to see if it helps.

Hello
I've just converted the column to numbers using the method in one of my posts (last post in thread).


http://www.excelforum.com/showthread...t=#post4087386

There's now only one 62 showing in the Pivot table. Does this help?

DBY

I'm no VBA expert but I'll try a few things out and get back if anything works.

I did a macro recording while doing the copy paste special multiply, but I couldn't figure out how to get the value of 1 into the code.

This example from SHG worked for me. http://www.excelforum.com/excel-prog...ly-in-vba.html

I don't know the difference between .value and .value2, but it works. I have seen other examples of this where people suggested .value = .value and that didn't work for me. I also tried .value = .value * 1 but I kept getting errors when it would hit a value that had numbers and letters in it.

Please Login or Register  to view this content.

Yes, I had some code running but every time at the end of the conversion I was getting run time error 13 type mismatch. But if you found a solution all's well.

Nice solution. Where there's a will there's a way!