Finding text within text
 

Hello,

I have two columns with text. I'd like take those rows that contain
multiple "keywords" and put them on a separate sheet. For example, a few of
the keywords would be: gift, check, personal, error and if that cell
contained any of those keywords then it would be put on a separate sheet. Is
this possible?

Thank you!

Hi!

Need a better explanation. Some examples would also help.

What does "I have two columns with text" have to do with it? Does that mean
1 column may contain a keyword and the 2nd column may also contain a keyword
and in those rows where both columns have keywords you want those 2 entries
extracted to another location?

Biff

"Cathy Landry" wrote in message
...
Hello,

I have two columns with text. I'd like take those rows that contain
multiple "keywords" and put them on a separate sheet. For example, a few
of
the keywords would be: gift, check, personal, error and if that cell
contained any of those keywords then it would be put on a separate sheet.
Is
this possible?

Thank you!

Hi Biff,

You're right, I was not very explicit in what I need. I'm working with a
spreadsheet that has data in col a through aa. columns r/x contain "notes"
regarding corp/purchase card expenditures. I need to look for certain words
within those two columns and copy the entire row/s to another sheet. We want
to look at those individuals that are making purchases that fall outside of
our company's policies and procedures. A few of the keywords would be
"gift", "personal", "party", "error", "check" etc.

Thank you!
Cathy

"Biff" wrote:

Hi!

Need a better explanation. Some examples would also help.

What does "I have two columns with text" have to do with it? Does that mean
1 column may contain a keyword and the 2nd column may also contain a keyword
and in those rows where both columns have keywords you want those 2 entries
extracted to another location?

Biff

"Cathy Landry" wrote in message
...
Hello,

I have two columns with text. I'd like take those rows that contain
multiple "keywords" and put them on a separate sheet. For example, a few
of
the keywords would be: gift, check, personal, error and if that cell
contained any of those keywords then it would be put on a separate sheet.
Is
this possible?

Thank you!

Hi!

Since you want the entire row of data "copied" to another sheet I think you
should use filters. Based on your description this would require too many
formulas to be efficient.

Take a look he

http://contextures.com/tiptech.html

There you'll find just about everything you always wanted to know about
filters!

Biff

"Cathy Landry" wrote in message
...
Hi Biff,

You're right, I was not very explicit in what I need. I'm working with a
spreadsheet that has data in col a through aa. columns r/x contain
"notes"
regarding corp/purchase card expenditures. I need to look for certain
words
within those two columns and copy the entire row/s to another sheet. We
want
to look at those individuals that are making purchases that fall outside
of
our company's policies and procedures. A few of the keywords would be
"gift", "personal", "party", "error", "check" etc.

Thank you!
Cathy

"Biff" wrote:

Hi!

Need a better explanation. Some examples would also help.

What does "I have two columns with text" have to do with it? Does that
mean
1 column may contain a keyword and the 2nd column may also contain a
keyword
and in those rows where both columns have keywords you want those 2
entries
extracted to another location?

Biff

"Cathy Landry" wrote in message
...
Hello,

I have two columns with text. I'd like take those rows that contain
multiple "keywords" and put them on a separate sheet. For example, a
few
of
the keywords would be: gift, check, personal, error and if that cell
contained any of those keywords then it would be put on a separate
sheet.
Is
this possible?

Thank you!

Hi Biff, thank you for your input.

Here's the code that I'm using courtesy of Jindon. For anyone else that's
looking to extract text within text this is perfect.

Sub FindAndCopy()
Dim r As Range, i As Long
Dim Phrase
Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")

Phrase = Array("gift", "check", "wrong")
With ws1

For Each r In Intersect(.Range("r:x"), .UsedRange)
For i = LBound(Phrase) To UBound(Phrase)
If InStr(1, r, Phrase(i), vbTextCompare) 0 Then
r.EntireRow.Copy _
ws2.Range("A65536").End(xlUp).Offset(1, 0)
Exit For
End If
Next
Next
End With
MsgBox "done"
Application.CutCopyMode = False
End Sub

"Biff" wrote:

Hi!

Since you want the entire row of data "copied" to another sheet I think you
should use filters. Based on your description this would require too many
formulas to be efficient.

Take a look he

http://contextures.com/tiptech.html

There you'll find just about everything you always wanted to know about
filters!

Biff

"Cathy Landry" wrote in message
...
Hi Biff,

You're right, I was not very explicit in what I need. I'm working with a
spreadsheet that has data in col a through aa. columns r/x contain
"notes"
regarding corp/purchase card expenditures. I need to look for certain
words
within those two columns and copy the entire row/s to another sheet. We
want
to look at those individuals that are making purchases that fall outside
of
our company's policies and procedures. A few of the keywords would be
"gift", "personal", "party", "error", "check" etc.

Thank you!
Cathy

"Biff" wrote:

Hi!

Need a better explanation. Some examples would also help.

What does "I have two columns with text" have to do with it? Does that
mean
1 column may contain a keyword and the 2nd column may also contain a
keyword
and in those rows where both columns have keywords you want those 2
entries
extracted to another location?

Biff

"Cathy Landry" wrote in message
...
Hello,

I have two columns with text. I'd like take those rows that contain
multiple "keywords" and put them on a separate sheet. For example, a
few
of
the keywords would be: gift, check, personal, error and if that cell
contained any of those keywords then it would be put on a separate
sheet.
Is
this possible?

Thank you!

Cathy Landry wrote...
....
Here's the code that I'm using courtesy of Jindon. For anyone else that's
looking to extract text within text this is perfect.

Sub FindAndCopy()
Dim r As Range, i As Long
Dim Phrase
Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")

Phrase = Array("gift", "check", "wrong")
With ws1

For Each r In Intersect(.Range("r:x"), .UsedRange)

This uses all columns between columns R and X, inclusive. Your previous
follow-up made it seem you wanted to look through only columns R and X.

For i = LBound(Phrase) To UBound(Phrase)
If InStr(1, r, Phrase(i), vbTextCompare) 0 Then
r.EntireRow.Copy _
ws2.Range("A65536").End(xlUp).Offset(1, 0)
Exit For
End If
Next
Next
End With
MsgBox "done"
Application.CutCopyMode = False
End Sub
....

You could use a macro for this, but it'd be simpler to do this with an
advanced filter. All columns in your original table would need field
names in the top row. You'd then need a criteria range with the field
names for columns R and X in another range and the words to match in
separate rows as follows.


Col_R_Field Col_X_Field
*gift*
*check*
*wrong*
*gift*
*check*
*wrong*


Filter in place, copy the filtered rows, and paste into another
worksheet.

Hi Harlan,

I did want only r & x, but then realized that if I needed to add/delete a
column in between them I'd have to adjust the code each time. By putting in
the range, it is working much better. Thank you very much to all that have
replied! Cathy

"Harlan Grove" wrote:

Cathy Landry wrote...
....
Here's the code that I'm using courtesy of Jindon. For anyone else that's
looking to extract text within text this is perfect.

Sub FindAndCopy()
Dim r As Range, i As Long
Dim Phrase
Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")

Phrase = Array("gift", "check", "wrong")
With ws1

For Each r In Intersect(.Range("r:x"), .UsedRange)

This uses all columns between columns R and X, inclusive. Your previous
follow-up made it seem you wanted to look through only columns R and X.

For i = LBound(Phrase) To UBound(Phrase)
If InStr(1, r, Phrase(i), vbTextCompare) 0 Then
r.EntireRow.Copy _
ws2.Range("A65536").End(xlUp).Offset(1, 0)
Exit For
End If
Next
Next
End With
MsgBox "done"
Application.CutCopyMode = False
End Sub
....

You could use a macro for this, but it'd be simpler to do this with an
advanced filter. All columns in your original table would need field
names in the top row. You'd then need a criteria range with the field
names for columns R and X in another range and the words to match in
separate rows as follows.


Col_R_Field Col_X_Field
*gift*
*check*
*wrong*
*gift*
*check*
*wrong*


Filter in place, copy the filtered rows, and paste into another
worksheet.

Cathy Landry wrote...
I did want only r & x, but then realized that if I needed to add/delete a
column in between them I'd have to adjust the code each time. By putting in
the range, it is working much better. Thank you very much to all that have
replied! Cathy
....

The advantage to using an advanced filter is that it uses field names
rather than column addresses. Add or remove as many columns as you want
as long as you keep the columns that were originally R and X. Wherever
they are, they'd retain their column labels/field names.

As for the macro, if you inserted a column to the left of X, then the
column that had been X would no longer be between columns R and X
inclusive. OTOH, if you deleted a column to the left of R, then the
column that had been R would no longer be between columns R and X. Your
rationale for using the code as-is is wrong. It won't help you if you
insert of delete columns.

Hi Harlan,

You could be right and the more I become familiar with Excel I may indeed
find that using advanced filters is the way to go. The spreadsheet I am
setting up will be a template for an expense management report to be copied
into on a monthly basis for an end user to analyze corp card usage and
possible mis-use. Unlike my other projects where I had access to the tables
to run sql scripts off of, this report is generated through a web based exp.
mngmnt tool which contain only "canned" reports. As you can imagine, having
them build a custom report is quite costly.

Thank you again....Cathy


"Harlan Grove" wrote:

Cathy Landry wrote...
I did want only r & x, but then realized that if I needed to add/delete a
column in between them I'd have to adjust the code each time. By putting in
the range, it is working much better. Thank you very much to all that have
replied! Cathy
....

The advantage to using an advanced filter is that it uses field names
rather than column addresses. Add or remove as many columns as you want
as long as you keep the columns that were originally R and X. Wherever
they are, they'd retain their column labels/field names.

As for the macro, if you inserted a column to the left of X, then the
column that had been X would no longer be between columns R and X
inclusive. OTOH, if you deleted a column to the left of R, then the
column that had been R would no longer be between columns R and X. Your
rationale for using the code as-is is wrong. It won't help you if you
insert of delete columns.