Create List based on Cell Value

[Steve]

Hello. I'm trying to Scan column C for the word "Yes". If it finds
it, copy the contents of column R of the same row, and create a list
in Sheet2 one under the other.

Ideally, I would aslo love to have only unique values. Maybe that can
be done after the full list is created?

Thanks so much!!

 

[JW]

One way. This returns unique values and places them in the first
available row in column A of sheet 2.
Sub uniqueList()
Dim uVal As Collection, u As Variant
Set uVal = New Collection
For i = 2 To Cells(Rows.Count, 3).End(xlUp).Row
If UCase(Cells(i, 3).Text) = "YES" Then
On Error Resume Next
uVal.Add Cells(i, 18).Text, _
CStr(Cells(i, 18).Text)
On Error GoTo 0
End If
Next i
If uVal.Count = 0 Then Exit Sub
With Sheets("Sheet2")
For Each u In uVal
.Cells(.Rows.Count, 1).End(xlUp) _
.Offset(1, 0).Value = u
Next u
End With
Set uVal = Nothing
End Sub