VBA compare 2 ranges and return values which occur in one and not the other

[danno79]

I have 2 ranges "SheetNames" starting in cell D7 - generated by the code below, and "OrgNames" starting in cell E7, that is input by a user . I want to generate a list starting in cell F7 containing values which are in OrgNames but not in SheetNames. There will always be values in SheetNames that do not occur in OrgNames. I'm sure this is probably quite simple to achieve but i'm stumped- any suggestions?....

Sub find_sheet_names()

Dim x As Integer
For x = 1 To Worksheets.Count
Cells(x + 7, 4).Value = Worksheets(x).Name
Next x

End Sub

 

[texasalynn]

a formula method would be to do a vlookup and wherever there are #N/A are the unmatched

 

[Datsmart]

This will list Unique items in column D to column F:

Sub CopyUnique2ColF()
'Lists duplicate items between columns D and E to F
    Range("F7:F65536").ClearContents
    er = 7
    For i = 7 To Range("D65536").End(xlUp).Row
         If Application.WorksheetFunction.CountIf(Range("E:E"), Range("D" & i)) = 0 Then
              Range("F" & er) = Range("D" & i)
              er = er + 1
         End If
    Next i
End Sub

 

[danno79]

John- thanks this is perfect