use vba to compare values in columns on separate sheets

[mdavid]

Hi, need vba code to compare Sheet1 Col.A with Sheet2 Col.C. Sheet1 Col.A values are unique, Sheet2 Col.C has duplicate values.
When a match is found append value in Sheet2 Col.A to Sheet1 Col.D and separate values by comma.
Thanks for any help
David

 

[vletm]

mdavid
Would You upload a sample file?

 

[mdavid]

Hi, Thanks for your help - seriously.
I know someone will do this for me in the end, but in the mean time I succeeded to solve it myself - "teach a man to fish" and all that.
The columns are sorted, here's my solution, hope it helps someone.

Sub Compare_species_id()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim sTemp As String
Set ws1 = ThisWorkbook.Sheets("Species")
Set ws2 = ThisWorkbook.Sheets("Complaint post id")
Dim lRow1 As Long, lRow2 As Long, i As Long, j As Long
lRow1 = ThisWorkbook.Sheets("Species").Range("A" & Rows.Count).End(xlUp).Row
lRow2 = ThisWorkbook.Sheets("Complaint post id").Range("A" & Rows.Count).End(xlUp).Row
j = 2
For i = 2 To lRow1
    sTemp = ""
    Do While (ws1.Cells(i, 1).Value = ws2.Cells(j, 3).Value And j <= lRow2)
         If sTemp = "" Then
            sTemp = ws2.Cells(j, 1)
         Else
            sTemp = sTemp & ", " & ws2.Cells(j, 1)
          End If
          j = j + 1
     Loop
     ws1.Cells(i, 4).Value = sTemp
Next i
End Sub