identify specific keywords (plural) in a column of text

[dominicp]

So i'm try to automate a process that I currently do using filters.

Here is the problem:

I have a list of text in a column (usually 700 or so entries).

I need to remove any entries that contain 1 of 42 keywords.

So I need a fast way to search each cell for each one of the 42 key words and flag the cell for deletion if contains anyone of the 42.

example
A
1 This field has keyword1
2 This field has keyword2
3 This field doesn't have any of the 42 keywords


somewhere else I would have a list of keywords in a column.

Thanks for the help.

 

[lenze]

Hi and welcome to the board!!!
Is the Keyword the ONLY thing in the cell, or can it contain other text??
lenze

 

[hiker95]

dominicp,

Sample data on Sheet1 before the macro:

Excel Workbook
	A	B
1	This field has keyword1
2	This field has keyword2
3	This field doesn't have any of the 42 keywords
4
Sheet1

And, sheet "Keywords":

Excel Workbook
	A
1	keyword1
2	keyword2
3	keyword3
4
Keywords

Sheet1 after the macro:

Excel Workbook
	A	B
1	This field has keyword1	Delete
2	This field has keyword2	Delete
3	This field doesn't have any of the 42 keywords
4
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel

Option Explicit
Option Base 1
Sub CKKeywords()
Dim c As Range, a As Long, b As Long
Dim MyKeys As Variant
Application.ScreenUpdating = False
MyKeys = Sheets("Keywords").Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
With Sheets("Sheet1")
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
  For a = LBound(MyKeys) To UBound(MyKeys)
    b = 0
    On Error Resume Next
    b = WorksheetFunction.Find(MyKeys(a, 1), c, 1)
    On Error GoTo 0
    If b > 0 Then
      c.Offset(, 1) = "Delete"
      Exit For
    End If
  Next a
Next c
End With
Application.ScreenUpdating = True
End Sub

Then run the "CKKeywords" macro.

 

[dominicp]

Hi lenze,

glad to be on the board..

The cells do contain more than just the keywords.

The entries are anywhere from 1 word to 5 words and can also contain punctuation.

hope that helps.
Thanks,
Dominic

 

[taigovinda]

Here's a made-to-order solution:

Excel Workbook
	A	B	C	D
1	list of identifiers		text string to search	search result
2	tai		steve knows what's best	steve
3	ryan		better ask lori for permission	lori
4	steve		go to marty for excel help	marty
5	marty		why does tai work so hard for this company?	tai
6	john		what exactly does ryan DO in ppt all day?	ryan
7	joe		process time + joe = process time / 2	joe
8	lori		john brought cookies	john
Sheet1

Excel 2003

Cell Formulas
Range	Formula
D2	=IF(ISERROR(LOOKUP(9.9999999E+307,SEARCH(search_list,C2),search_list)),"",LOOKUP(9.9999999E+307,SEARCH(search_list,C2),search_list))

Excel Workbook
Name	Refers To
search_list	=OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$A:$A),1)
Workbook Defined Names

Copy the formula down; you can use a named range, or substitute the range name in the formula with an absolute reference. Note that if there are multiple matches, the formula will return the *last* match in your search list - so you can sort your search list accordingly.

 

[lenze]

Dominic:
It appears you have the best of both worlds from Hiker and Tai. Your choice!!
Formula or Macro?

lenze

 

[dominicp]

thanks all..i will test both methods.

 

[dominicp]

Hiker...

tried your code but resulted in all fields being marked deleted...

any thoughts on what i could be doing wrong..

Sample of text i'm searching:
<table style="border-collapse: collapse; width: 198pt;" border="0" cellpadding="0" cellspacing="0" width="264"><col style="width: 198pt;" width="264"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 198pt;" height="20" width="264">Garrison Opportunity Fund LLC</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">Earth Wind & Fire Fund Ltd.</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">FX Bridge Technologies Corp</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">FX Bridge Technologies Corp</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">CHATA BIOSYSTEMS INC</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl64" style="height: 15pt;" height="20">Thornton Holding Company, Inc.</td> </tr> </tbody></table>
These start in cell A1 on Sheet1

Sample of keywords

<table style="border-collapse: collapse; width: 140pt;" border="0" cellpadding="0" cellspacing="0" width="186"><col style="width: 140pt;" width="186"> <tbody><tr style="height: 15pt;" height="20"> <td style="height: 15pt; width: 140pt;" height="20" width="186">Manager</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Holding</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">Fund</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">LLP</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">LP</td> </tr> <tr style="height: 15pt;" height="20"> <td style="height: 15pt;" height="20">L.P.</td> </tr> </tbody></table>
These start on cell A1 on sheet "Keywords"

 

[Domenic]

Maybe...

B1, copied down:

=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH(" "&Keywords!$A$1:$A$6&" "," "&A1&" "))),"Y","")

or

=ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH(" "&Keywords!$A$1:$A$6&" "," "&A1&" ")))+0

 

[hiker95]

dominicp,

Sample data before the macro:

Excel Workbook
	A	B
1	Garrison Opportunity Fund LLC
2	Earth Wind & Fire Fund Ltd.
3	FX Bridge Technologies Corp
4	FX Bridge Technologies Corp
5	CHATA BIOSYSTEMS INC
6	Thornton Holding Company, Inc.
7
Sheet1

Excel Workbook
	A
1	Manager
2	Holding
3	Fund
4	LLP
5	LP
6	L.P.
7
Keywords

After the macro:

Excel Workbook
	A	B
1	Garrison Opportunity Fund LLC	Delete
2	Earth Wind & Fire Fund Ltd.	Delete
3	FX Bridge Technologies Corp
4	FX Bridge Technologies Corp
5	CHATA BIOSYSTEMS INC
6	Thornton Holding Company, Inc.	Delete
7
Sheet1

I have modified the macro to account for the fact that all the trailing spaces in each data cell in your Sheet1 and sheet Keywords:


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel

Option Explicit
Option Base 1
Sub CKKeywords()
Dim c As Range, a As Long, b As Long
Dim MyKeys As Variant
Application.ScreenUpdating = False
MyKeys = Sheets("Keywords").Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
With Sheets("Sheet1")
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
  For a = LBound(MyKeys) To UBound(MyKeys)
    b = 0
    On Error Resume Next
    b = WorksheetFunction.Find(Trim(MyKeys(a, 1)), c, 1)
    On Error GoTo 0
    If b > 0 Then
      c.Offset(, 1) = "Delete"
      Exit For
    End If
  Next a
Next c
End With
Application.ScreenUpdating = True
End Sub

Then run the "CKKeywords" macro.