vlookup failure & ctrl-f failure?

[joemeshuggah]

having some trouble understanding what is causing the following...

i have a report that we run monthly, each month the results of the report
are pasted in an excel spreadsheet that contains all the prior months
(appended each month so as to maintain a running total).

when i went to refresh the pivot table that summarizes the report data after
adding november data, values duplicated. i found that for whatever reason,
november's data was not being viewed the same as the months prior.

if i go into the raw data and do a control find for one of the november
values, it will only return values from november, even though there are
values in previous months that are the same.

i tried using the len function to see if there was a difference in character
length, but the result was 5 for all values.

text to columns did not work, nor did =left(a2,5)

however, left(a2,4) appeared to correct the issue.

any ideas on why a 4 in the left function would correct the problem as
opposed to 5 or text to columns?

 

[Pete_UK]

Perhaps you have a non-breaking space character in position 5.
Highlight the data, then CTRL-H:

Find what: Alt-0160
Replace with: leave blank
Click Replace All

where Alt-0160 means hold the Alt key down while typing 0160 on the
numeric keypad.

Hope this helps.

Pete

 

[Luke M]

Sounds like, somehow, the 5th character is not matching. Could you do a
direct copy of what you have to make your problem clearer?

If its not an exact match that explains why VLOOKUP and FIND are failing.
One other idea is that with dates, possibly there's some different times
(like 1pm vs 2pm) associated with it, or if you're not displaying years,
those could be different.

 

[joemeshuggah]

looks like that did the trick...much appreciated! where can i find more info
on character types as such? is there a way to pull up a list of character /
character types in excel?

Thanks!!!

 

[Pete_UK]

You're welcome.

You can put this formula in A1:

=CHAR(ROW())

then copy it down to row 255, and then you can see the characters that
Excel uses in that font. However, if you change to a symbol font like
Wingdings then they will appear differently.

Hope this helps.

Pete