very simple problem?
Posted by George A. on July 11, 2001 3:47 PM
I worksheet of data and generate some pivot tables
from it. One of the columns has specs 0.24, 0.25, 0.28, etc.
When I generate the pivot table the 0.28 shows up twice.
I have checked the source data again and again and I cannot
find any difference in the 0.28s. No formatting no extra spaces
no clue.
It does appear that one is left aligned and one is right
aligned and I don't see that.
any thoughts? and any way to automate the corrections once
the problem is figured out?
this seems so simple but I can't see it.
thanks
Posted by Damien on July 11, 2001 4:20 PM
make sure that there was no rounding function done on any of the data shouldn't make any difference but you said already that you check the cell format seems strange
Posted by George A. on July 11, 2001 4:59 PM
okay now here is the latest. It appears that the
number is just not being recognized as such.
i changed the number of decimals to 5 and it never
changes the format (the other do). As soon as I
retype 0.28 into the cell it goes out to 5 decimals
like the other 0.28s.
this is a nightmare since there are so many data point
i don't want to have to retype them all (and paste
value doesn't work)
Posted by Sam Gaylord on July 11, 2001 8:26 PM
I have the identical problem, See "Identical cell contents not really identical??" Excel will sort these items differently as well. I'll let you know If I figure it out before you do!! haha!
Posted by Sam on July 11, 2001 8:38 PM
George,
I bet your number (and mine) is text. See the posts below titled "won't sort." Now all we have to do is figure out how to fix it.
Posted by Aladin Akyurek on July 12, 2001 2:39 AM
George,
Just a try, considering Sam's hypothesis:
Insert a column next to the column of 0.28's. Lets say that these columns are A and B.
In B1 enter: =A1+0 [ copy down as far as needed ]
Select all the values in B, do a Paste Special|Values over column A, then delete column B.
Aladin
Posted by stevie windows on July 12, 2001 2:40 AM
I had a very very similar problem - the answer was so stupid,I feel embaressed posting it. One of the two values had a space in it that the other didnt, so the pivot table read it as a seperate value. It was a while ago, and I can't remember how they were formatted. It's a tough one to spot though.
Posted by Loren on July 12, 2001 5:07 AM
Try the Value function, converts all to numbers
Posted by George A. on July 12, 2001 3:08 PM
The value function actually works. As does selecting
one of the 0.28 and auto filling. Although with
the volume of data, I would prefer not to use that.
I would still like to figure out the reason too.
Using auto filter, the number actually gets filtered
the correct way and only one 0.28 show up.
It only appears different in the pivot table.
Talk about confusing. Maybe this helps, the 0.28
was formatted 'general' before. I changed this to
number but it still did not like it. As soon as I
retyped it, it was okay.
