@Fluff you helped me with this formula before and I was hoping you could explain each element so I can adjust it for other uses. For example I think I should be able to adjust the "If .exists(cl.Value) Then .Remove cl.Value" line to include ".removeduplicates" and then add code to paste the new data free from of duplicates to a new sheet. I'm also assuming I can expand the dictionary to include data from a range A:Z for example, yet use column M to check for duplicates.
Sub FilterData()
Dim cl As Range
With CreateObject("scripting.dictionary")
For Each cl In Range("B2", Range("B" & Rows.count).End(xlUp))
.Item(cl.Value) = Empty
Next cl
For Each cl In Sheets("sheet2").Range("A5:A40")
If .exists(cl.Value) Then .Remove cl.Value
Next cl
Range("B:B").AutoFilter 1, .keys, xlFilterValues
End With
End Sub
Sub FilterData()
Dim cl As Range
With CreateObject("scripting.dictionary")
For Each cl In Range("B2", Range("B" & Rows.count).End(xlUp))
.Item(cl.Value) = Empty
Next cl
For Each cl In Sheets("sheet2").Range("A5:A40")
If .exists(cl.Value) Then .Remove cl.Value
Next cl
Range("B:B").AutoFilter 1, .keys, xlFilterValues
End With
End Sub