Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default 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.

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,872
Default 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
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default 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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy entire row from another sheet onto first sheet if a unique text matches Smeghead Excel Worksheet Functions 1 March 14th 13 05:00 AM
Import data from another sheet that matches to a class list? gmac70 Excel Worksheet Functions 4 October 5th 09 02:37 AM
Search a sheet for a date then delete the row, repeat until the lastrow of the table. Richhall[_2_] Excel Worksheet Functions 3 September 25th 09 01:57 PM
Copy from sheet to sheet when matches criteria jeremiah Excel Worksheet Functions 2 June 2nd 09 01:05 PM
If Info. from one Sheet matches, send it to another sheet Slava Excel Discussion (Misc queries) 1 June 21st 05 06:36 PM


All times are GMT +1. The time now is 01:00 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"