compare matching data in different sheets

[boots]

I have a file with two worksheets. Column C of Sheet1 has FILE IDs, as does Column D of Sheet2. I would like to find any matches and then copy all of the data from the matching row in Sheet1 to the end of the matching row on Sheet2.

[kadghar]

I have a file with two worksheets. Column C of Sheet1 has FILE IDs, as does Column D of Sheet2. I would like to find any matches and then copy all of the data from the matching row in Sheet1 to the end of the matching row on Sheet2.

Hi,
You can assign a range to a variant, so you can work it with FOR or DO. Try to get the info you want into an array and after that send it to Excel. If you use Excel during the process, it'll slow things down.

Something like this should do.

[CODE=vb]sub matchData()
dim a
dim b
dim c() as string
dim i as long
dim j as long
dim k as long
With Worksheets(1)
a = range(.cells(1, 1), .cells(1,1).end (-4121).end(-4161))
End With
With Workseets(2)
b = range(.cells(1, 4), .cells(1,4).end (-4121))
End With
ReDim Preserve c (1 to Ubound(a,2)-3)
For i = 1 To Ubound(a)
j = 1
Do
If b(j,1) = a(i,3) Then
For k = 1 To Ubound(c)
c(k) = a(i,k+3)
Next
With Worksheets(2)
Range(.Cells(j, 5),.Cells(j,4+u bound(c)) = c
End With
Exit Do
End If
j = j + 1
Loop Until j > Ubound(b)
Next
End Sub[/CODE]


HTH

[boots]

thanks, I will give that a shot.

[Tequilaman]

If you are having really much, I'ld assign the cell content into variables and let it mtch the values of them in the backround. Should be a little faster if you need it more often