I don't know pivot tables at all. I've managed to bumble through building a couple of them, and that's about it.
Now, because someone has thrown one bad bit of data into my table, several of these pivot tables are jacked up. And I have no clue how to correct it, nor how to find the bad data and kill it.
I'm using XL2007. I have two sets of four pivot tables that are all built the same way:
-- Row labels is "Projects" (project numbers, all in a format like A1234)
-- Column labels is "Late" (either an empty cell or the word "Late")
-- Values is "Count of Due Date" (all formatted as short date)
For each pivot table, the "Late" column points to one of four different "Late" columns (like "Milestone 1", "Milestone 2", etc). This yields a four-column pivot:
Project No. / Count On-Time / Count Late / Total Count
This last time that I refreshed the pivots, I get an extra column!! Sandwiched in between "Late" and "Total" is another "Late" column. It shows a number 1 on only one project. When I click the filter arrow at the top of the column, I see what appears to be a single-character data point (like a space in a cell), a list of project numbers, the word "Late", and (blanks) at the bottom. If I uncheck (blanks), my bad column goes away - but my count for that project is off by one. If I uncheck the "space", all I get is a count of the Late values!!
I went to the column of Late values, filtered out "Late", and ran a LEN formula down the side. Everything came up 0!!
I searched for a way to filter the pivot column by LEN > 2, but no go.
These are statistics the boss needs. How can I hunt this down and get rid of it??!
Ed
Now, because someone has thrown one bad bit of data into my table, several of these pivot tables are jacked up. And I have no clue how to correct it, nor how to find the bad data and kill it.
I'm using XL2007. I have two sets of four pivot tables that are all built the same way:
-- Row labels is "Projects" (project numbers, all in a format like A1234)
-- Column labels is "Late" (either an empty cell or the word "Late")
-- Values is "Count of Due Date" (all formatted as short date)
For each pivot table, the "Late" column points to one of four different "Late" columns (like "Milestone 1", "Milestone 2", etc). This yields a four-column pivot:
Project No. / Count On-Time / Count Late / Total Count
This last time that I refreshed the pivots, I get an extra column!! Sandwiched in between "Late" and "Total" is another "Late" column. It shows a number 1 on only one project. When I click the filter arrow at the top of the column, I see what appears to be a single-character data point (like a space in a cell), a list of project numbers, the word "Late", and (blanks) at the bottom. If I uncheck (blanks), my bad column goes away - but my count for that project is off by one. If I uncheck the "space", all I get is a count of the Late values!!
I went to the column of Late values, filtered out "Late", and ran a LEN formula down the side. Everything came up 0!!
I searched for a way to filter the pivot column by LEN > 2, but no go.
These are statistics the boss needs. How can I hunt this down and get rid of it??!
Ed
