Count Unique ocurrances in a list

[Russ]

Hello,
I have a sheet with a long column of names. Many are
duplicated 2-10 times. I need a way to count the number
of times each unique name ocurrs. Example:

ColA
Jim
Bob
Jim
Sally
Bob
Aaron
Alice
Bob

I want to end up with a count for each name:
Bob 3
Jim 2
Sally 1
Aaron 1
Alice 1

The list is long with many names so I don't want to do a
CountIf(A1:A500,"Bob") for each one unless there is a way
to automatically put each unique "Name" in.

Any suggestions?

Thanks
Russ

 

[Bernard]

Visit Chip's site at www.cpearson.com for lots of stuff
on this topic.

 

[Peo Sjoblom]

One way, first create a unique list with the names, select the names header
included and do data>filter>advanced filter,
select copy to another location (assume you copy the header to H1 and the
first name will start in H2) and unique records only. That will give you all
the names
Now in I2 put

=COUNTIF(A:A,H2)

copy down as long as needed

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Russ]

Thanks! Worked like a charm.

-----Original Message-----
One way, first create a unique list with the names, select the names header
included and do data>filter>advanced filter,
select copy to another location (assume you copy the header to H1 and the
first name will start in H2) and unique records only. That will give you all
the names
Now in I2 put

=COUNTIF(A:A,H2)

copy down as long as needed

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)






.