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