Need to search one column for multiple text

[dbakernc]

Basically I have the need to search for specific text (ex. cpu) in a text string of one column (D). I am using Excel 2011 on a mac. I think I can use VLOOKUP, but am not sure. Also I need to search for multiple text. For example I need to filter on cpu:

A B C D
ID User Component Description
C123 Jeff cpu The cpu is broken
C235 Kim mem memory is not working
C345 Bob kernel The cpu is damaged


Currently I am able to use an auto filter to sort the column C on cpu but I also need to sort column D on cpu and the autofilter is not working. I figure autofilter only works on one word. I need all of the rows that have "cpu" or whatever other text value that I need to only be displayed.

Range("$C$1:$C$5000").AutoFilter Field:=1, Criteria1:=Array( _
"cpu", "crash", "Segmentation-fault"), Operator:= _
xlFilterValues

What I need to do is sort the row that has the specific text such as cpu. Right now I have a macro reading the data from one file and sorting it in another. I just can't figure out how to search Column D for the same text ex. cpu.

Sub BugSort()
Workbooks.OpenText Filename:="report.xlsx"
Range("A1:I500").Select
Selection.Copy
Workbooks.Open "Bug-Sort.xlsx"
ActiveWorkbook.Sheets.Add
Range("A1").Formula = "Availability"


Range("A3").Select
ActiveSheet.Paste


Range("$C$1:$C$5000").AutoFilter Field:=1, Criteria1:=Array( _
"crash", "Segmentation-fault", "High-cpu", _
"coredump", "'shell-execution-failed", "switchover"), Operator:= _
xlFilterValues
Columns("A:I").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("C2:C3000") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("A1:H5000") 'may want to code this so it scopes to diff rows and columns
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply


End With

End Sub

 

[MikeWx]

How many other values are you looking for? Have you looked at using Advanced Filter to find your values in Column C or in Column D?

 

[dbakernc]

The Advance Filter is working for column C, but not Column D. I'm guessing because I'm looking for one or two words out of a sentence. I'm looking for several words such as listed "cpu" "memory" "segmentation fault"

Debra

 

[shg]

Automation aside for the moment, you could list the words of interest across the header row and use a formula:

	A​	B​	C​	D​	E​	F​	G​
1​	ID​	User​	Description​	cpu​	memory​	​	​
2​	C123	Jeff	The cpu is broken	1​	0​		D2 and across and down: =--ISNUMBER(SEARCH(" " & D$1 & " ", " " & $C2 & " "))
3​	C235	Kim	memory is not working	0​	1​
4​	C345	Bob	The cpu is damaged	1​	0​

 

[dbakernc]

Ok. Let me think about this. It may work for me. I will be searching for around 50 -75 different words or phrases. I still think it may work. It also solves the problem of making sure all items are accounted for.

 

[shg]

You're welcome.

 

[dbakernc]

shg - Sorry I should have said thanks! I was born in Texas by the way - love the flag

Debra

 

[dbakernc]

A coder I work with helped me on this. I knew a for loop was needed.

Sub BugScriptSample()

Dim sPath As String
sPath = ThisWorkbook.Path
If Right(sPath, 1) <> "\" Then sPath = sPath & "\"
Workbooks.OpenText Filename:="report.xlsx"

Sheets(1).Select
Range("A1").Select
On Error Resume Next
Set mylastcell = Cells(1, 1).SpecialCells(xlLastCell)
mylastcelladd = Cells(mylastcell.Row, mylastcell.Column).Address
myrange = "A1:" & mylastcelladd
Range(myrange).Select
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Paste

NumRows = Range("A2", Range("A2").End(xlDown)).Rows.Count
For i = NumRows To 2 Step -1
iVal = WorksheetFunction.CountIf(Cells(i, 9), "*crAsh*") + _
WorksheetFunction.CountIf(Cells(i, 9), "*LED*") + _
WorksheetFunction.CountIf(Cells(i, 9), "*CDP*")
If iVal = 0 Then
Cells(i, 9).EntireRow.Delete
End If
Next i

End Sub