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

Steve,
When I was looking at a similar problem, a Google search turned up a macro
that I have amended for my use. Can't remember the exact location, but this
is the header that I retained.

*From: Iwer Mørck )
Subject: FREQUENCY?
Newsgroups: microsoft.public.excel.programming
Date: 1999/03/23*

Could be worth looking at.
Martin


Steve Wylie wrote in message ...
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:

<snip
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

This does it with formulas. All you need to do is copy the list
of responses into column B and sort that column. Copy column
A and C formulas down to how ever many rows you need to match
the data in column B. Copy the rows in the other table down as
many rows as you need for the number of unique replies you get.

www.home.gen.nz/Excel/Survey.xls


Chrissy.

Steve Wylie wrote
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

Thank you for your prompt reply. I have located the
message you describe and will hang onto the macro content
in case it comes in useful in the future. However at the
moment I am having a go at the Formula solution described
by Chrissy in the other reply to my post, but thanks for
the pointer to the possible macro solution.

Steve