Hi Linda,

Based on the latest feedback...

In transactions, following sample data:

Row 1: 1
Row 2: 2
Row 3: =3 & " " & CHAR(9)
Row 4: 4
....
Row 7: =7 & " " & CHAR(10)
....

In Exposed:
Row 1: 1
Row 2: 2
Row 3: 3
....
Row 9: =9 & " " & CHAR(12)

The macro CompareData gives in column B
1 1
2
3 3
4
5 5
6
7 7

8
9 9
10


It has also found the 'duplicates' for 3, 7 and 9.

Wkr,

JP

Sub CompareData()
Dim colTransactions As Collection
Dim varExposed As Variant
Dim lngRow As Long
Dim lngCount As Long
''' read the data
Dim Timer As Date
Dim varRes As Variant

Timer = Now()

Application.ScreenUpdating = False

Set colTransactions = New Collection

With Sheets("Transactions")
.Activate
lngRow = .Cells(1, 1).CurrentRegion.Rows.Count
varExposed = .Range(Cells(1, 1), Cells(lngRow, 1))

For lngCount = 1 To lngRow
colTransactions.Add Item:=varExposed(lngCount, 1),
Key:=Trim(Application.Clean(CStr(varExposed(lngCou nt, 1))))
Next lngCount
End With

With Sheets("Exposed")
.Activate
.Columns("B").ClearContents
lngRow = .Cells(1, 1).CurrentRegion.Rows.Count
varExposed = .Range(Cells(1, 1), Cells(lngRow, 1))
End With

On Error GoTo Eror_CompareData
For lngCount = 1 To lngRow
varRes =
colTransactions(Trim(Application.Clean(CStr(varExp osed(lngCount, 1)))))
''' value iin Exposed found in Tranasactions
Cells(lngCount, 2) =
colTransactions(Trim(Application.Clean(CStr(varExp osed(lngCount, 1)))))
Next_Exposed:
Next lngCount

Exit_CompareData:
Debug.Print Now() - Timer
Exit Sub
Eror_CompareData:

Resume Next_Exposed
End Sub
"mathel" wrote in message
...
After reading both your comments and Dave Peterson's, I found the problem
with my data. When adding more data to the WS, it is copied from a Word
doc.
Some people (I think me mostly), double click the data to be copied, vs
dragging across the info to hightlight & copy. The double click method
will
include a 'space' at the end of the data. VLOOKUP will not match/find any
of
the rows where the data has a space at the end.

Any work-around in the formula, or different formula that can be used that
will recognize the data with or without the space? The formula now used
is:
=VLOOKUP(A1,'Daily Transactions'!$A$:$A$19801,1,FALSE)

The type of data I am using is a 20 digit number, the ws has the column(s)
formatted as Text, and when copied from Word to the ws, we us
Paste-special-text.

Thanks
--
Linda


"Joel" wrote:

If VLOOKUP isn't working I don't think VBA will be any better. There
must be
something different with the data for VLOOKUP not to work. I would check
the
Tools - options - Calculation menu to see what the number of Iterations
is
set to. If the iteration n umber is set too low or you are set to manual
calculation this may be the cause of the problem.

"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