At work we do a lot of surveys and have to analyse the
data. We use a survey package called Snap, which is good
for analysing statistics but not so good with analysing
or collating text answers to questions.

I was wondering if anyone could help me design an Excel
macro that could assist me in collating text answers.

Let's say I have a question "Which newspaper do you read
every day?" and there is a space for respondents to type
a reply. Assuming all the replies are typed correctly
(no spelling errors etc), the Snap software can dump out
all the responses into a file that can be read into
Excel, then sorted into alpha order, and it would look
something like this:

Daily Mail
Daily Mail
Daily Mail
Daily Mirror
Daily Mirror
Daily Star
Herald
Herald
Herald
Herald
Sun
Sun
Telegraph
Telegraph

.... and so on. Now what I eventually want is really a
table, or the figures to go into a table, that would look
like this:

Paper Responses Percentage

Daily Mail 8 10
Daily Mirror 6 8
Daily Star 10 14
Herald 4 5

etc etc.

What I am looking for is a macro that would go down a
column adding up the entries that are the same (eg all
the Mail's, all the Herald's, etc) and placing the tally
for each one in the column adjacent to the first one,
then deleting the extra ones. If it was also able to
calculate the percentage column too, that would be a
bonus.

If someone could get me started on this macro, or if it
is relatively simple, give me the text for the complete
macro, I would be most grateful.

Steve Wylie