Thank u very much,

I created another similar to your code

Sub match()

r1 = Worksheets("sheet1").Cells(Rows.Count, "A").End(xlUp).Row
r2 = Worksheets("sheet2").Cells(Rows.Count, "A").End(xlUp).Row

Set r3 = Worksheets("sheet1")
Worksheets("sheet2").Range("B2").Select
For a = 2 To r2
For i = 2 To r1
If Cells(a, "A") = r3.Cells(i, "A") Then
temp = r3.Cells(i, "B")
te = te & "," & temp
Else
End If
Next i
Cells(a, "B") = te
te = ""
Next a
End Sub

I was testing both codes with short rows say 100, at that time both
runs fast.

When I run on 100 THOUSAND rows, Your code completes in 40 Seconds.
My code completes in 1 Minute 05 Seconds.

Why is it like that any clue ?




On Mar 15, 12:24*am, Shane Devenshire
wrote:
Hi,

Here is some code

Adjust your ranges:

Sub Match()
* * Dim myCon As String
* * Dim myCell As Range
* * Dim cell As Range
* * For Each cell In Sheet2.Range("A2:A10")
* * * * myCon = ""
* * * * For Each myCell In Sheet1.Range("A1:A15")
* * * * * * If cell = myCell Then
* * * * * * * * If myCon = "" Then
* * * * * * * * * * myCon = myCell.Offset(0, 1)
* * * * * * * * Else
* * * * * * * * * * myCon = myCon & ", " & myCell.Offset(0, 1)
* * * * * * * * End If
* * * * * * End If
* * * * Next myCell
* * * * cell.Offset(0, 1) = myCon * *
* * Next cell
End Sub

NOTE VBA comparisons are case sensitive, so Monte carlo is not equal to
Monte Carlo

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

" wrote:
Hi,

I have two worksheets - Sheet1 and Sheet2

Sheet 1
Col A * * * * * * *Col B
Codes * * * * * * A/C Numbers
Pacific * * * * * * *145762
Atlantic * * * * * * 345782
Monte carlo * * * 478562
Pacific * * * * * * * 785

Sheet 2
Col A
Codes ( unique )
Pacific
Atlantic
Monte Carlo

I am using Excel 2007,
I need to match Sheet 2 Col A data with Sheet1 Col A data,
if any match found i need result in Col B of Sheet 2. If there are
more than one then
the A/C numbers to be separated by comma ( eg., 145762,785)

Sheet 1 has Approximately 1.5 Lakh Row

Sheet 2 has only 10,000 rows.

Please help me guys....