Mystery - Excel treats the same cell contents differently

I saw a document somewhere in the past couples of weeks, which I wanted
to review in greater detail but now I can't find it. It stated a
problem within Excel where cell contents appear to be the same to the
eye, but not to Excel. Can anyone point me in the right direction!

Probably to do with text vs numeric

Take alook at these

123
123

They are not the same - The second has a trailing space and will make it
Text as far as Excel is concerned.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------



"Dale" <[email protected]> wrote in message
news:[email protected]...
>I saw a document somewhere in the past couples of weeks, which I wanted
> to review in greater detail but now I can't find it. It stated a
> problem within Excel where cell contents appear to be the same to the
> eye, but not to Excel. Can anyone point me in the right direction!
>

Don't know what 'document' you saw, but there are several things of that ilk
that come to mind right away.

3 examples:
'123 entered in a cell (leading apostrophe) is NOT the same as a numeric 123
"Dale " would look the same as "Dale", but Excel sees the extra space and
says they're not the same
12.499 formatted as Comma2 will LOOK like 12.50, but it's NOT


"Dale" wrote:

> I saw a document somewhere in the past couples of weeks, which I wanted
> to review in greater detail but now I can't find it. It stated a
> problem within Excel where cell contents appear to be the same to the
> eye, but not to Excel. Can anyone point me in the right direction!
>
>

Ken Wright wrote...
>Probably to do with text vs numeric
>
>Take alook at these
>
>123
>123
>
>They are not the same - The second has a trailing space and will make it
>Text as far as Excel is concerned.
....

You don't even need different types. Unless column widths are huge or
number format is set to show 15 decimal places, both the following cell
*contents*

122.99999999
123.00000001

will display as 123.

Very true, guess i was a bit quick off the mark there. :-)

That scenario will likely be the common one, especially with numbers
generally being formatted to a rounded state.

Regards
Ken...................


"Harlan Grove" <[email protected]> wrote in message
news:[email protected]...
> Ken Wright wrote...
>>Probably to do with text vs numeric
>>
>>Take alook at these
>>
>>123
>>123
>>
>>They are not the same - The second has a trailing space and will make it
>>Text as far as Excel is concerned.
> ...
>
> You don't even need different types. Unless column widths are huge or
> number format is set to show 15 decimal places, both the following cell
> *contents*
>
> 122.99999999
> 123.00000001
>
> will display as 123.
>

I checked the format which is General in both cells, the content is
"50962" and no leading or trailing chars, but my pivot table counts
these as different things. I manually have to copy/paste to get rid of
the dup. Any thoughts...

Select all your data and format as any numeric format.
Now put a 1 in any empty cell, copy that cell, select all your data and do
edit / paste special / tick values and multiply.
Now refresh the Pivot table

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------


"Dale" <[email protected]> wrote in message
news:[email protected]...
>I checked the format which is General in both cells, the content is
> "50962" and no leading or trailing chars, but my pivot table counts
> these as different things. I manually have to copy/paste to get rid of
> the dup. Any thoughts...
>

I have found two instances where two identical numbers were treated differently in Pivot tables in one case some of the data was imported with a space, I highlighted all of the data and used replace to remove all of the spaces from the numerical Data. in the other case which sounds like yours Everything appeared the same and there were no spaces in the data but 602 was treated differently than another 602 my pivot table had two 602s (vehicle number 602)and the values associated with each 602 would be counted separately I found that refreshing the 602 by hitting f2 enter made them calculate the same (a symptom of this was that despite all cell content, formatting, and alignment being the same one 602 would align to the right wile the other aligned to the left.)