Help! Text word search.

[Michelle]

I am using Excel 2003. I have a spreadsheet with one column and hundreds of
rows of text data (exported from an open ended survey question). I want to
be able to group common responses by searching for key words and then
grouping them together. So if column A has "all data." Column "B" may
contain responses containing the word "budget." Column "C" may contain
responses containing the word "HR."

I tried to use the autofilter (custom) function and then cut and paste into
another column, but that didn't work. I also read just about every question
on this string. I am afraid I am not a proficient Excel user.

HELP! If I can just find a way to highlight a particular search, I can cut
and paste myself Thanks for your help!

 

[Fred Smith]

You want a pivot table. They are easy to learn, and you will never regret
the time you spent finding out about them.

Debra Dalgleish has excellent information on Pivot Tables here:
http://www.contextures.com/xlfaqPivot.html

Regards,
Fred.

 

[Joel]

Ther only way to do this is with a macro. the code copies the responses from
sheet1 to sheet2. The macro macro prompts for a string (keyword) and copies
the cells from column A to the new sheet in a column. the macro also keep
ttracks of which responses where moved by putting a counter in column B of
the original worksheet. the macro sorts the orginal worksheet by the number
of times each response gets moved. I assumed some responses may fall into
more than one category.


Sub moveresponses()

Set Sourcesht = Sheets("Sheet1")
Set DestSht = Sheets("Sheet2")


With Sourcesht


Lastrow = .Range("A" & Rows.Count).End(xlUp).Row
If WorksheetFunction.CountA("B2:B" & Lastrow) > 0 Then
'insert header row
.Rows(1).Insert
.Range("B2:B" & Lastrow) = 0
.Range("B1") = "Times Moved"
.Range("A1") = "Answer"
Lastrow = Lastrow + 1
End If

Response = InputBox("Enter key word to response :")

'search for key work
Set c = .Columns("A").Find(What:=Response, _
LookIn:=xlValues, LookAt:=xlPart)
If c Is Nothing Then
MsgBox ("Did not find any response" & vbCrLf & _
"Exiting Macro")
Else
If DestSht.Range("A1") = "" Then
NewCol = 1
Else
LastCol = DestSht.Cells(1, Columns.Count).End(xlToLeft).Column
NewCol = LastCol + 1
End If

DestSht.Cells(1, NewCol) = Response
NewRow = 2

firstAddr = c.Address

Do
If c.Row > 1 Then
DestSht.Cells(NewRow, NewCol) = c.Value
c.Offset(0, 1) = c.Offset(0, 1) + 1
End If
Set c = .Columns("A").FindNext(after:=c)
x = c.Address
Loop While Not c Is Nothing And c.Address <> firstAddr
End If

.Range("A1:B" & Lastrow).Sort _
header:=xlYes, _
key1:=.Range("B1"), _
Order1:=xlAscending

End With



End Sub

 

[YESHWANT]

Hi Michelle,

Instead of cut+paste and/or copy+paste, I would like to suggest one more
simpler way of doing the same.

Just add column before or after column A, apply the desired filter and fill
the column with appropriate word/group of words and use filter based on that
column. I think this can help u in the long way.

learn - n - enjoy