Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
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! |
#3
|
|||
|
|||
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! |
#4
|
|||
|
|||
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! |
#5
|
|||
|
|||
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! |
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
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. |
#8
|
|||
|
|||
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. |
#9
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Improvements for text finding functions | Excel Discussion (Misc queries) | |||
Formulas dealing with text data | Excel Worksheet Functions | |||
Finding Specific Text in a Text String | Excel Worksheet Functions | |||
Finding the mode of text | Excel Discussion (Misc queries) | |||
Finding Partial Text in a Cell | Excel Worksheet Functions |