How to filter arrays

[excelpunk]

Hello!

I am a decent excel user (but not good at macros). Here is the issue:
I have a list of 5000 addresses in column A, In column B there are 200 addresses which are to be removed from column A (It has to be a exact match) and also in column C I have name of the streets (50 entries), i also want to delete all the addresses which have the street names same as in column C. This list will be updated and I need a very quick way to get the list, I guess the standard way of doing it is declaring three arrays and then comparing but It takes a lot of time (I can have a power nap!). Any help in this regard will be greatly appreciated!

 

[mirabeau]

Wow! Excellent!

The code does exactly what I wanted it to do. I really appreciate your time. I only have a last request to you. Can you please remove the case sensitive thing.. I dont think including case sensitivity is a good idea because 'Suit' or 'suit' would mean the same, I would be extremely grateful if you can remove the case sensitivity. Rest, the code is simply perfect.

Not sure what you mean.

Do you want 'Suit' and 'suit' to be treated as the same or do you want them treated as different?

The above code treats them as different. Do you want it modified, or not?

 

[excelpunk]

I want to be treated them as same..

 

[mirabeau]

OK.

Just make the change in red in the relevant part of the last code

....
If Len(e) > 0 Then
    For Each f In Range("C1").Resize(nc).Value
        If InStr(UCase(e), UCase(f)) > 0 Then q(k, 1) = Empty: Exit For
    Next f
    End If
....