How Can Excel Identify & Count the # of Duplicate Entries

[mrboloyen]

I am working on a statistical probability problem.

How can Excel be used to count the statistical data?

I want Excel to
1) list the individual answer only once
2) and count the # of occurances (of the answer)

How can this be done?

I have an Excel spreadsheet that contains statistical data.

In the spreadsheet, there are 10,000 "yes/no" answers and
10,000 "A/B/C" answers. There are also 10,000 answers to
more complicated series questions "Choose either A/B/C
and then either D/E/F and then either G/H/I....ect"

The series questions have individual answers (For
example, the series question, "Choose either A/B/C and
then either D/E/F and then either G/H/I", may produce
answers like "ADG" or "B,D,A"

The "Count If" function works great on the "Yes/No" answers, but i
isn't practical for more complicated answers. I would use the "Coun
If" function, but some questions have over 500 different answers o
more answers.

When using Excel's Data Filter, it list the individual answers that I'
looking for. But again, it is too much work to isloate the answer
that way.

Please help.

Thank Yo

 

[c-]

If you are working with 30K+ records, you might be better
off using Access and just doing a simple query where you
group by answer and count answer.

That being said, in Excel to get the list of unique
answers you can select the whole column of answers, then
choose Data..Filter..Advanced. Then choose that you want
unique values only and pasted into separate location.
This will give you a list of unique answers, then you can
do the countif to get the number of times each answers
appears.

Hope that helps.

c-

 

[Frank Kabel]

Hi
I would take a look at pivot tables to achieve your goal. For some
information regarding picots take a look at:
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.cpearson.com/excel/pivots.htm

Select the column for answers as row for the pivot table and the
countof answers ad data source

HTH
Frank

 

[BrianB]

Concatenate the columns you wish to combine into a new column and the
do a pivot table.

If you wish you can use your new column name twice in the PT setup - i
ROW (to show values) and DATA (to show count of values)

 

[mrboloyen]

I've tried it. And it will work but it is a lot of work.
The data is too big for the pivot tables.
I'd have to break the columns in halfs or forths then recombine th
seperate pivot tables.

Any other others

 

[mrboloyen]

Thanks. This will definately work.

If you have any alternate ways of solving this please let me know

 

[mrboloyen]

I'll give it a try.

Thanks!