Hi all,
I have used the following code to lookup values for ID in
colB and for that value gives all the values of the WRnbr in colC ,
but I need the code to return only distinct values for the WR#,please
advise of the modifications.ANy help would be appreciated:

Option Explicit
Sub FindWRNbr()
Dim ws1 As Worksheet, ws2 As Worksheet, a As Long, SPMID As String
Dim c As Range, firstaddress As String, Hold As String
Set ws1 = Sheets("SPM_id_view")
Set ws2 = Sheets("Dragoni_owned")
Application.ScreenUpdating = False
With ws1
For a = 2 To .Cells(Rows.Count, 2).End(xlUp).Row Step 1
Hold = ""
SPMID = .Cells(a, 2).Value
With ws2.Columns(34)

Set c = .Find(SPMID, LookIn:=xlValues, LookAt:=xlWhole)
If Not c Is Nothing Then
firstaddress = c.Address
Do
Hold = Hold & c.Offset(, -33).Value & "#"
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstaddress
End If
End With
If Right(Hold, 1) = "#" Then
Hold = Left(Hold, Len(Hold) - 1)
ws1.Cells(a, 3) = Hold
End If
Next a
End With
Application.ScreenUpdating = True
ws1.Select
End Sub