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!

 

[excelpunk]

Can we use advance filter?

 

[mirabeau]

It shouldn't take very long at all to delete the 200 addresses in Column B from the 5000 addresses in Column A.

Probably likewise for Column C, although you don't make it clear whether your street names are all or just part of addresses in Col A. You did refer to exact matches between Col A and Col B addresses. But what about Col C? Exact matches or not??

Anyway, running this macro on a copy of your data. It should give in Col E the contents of Col A with the contents of Col B removed.

Sub deladresses()
Dim d As Object, na&, nb&, e
Set d = CreateObject("scripting.dictionary")
na = Cells(Rows.Count, "a").End(3).Row
For Each e In Range("A1").Resize(na).Value
    d(e) = 1
Next e
nb = Cells(Rows.Count, "b").End(3).Row
For Each e In Range("B1").Resize(nb).Value
    If d(e) = 1 Then d.Remove e
Next e
[e1].Resize(d.Count) = Application.Transpose(d.keys)
End Sub

 

[excelpunk]

Thank you for the reply. I really appreciate it. However I have some queries:

1. What if I have more than half a million rows filled with addresses in column A, more than 20000 addresses in column B and around 10000 different street names in column C. Will the speed decrease?

2. Column C has street address, so if in column A, if any of the records have that particular street address (Street address is just part of the string so it should not be a exact match) that should be deleted. E.g. If my address (In column A) reads as '10 downing lane, James park'. and I have '10 down lane' in column C then this particular address in column A should not be deleted because its 'Down' instead of 'Downing', however if its '10 downing lane' then that particular address in column A should be deleted. Please let me know.

 

[excelpunk]

The code works fine but there is a issue with the code:

1. Suppose out of 3000 addresses 10 addresses (out of 50 addresses) were matched, now the list in column E should contain (3000-10) addresses but it now shows (3000-10+40).. I dont know why!!!!

 

[mirabeau]

Large problems do generally take longer and the size you suggest is pretty large.

However, depending on the specs of your computer, I'd be surprised if it took more than two or three minutes. I've included a timer device so you can check this.

I made allowance for Column 3 in the revised version below. It should do as you request, but you should test it to check.

Sub deladresses2()
t = Timer
Dim d As Object, na&, nb&
Dim e, nc&, g, k, q()

Set d = CreateObject("scripting.dictionary")

na = Cells(Rows.Count, "a").End(3).Row
For Each e In Range("A1").Resize(na).Value
    d(e) = 1
Next e

nb = Cells(Rows.Count, "b").End(3).Row
For Each e In Range("B1").Resize(nb).Value
    If d.exists(e) Then d.Remove e
Next e

nc = Cells(Rows.Count, "c").End(3).Row
For Each g In Range("C1").Resize(nc).Value
    For Each e In d.keys
        If InStr(e, g) > 0 Then d.Remove e: Exit For
Next e, g

ReDim q(1 To d.Count, 1 To 1)
For Each e In d.keys: k = k + 1: q(k, 1) = e: Next e
[e1].Resize(d.Count) = q
MsgBox "Code took " & Format(Timer - t, "0.00") & " secs"
End Sub

 

[mirabeau]

The code works fine but there is a issue with the code:

1. Suppose out of 3000 addresses 10 addresses (out of 50 addresses) were matched, now the list in column E should contain (3000-10) addresses but it now shows (3000-10+40).. I dont know why!!!!

This shouldn't occur in the revised version, which does a bit of stuff somewhat differently.

But post back if the problem recurs.

 

[excelpunk]

Hi,

Thanks for the code. I tested it, for some reason I am not able to get the exact list. I am missing something here? or is there a prob in the code. In column C one of the entries is 'Suite 4' but when I copy this value and then do a 'Find' in column E (Where are the addresses are populated) I am still able to find address which contain Suite 4.

E.g. One of the address is "Suite 4, Hwy 301 Blvd" and in one of the rows in column C is "Suite 4", so in column E I should not see the above address because it has "Suite 4" but it does appear! Is there a problem in the code? Thanks for your patience.

 

[mirabeau]

Hi, I had a bit of a think about that and decided to do another code.

Try this on some test data and see if it does the job.

Sub testing()
Dim d As Object, na&, nb&, nc&
Dim e, q, f, k&, p&
Set d = CreateObject("scripting.dictionary")
d.comparemode = 1
na = Range("A" & Rows.Count).End(3).Row
nb = Range("B" & Rows.Count).End(3).Row
nc = Range("C" & Rows.Count).End(3).Row
For Each e In Range("B1").Resize(nb).Value
    d(e) = 1
Next e
q = Range("a1").Resize(na)
For Each e In q
    k = k + 1
    If d(e) = 1 Then q(k, 1) = Empty
    If Len(e) > 0 Then
    For Each f In Range("C1").Resize(nc).Value
        If InStr(e, f) > 0 Then q(k, 1) = Empty: Exit For
    Next f
    End If
Next e
For Each e In q
    If Len(e) > 0 Then p = p + 1: q(p, 1) = e
Next e
[e1].Resize(p) = q
End Sub

It aims to list in column E the list in Column A with the relevant criteria from cols B and C removed.

It also occurred to me that that case may be causing apparent errors. i.e. if there's a capital in say "Suite" in Col C but not in "suite" in Col A.

Is case sensitivity an issue? Can easily be done either way. The above code is the case-sensitive version.

 

[excelpunk]

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.