counting unique instances of text in a list
 

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.


--
WadeSansing
------------------------------------------------------------------------
WadeSansing's Profile: http://www.excelforum.com/member.php...o&userid=23940
View this thread: http://www.excelforum.com/showthread...hreadid=375717

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


--
barrfly

Excel User - Energy markets
------------------------------------------------------------------------
barrfly's Profile: http://www.excelforum.com/member.php...fo&userid=4141
View this thread: http://www.excelforum.com/showthread...hreadid=375717

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

DataPivot 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?

Ron


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=375717

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?


--
WadeSansing
------------------------------------------------------------------------
WadeSansing's Profile: http://www.excelforum.com/member.php...o&userid=23940
View this thread: http://www.excelforum.com/showthread...hreadid=375717

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


--
barrfly

Excel User - Energy markets
------------------------------------------------------------------------
barrfly's Profile: http://www.excelforum.com/member.php...fo&userid=4141
View this thread: http://www.excelforum.com/showthread...hreadid=375717

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.


--
WadeSansing
------------------------------------------------------------------------
WadeSansing's Profile: http://www.excelforum.com/member.php...o&userid=23940
View this thread: http://www.excelforum.com/showthread...hreadid=375717