Macro to gather comments

[Guest]

Can I use a macro to gather comments from a survey. I have a survey that
pulls the names in column A and the coresponding comments in column H. I have
tried V & H lookups but they only work for the first entry. I may have
anywhere from 0 to 15 comments per name. Any ideas?

 

[Otto Moehrbach]

Scott
Your question is much too general. Provide specifics about what you
have. Such as "I have this in this cell/row/column." "I have that in this
other cell/row/column." "I want to do this and this and this". What do you
want to do when you say "gather comments"? HTH Otto

 

[Guest]

The name of the individual will be in column A (there will be multiple names
as well as multiple entries for each name.) Columnn H is the column that has
the comments entered into it. I need to pull each comment for the same name
onto another spreadsheet that is a summary of the entire survey. I cannot
find a way besides sutting all of the comments into the first cell of column
H for each different name that appears. I hope that is enough information. If
you would like you can email me at (e-mail address removed) and I will
send you the sheet so you can see what I am doing.

 

[Guest]

You can use a pivot table.
Data>PivotTable and PivotChart Wizard follow the steps in the wizard. Once
you get to the layout screen drag the Name field button to the Row area, then
drag the Comments field button to the Row Area, and finally drag the Comments
button to the Data area and perform a count on the comments. Do not be
confused by the fact that you will have the comments field button in two
spots. The Row area and the Data Area. Use the wizard to guide you.

 

[Otto Moehrbach]

Scott
You can use Autofilter for this. Let's call the sheet that has the
comments, the first sheet, and the other sheet the second sheet.
Copy all the names in the first sheet and paste them into Column A of
the second sheet. Delete all the rows that have duplicates.
In the first sheet, select any cell within your data, and click on
Data - Filter - Autofilter. This places a drop-down arrow in the first cell
of every column.
Click the down-arrow in the names column. This displays all the names
in that names column. Click on a name. This filters your data so only that
one name is displayed (however many times it appears in your data). Also,
only those comments that apply to that one name are displayed. Select all
the comments shown and click Edit - Copy.
Go to the second sheet, find the name you selected on the first sheet,
select the corresponding cell in Column B, and click on Edit - Paste
Special - Transpose. Click OK. That places all the comments for that one
name into the same row as that one name. Repeat this for each of the
remaining names.
If the amount of data is so large that this procedure is cumbersome, you
might consider a VBA solution. Post back if you want to go with VBA HTH
Otto