Matching !!



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


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.

Shane Devenshire

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


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

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")
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
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 ?

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads