Counting duplicates\Frequency of duplicates

[A.D.]

A have a single column list with each row in the list containing one of
750 possible text values. The list runs 11,000 rows so there is
substantial duplication.

I know I can identify and count the number of dupes for each term by
using autofilter and the SUBTOTAL function - but it would take too long
to go through and filter each of the 750 values.

I've previously seen posted a formula that displays the number of dupes
for each cell when pasted down an adjacent helper column. Does anyone
know this? (A simple example below - I'm looking for a formula that
could quickly provide a count as shown in column B, below.)

Basically, my goal is to have a list that displays only the 750 unique
terms and the number of times (rows) each occurred

Thanks in advance for any help.

A B
1 dog 2
2 cat 4
3 bird 2
4 fish 1
5 cat 4
6 cat 4
7 dog 2
8 bird 2
9 cat 4


(To throw one other factor into the mix, if there is a second column
with a numberic value next to each, I'd like to also display the sum of
those values for each corresponding term - again, without having to
autofilter and subtotal each term.)

Thanks!

 

[Andy B]

Hi

Have a look at Chip's site:
http://www.cpearson.com/excel/duplicat.htm

 

[RagDyeR]

Enter this in B1:

=COUNTIF(A:A,A1)

And drag down to copy,
OR
Select B1 after the formula has been entered, and *double click* the fill
handle (small black square in lower right corner), to *automatically* copy
the formula down column B, as far as there is data in column A.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


A have a single column list with each row in the list containing one of
750 possible text values. The list runs 11,000 rows so there is
substantial duplication.

I know I can identify and count the number of dupes for each term by
using autofilter and the SUBTOTAL function - but it would take too long
to go through and filter each of the 750 values.

I've previously seen posted a formula that displays the number of dupes
for each cell when pasted down an adjacent helper column. Does anyone
know this? (A simple example below - I'm looking for a formula that
could quickly provide a count as shown in column B, below.)

Basically, my goal is to have a list that displays only the 750 unique
terms and the number of times (rows) each occurred

Thanks in advance for any help.

A B
1 dog 2
2 cat 4
3 bird 2
4 fish 1
5 cat 4
6 cat 4
7 dog 2
8 bird 2
9 cat 4


(To throw one other factor into the mix, if there is a second column
with a numberic value next to each, I'd like to also display the sum of
those values for each corresponding term - again, without having to
autofilter and subtotal each term.)

Thanks!

 

[Stephen Dunn]

Take a look in the help files for COUNTIF and SUMIF, they are all you need
here.