Matching !!

[fi.or.jp.de]

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

 

[Shane Devenshire]

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

 

[fi.or.jp.de]

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

 

[Shane Devenshire]

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

 

[fi.or.jp.de]

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 ?