Imported some data from Cognos and trying to manipulate it with Excel, this is something i do every month. Trying to create a Pivot table,

The cell value is, appears, identical. That the pivot table is not grouping them implies that the cells are actually different values.

Any suggestions as to what might be causing this, and what to do to sort it.

are the cell contents numerical or textual?

if the former, do a mathmatical transformation on them and reverse it such as x10 /10 sometimes excel treats numbers as text EVEN when you have reformatted.

if latter check for spaces at the end of the entry or an apostrophe at the beginning

doublepost

The cells are numerical, but i had problems earlier with a textual field not matching another textual field that it appeared to be the same as.

I have tried adding a blank cell, tried multiplying by a number, tried formating the cells as numbers and still no joy, Even tried Trim().

If i double click on the problem cells i can manually delete the last character( a space?) and then the cell behaves as a number/

if there’s a space in the cell it will treat it as text.

if you copy and paste the bad column somewhere then in the column next to your copy and pasted one back it =a1*10/10 then copy and paste values back.

do a find and replace on the column, find ” ” replace with “”, to get rid of the spaces

tried that and it failed.

sorted it, not elegant though,

IF(ISERROR(A1*10/10),VALUE(MID(A1,1,(LEN(A1)-1)),VALUE(A1))

seemed to work!

were you getting errors when you tried a maths function on a cell with a space at the end then?

The field was an account number, so although a numerical field it wasn’t strictly relevant that it was a number or a text string. I have one database where i have dumped the last 15months of sales data as it was the only way of doing the analysis. I have never paid any attention to the nature of the fields as everything behaved as it should. Until yesterday! Something about the data i imported yesterday is different. The fields look identical but are not.

Using Vlookup wasn’t working, so i landed up fiddling with the lookup tables, and breaking them!!!!!! before i figured what the problem was.

When i tried to treat the account numbers as numeric fields the old data allowed it, the new data did not. ( and gave error messages to that effect)

Still no idea why the data was different, at least i know where to start if it happens again.

hate cleaning and data handling.
Usually because some numpty* puts text in numerical cells etc etc.
Long live validation! 🙂
.
.
.
.
* the kind that uses Excel for colourful tables instead of Word…

finally getting there! the dataset has had a Char(160) added to everything. by substituting the character for a space and then using trim the results are clean. 😀

Now to figure out the best way of cleaning the 50000 lines in my Access database that are affected?

how irritating.
Looks like a kind of html export bug or something.
http://htmlhelp.com/reference/charset/iso160-191.html
Where/what software does your data come from?

the data comes from Cognos Reportnet, data is exported as a html view which can then be saved as Excel. I did get a stylesheet error, which i don’t normally and ignored it, as you do when the data “looks” ok. Live and Learn.
Might be easiest to delete the affected data, i know the affected range. then clean the Excel data line by line and then re-import.