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



"Michelle" wrote:

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!