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

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.

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

Hi Joel.

Am Sun, 23 Mar 2014 02:57:09 -0700 (PDT) schrieb Joel Callahan:

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.

to flag the matches:

Sub Flag()
Dim arrEx As Variant
Dim LRow As Long
Dim rngC As Range
Dim i As Long

arrEx = Sheets("Sheet2").Range("A1:A4")
With Sheets("Sheet1")
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
For Each rngC In Range("A2:A" & LRow)
For i = LBound(arrEx) To UBound(arrEx)
If InStr(rngC, arrEx(i, 1)) Then
rngC.Offset(, 2) = "True"
Exit For
End If
Next
Next
End With
End Sub

To delete the rows:
Sub DeleteRows()
Dim arrEx As Variant
Dim LRow As Long
Dim n As Long
Dim i As Long

Application.ScreenUpdating = False
arrEx = Sheets("Sheet2").Range("A1:A4")
With Sheets("Sheet1")
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
For n = LRow To 2 Step -1
For i = LBound(arrEx) To UBound(arrEx)
If InStr(.Cells(n, 1), arrEx(i, 1)) Then
Rows(n).Delete
Exit For
End If
Next
Next
End With
Application.ScreenUpdating = True
End Sub


Regards
Claus B.
--
Vista Ultimate SP2 / Windows7 SP1
Office 2007 Ultimate SP3 / 2010 Prodessional

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

OH. MY. GOODNESS!

THANK YOU do very much!

I can now save 3 hours a day on my reports!!!!

I truly appreciate your quick reply.

-Joel