Yep, done this, and it resolved the problem. I guess we will have to do this
on a daily basis to ensure none of the users pasted data with a space
included at the end.

Thanks again for all your help. This website has to be one of the best
sources of information and help the I have used to date!
--
Linda


"JP Ronse" wrote:

Hi Linda,

Is Edit/Replace find:<space replace:<nothing not the easiest way to do?

Wkr,

JP

"mathel" wrote in message
...
I found the problem! The data we are adding to worksheet 'Exposed' on a
daily basis is a 20 digit number, so the column in the worksheet is
formatted
to 'Text'. The data is being copied from a Word document, and we use
'Paste-Special-Text' into the spreadsheet. What I found was that if I
double
click the number in the Word doc, there is a space at the end of it On
all
numbers where there is a space at the end, VLOOKUP will not do a match.

That being said, I did find a piece of code that found the number whether
it
had a space at the end of it or not. The problem is, this code is to
replace
the text in the found row. I do not know anything about VBA, so don't
know
how to modify it so that it would, lets say, change the cell interior to
Yellow. The code is as follows:

Sub Replace_TExt()


For i = 1 To
ActiveSheet.Range("B:B").Cells.SpecialCells(xlCell TypeLastCell).Row

If Trim(ActiveSheet.Range("B" & i)) < "" Then

ActiveSheet.Range("A:A").Replace What:=ActiveSheet.Range("B" & i),
Replacement:=ActiveSheet.Range("B" & i).Value, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
False, ReplaceFormat:=False

End If

Next i
End Sub

Is it possible to have this code 'modified' to change the cell vs replace
text?

THANK YOU so much for all you help/comments in finding what the problem
was
with my ws.
--
Linda


"Dave Peterson" wrote:

I don't see how using VBA would help find an exact match if you can't do
it
using formulas.

I'd look for differences in the data.

Debra Dalgleish has lots of notes on troubleshooting =vlookup():
http://contextures.com/xlFunctions02.html#Trouble

If that doesn't help, what are you matching on--simple text, whole
numbers,
times, dates, fractions, long strings????

mathel wrote:

I've checked thru the Discussion group and found this subject has been
asked
several times, but can't find something I can use.

I am using Excel 2003 and have a wb with 2 sheets - 'Exposed' and
'Transactions'. I need to compare Column A in ws 'Transactions' to
Column a
in ws 'Exposed'. If there is a match, have the data show on the same
row in
Column b on ws 'Exposed'. The number of rows on both ws can vary
anywhere
from 500 rows to as many as 5000. I know the simple answer is to use
VLOOKUP, unfortunately, there are multiple users (and PCs) using this
wb and
for whatever reason the formula does NOT always work and the data is
being
missed so I am looking for VBA.

Even if the data in Column A could not be put into Column B, I would be
happy if the cell was highlighted.

Thanks
--
Linda

--

Dave Peterson