counting unique instances of text in a list

[WadeSansing]

I have a list of names in excel. They change frequently.

I want to read the list and count how many times each name appears.
for example if I have:

bob
bob
tom
sally
frank
bob
sally

I want it to return:

bob 3
tom 1
sally 2
frank 1

I do not care if "bob 3" is 2 columns one for name one for count, or if
they comibne with a concatenate or something, as long as it return the
name and count together somehow.

I really need it to do this without a lot of manual intervention. I
don't want to do an "advanced filter" because I'd have to redo the
filter evertime I look for the info. It needs to be dynamic. Just
so you'll know I am actually pulling the data from a web query on a
second sheet in the book. I've kinda got it working now but have to do
the "advanced filter" every time I update data, and for some reason the
filter always doubles up the first name if it is in the list more than
once.

I also do not care how the data is given to me, this can be done with
VB (which I suck at) or standard excel formulas. I really really do
not care how it counts the names and return the answers for me, jsut so
long as it does. It can be a button I click and have a vb script add up
and return info in dialog box, or whaterver.

Can anybody help? It seems like this would be a standard function in
excel, but I certainly can't find it, or jsut aren't smart enough to
figure out how to use the function if I have.

 

[barrfly]

Try using a pivot table. That way when you original spreadsheet change
you simply just refresh the pivot table

 

[Ron Coderre]

I think a Pivot Table would do what your asking for.

Data>Pivot Table>
-Excel List........[Next]
-Range: Select your range of names........[Next]
-Select the cell where you want the pivot table to be created.
-Click the [Layout] button
-Drag the column heading to ROW
-Drag the column heading to DATA (use COUNT)........[OK]
-[Finish]

Does that help?

Ro

 

[WadeSansing]

TY, works very well.

One minor bit, is there a way to make it refresh the data automatically
when I refresh the web import page? or do I have to right click and
choose "! refresh data" every time?

 

[barrfly]

If you are comfortable with VBA , record a macro where you refresh th
web query and then refresh the pivot table - then assign the macro to
button. From then on all you have to do is run one macro

 

[WadeSansing]

That is apparently beyond my vb skillz (which I don't really have any
as I can't get it to work Thx for the help guys