Flag or Delete a line from Sheet 1 if a wildcard search matches from an Exclude list of words in sheet 2

Sandmanninja · Mar 23, 2014

SITUATION:
I am using Excel 2007 on a Windows 7 Professional environment.
I have a list of about 30,000 URLS (from a proxy log)

Desired Results:
I need to (somehow - I don't care how) search through the column of data on sheet 1 and compare it to a list of Exclude Words on sheet 2.
The sheet 2 has about 15-20 words that, if ANY wildcard matches are TRUE, then either the line with the match on sheet 1 is deleted or a certain value (TRUE? KILL?) is entered (automatically) into a cell on that line.

SHEET 1:
URL Browse Time
FXP://corp.icsfl.com 0:03:00
HXXP://9msn.com.au 5:45:00
HXXP://a.rad.msn.com 5:42:00
HXXP://a.scorecardresearch.com 0:48:00
HXXP://ad.afy11.net 0:03:00
HXXP://ad.au.doubleclick.net 0:36:00
HXXP://ad.doubleclick.net 1:57:00
HXXP://ad.turn.com 0:09:00
HXXP://ad.yieldmanager.com 0:12:00
HXXP://ad-ace.doubleclick.net 0:42:00
HXXP://adadvisor.net 0:12:00
HXXP://ad-emea.doubleclick.net 0:15:00
HXXP://adfarm.mediaplex.com 0:03:00
HXXP://ads.rtbidder.net 0:03:00
HXXP://adx.adnxs.com 0:03:00
HXXP://aidps.atdmt.com 0:03:00
HXXP://ajax.aspnetcdn.com 0:03:00
HXXP://ajax.googleapis.com 0:03:00
HXXP://ajax.microsoft.com 0:03:00
HXXP://amch.questionmarket.com 0:03:00
HXXP://api.bing.com 5:42:00
HXXP://api.parsely.com 0:03:00
HXXP://apis.google.com 0:06:00
HXXP://apnstatic.ask.com 0:03:00
(just to list a few of the 30,000 lines)

SHEET 2 (exclude list):
google
doubleclick
mediaplex
bing

So if *google* is within ANY line of the sheet 1, it will either delete the entire line OR have some flag set in a nearby cell.

I don't care if the execution is efficient or not. I have to do a report every month and I'm doing this BY HAND.
(We're monitoring web usage in a school and need to exclude the irrelevant URLs)
Please, please, oh god please help me.

I've played with vlookup, advance filter, and no matter what I try, I cannot get it quite right.
The closest was with a vlookup but it reported far too many false positives and then just skipped what should have been matches.

I thank you for your assistance.

tlowry · Mar 23, 2014

Welcome to MrExcel

Here is one approach.
Try it on a small set of data first

Code:

Sub main()
    Const DataSheet = "Sheet1"   'Name of sheet with data
    Const ExluSheet = "Sheet2"   'Name of sheet with words to exclude
    Dim wd, c, firstaddress
    Dim lrData: lrData = Sheets(DataSheet).Cells(Sheets(DataSheet).Rows.Count, 1).End(xlUp).Row
    Dim lrExlu: lrExlu = Sheets(ExluSheet).Cells(Sheets(ExluSheet).Rows.Count, 1).End(xlUp).Row
    For Each wd In Worksheets(ExluSheet).Range("A1:A" & lrExlu)
        With Worksheets(DataSheet).Range("a1:a" & lrData)
            Set c = .Find(wd, LookIn:=xlValues, LookAt:=xlPart)
            If Not c Is Nothing Then
                firstaddress = c.Address
                Do
                    Sheets(DataSheet).Cells(c.Row, 2) = "#N/A"
                    Sheets(DataSheet).Cells(c.Row, 3) = wd
                    Set c = .FindNext(c)
                Loop While Not c Is Nothing And c.Address <> firstaddress
            End If
        End With
    Next
End Sub

Sandmanninja · Mar 23, 2014

Thank you!
I'll try this at work in about 2 hours and let you know!
Thanks again for the quick response

shg · Mar 23, 2014

Maybe just a formula?

[Table="width:, class:grid"][tr][td] [/td][td]

A

[/td][td]

B

[/td][td]

C

[/td][td]

D

[/td][/tr]
[tr][td]

1

[/td][td]

Exclude

[/td][td]

[/td][/tr]

[tr][td]

2

[/td][td][/td][td][/td][td]google[/td][td][/td][/tr]

[tr][td]

3

[/td][td][/td][td][/td][td]doubleclick[/td][td][/td][/tr]

[tr][td]

4

[/td][td][/td][td][/td][td]mediaplex[/td][td][/td][/tr]

[tr][td]

5

[/td][td][/td][td][/td][td]bing[/td][td][/td][/tr]

[tr][td]

6

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

7

[/td][td]

URL

[/td][td]

Time

[/td][td]

Exclude?

[/td][td][/td][/tr]

[tr][td]

8

[/td][td]FXP://corp.icsfl.com[/td][td]