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....

Hi,

You are going to need to write code for this. Also, what is the maximun
number of matches that you could find for 1 code? If it is too many you
won't be able to enter it in a cell.

and now to really show my ignorance - what is Lakh Row?



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....

Hi Shane,

The maximum number of matches for one code would be 3.

Lakh rows is equal 100 thousand rows

Appreciate your time.

Looking for positive response.

Thanks in advance



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

You are going to need to write code for this. *Also, what is the maximun
number of matches that you could find for 1 code? *If it is too many you
won't be able to enter it in a cell. *

and now to really show my ignorance - what is Lakh Row?

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....

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....

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....