Need Macro that returns a "true" value in a new column for each row that contains a search term

[DLR1981]

Hi Everyone,

I have a spreadsheet that contains 6 columns. One of the columns contains system descriptions, which may run a paragraph or two in length. I would like to create a Macro that searches the system description column for a key word and returns "true" in a new column.

For example, if I search for "content management," I would like a macro that returns in a new column on the same spreadsheet a value of "true" in the row(s) that contains the term "content management" in the system description column (column H). Ideally, I'd like to be able to search for multiple terms on the same spreadsheet, such that if I search for "content management," all "true" values in return in column I, and if I search for "case management" all true values are returned in column J, and so on...

Does anyone know how to do this?

 

[Rick Rothstein]

You didn't tell us everything about your layout, so I had to guess...

Assuming your data starts on Row 2, put your search terms on Row 1 (so "content management" would be placed in cell I1 and "case management" in cell J1 and so on) and put this formula in cell I2 and copy it across for as many search terms as you have on Row 1 and then copy all those formulas down to the end of your data...

=IF(COUNTIF($H2,"*"&I$1&"*"),TRUE,"")

 

[DLR1981]

Thanks, Rick! This is great!

Any idea how I could write a macro to copy rows that contain "true" results in the search term column to a new worksheet?

Thanks again!

 

[Rick Rothstein]

Any idea how I could write a macro to copy rows that contain "true" results in the search term column to a new worksheet?

Assuming your data is not on Sheet2 and that the output is to go to Sheet2, run this macro from the sheet with your data on it...

[table="width: 500"]
[tr]
	[td]Sub CopyWhenTRUE()
  Dim C As Long, LastCol As Long, WSout As Worksheet
  Set WSout = Sheets("Sheet2")
  LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
  For C = 9 To LastCol
    WSout.Cells(1, C - 8).Value = Cells(1, C).Value
    Intersect(Columns(C).SpecialCells(xlFormulas, xlLogical).EntireRow, Columns("H")).Copy WSout.Cells(2, C - 8)
  Next
End Sub[/td]
[/tr]
[/table]

The first row on the output sheet will contain the search words and under them will be listed the values from Column H where the search word column returned TRUE.

 

[DLR1981]

Hi Rick,

Thanks again for all of your help. I need to modify the above macro to pull in the entire row that contains the value of True for the search term I am looking for. In other words, I need to be able to pull in the data from rows A through H if H contains the search term I am looking for.

I appreciate your assistance, and I'm sorry to keep bugging you!

 

[Rick Rothstein]

Thanks again for all of your help. I need to modify the above macro to pull in the entire row that contains the value of True for the search term I am looking for. In other words, I need to be able to pull in the data from rows A through H if H contains the search term I am looking for.

Then you are going to have to tell us where the output should go to and how it should be laid out (I believe the way I set up my code's output, the lead-in columns would conflict with each other because the blank rows were squeezed out of the listings).

 

[DLR1981]

Hi Rick,


Thanks again for your help.


Here is a quick run down of what I need:


- I have a table with eight columns (Columns A thru H). I would like to be able to perform a key word search in the table. If the key word appears in one of the columns, I would like Excel to copy into a new spreadsheet the entire row(s) where the key word is located.
- For example, if I search for "content management," I would like Excel to copy into Sheet2 every row/columns that contains the term "content management."
- For every new term I serch for, I would like Excel to post the results in a new spreadsheet (Sheet3, Sheet4, and so on...).


The result set on each new spreadsheet should include all columns contained in the sheet I am searching within.


Does that make sense?

 

[Rick Rothstein]

- I have a table with eight columns (Columns A thru H). I would like to be able to perform a key word search in the table. If the key word appears in one of the columns, I would like Excel to copy into a new spreadsheet the entire row(s) where the key word is located.

The text I highlighted in red appears to be a major change from what we were doing earlier. In our earlier conversations, we appeared to only be searching Column H for the search word, but the above highlighted text makes it seem like the search word can be in any of the 8 columns.

So where should the search take place... only in Column H or in all of the columns A thru H?

Whatever your answer is to the above question, is all of your data in the search column or columns constants (that is, there are no formulas in the search column or columns, correct)?

 

[DLR1981]

The search should take place in all columns (A thru H, as opposed to just H). The table that is being searched does not contain any formulas. In other words, the search wouldn't consider the "CountIf" statement.

I realize the problem I am trying to solve is different than the initial issue I introduced. Essentially what I am trying to do is automate a process for returning entire rows based on the results of a key word search.

Thank you!! I apologize for any frustration I may have caused you!

 

[Rick Rothstein]

See if you can use this macro... it will ask you for the word or phrase that you want to find and the create a new worksheet with the word or phrase as its name (or it will simply add to the bottom of any text already on a sheet named with the search word or phrase if it already exists)... if the word or phrase has any illegal characters for a sheet name, those characters will be replaced by underline characters.

[table="width: 500"]
[tr]
	[td]Sub FindPhrases()
  Dim R As Long, C As Long, X As Long, Z As Long, Phrase As String
  Dim DataSheet As Worksheet, WS As Worksheet, Data As Variant, Result As Variant
  Set DataSheet = Sheets("Sheet1")
  Phrase = InputBox("What word or phrase do you want to search for?")
  If Len(Phrase) Then
    Data = DataSheet.Range("A1:H" & DataSheet.Columns("A:H").Find("*", , xlValues, , xlRows, xlPrevious).Row)
    ReDim Result(1 To UBound(Data), 1 To 8)
    For R = 1 To UBound(Data)
      For C = 1 To 8
        If InStr(1, Data(R, C), Phrase, vbTextCompare) Then
          Z = Z + 1
          For X = 1 To 8
            Result(Z, X) = Data(R, X)
          Next
          Exit For
        End If
      Next
    Next
  End If
  Phrase = Left(Phrase, 31)
  For X = 1 To Len(Phrase)
    If InStr("\/*?:[]", Mid(Phrase, X, 1)) Then Mid(Phrase, X) = "_"
  Next
  On Error GoTo NoSuchSheet
  Sheets(Phrase).Activate
  On Error GoTo 0
  Cells(Cells.Find("*", , xlValues, , xlRows, xlPrevious).Row + 1, "A").Resize(UBound(Result), 8) = Result
  DataSheet.Activate
  Exit Sub
NoSuchSheet:
  Sheets.Add After:=Sheets(DataSheet.Index)
  ActiveSheet.Name = Phrase
  DataSheet.Rows(1).Copy Range("A1")
  Resume
End Sub[/td]
[/tr]
[/table]