Filterin of Cities in addresses

Hi,

I am a newbie in excel. My problem is how to match and filter the cities in a list of address in Column B. There's about 14,500 list of addresses in one column and I need to identify which city needs to be excluded from the list in Column A. This is because these cities are affected by Typhoon Yolanda. Please see attached.

I already tried vlookup but it doesn't provide the result I need.

Please help. I have a deadline on this.

Thanks.

Benj

Hi..

Try this..

The results are highlighted red in Sheet 1 and also copied to Sheet2..

Option Compare Text
Private Sub CommandButton1_Click()
    Dim r As Range
    Dim LR As Long
    Application.ScreenUpdating = False
    With CreateObject("VBScript.RegExp")
        .IgnoreCase = True
        .Pattern = "\b(Aklan|Antique|Capiz|Iloilo|Negros Occidental|Ormoc, Leyte|Coron, Palawan)\b"
        For Each r In Range(Cells(1, 2), Cells(Rows.Count, 2).End(xlUp))
            If .test(r.Value) = True Then
                LR = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row + 1
                Cells(r.Row, 1).EntireRow.Interior.ColorIndex = 3
                Cells(r.Row, 2).Copy Destination:=Sheets("Sheet2").Range("A" & LR)
            End If
        Next r
    End With
    Sheets("Sheet2").Columns.AutoFit
    Application.ScreenUpdating = True
End Sub

Originally Posted by apo

Hi..

Try this..

The results are highlighted red in Sheet 1 and also copied to Sheet2..

Option Compare Text
Private Sub CommandButton1_Click()
    Dim r As Range
    Dim LR As Long
    Application.ScreenUpdating = False
    With CreateObject("VBScript.RegExp")
        .IgnoreCase = True
        .Pattern = "\b(Aklan|Antique|Capiz|Iloilo|Negros Occidental|Ormoc, Leyte|Coron, Palawan)\b"
        For Each r In Range(Cells(1, 2), Cells(Rows.Count, 2).End(xlUp))
            If .test(r.Value) = True Then
                LR = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row + 1
                Cells(r.Row, 1).EntireRow.Interior.ColorIndex = 3
                Cells(r.Row, 2).Copy Destination:=Sheets("Sheet2").Range("A" & LR)
            End If
        Next r
    End With
    Sheets("Sheet2").Columns.AutoFit
    Application.ScreenUpdating = True
End Sub

Thank you so much for the help. This would easily help us identify how may of our clients need help.

Maybe something like this? It's big file sorry


Azumi

Originally Posted by azumi

Maybe something like this? It's big file sorry


Azumi

It helps a lot. Thank you for your quick reply. I appreciate it. this could help us easily identify the areas affected by typhoon and sort out the list of clients need help.

Hi Azumi..

When i ran your filter for the City of "AKLAN".. 2 out of the first 8 results don't seem correct..

It looks like it has picked up the string "AKLAN" within other words..

POBLACION BANGA MALAY WESTERN VISAYAS AKLAN 5608
880 NEW BUSWANG KALIBO AKLAN
DONGON WEST NUMANCIA WESTERN VISAYAS AKLAN 5604
203 LOWER KALAKLAN RD. OLONGAPO ZAMBALES
131 YAP MANOR POBLACION BANGA AKLAN
SCUBA CHAMP STATION 1 BALABAG MALAY AKLAN
LGU MALAY BALABAG MALAY WESTERN VISAYAS AKLAN 560
SA GEMINI ST UPPER KALAKLAN OLONGAPO ZAMBALES

I also noticed that when doing a Filter on the city of "Antique".. the following result came:
162 RIZAL ST POBLACION ANTIQUERA CENTRAL VISAYAS

Having said that.. I like the way you have set it out..

Yes sure, happy it works for you, and nice feedback

Regards
Azumi