counting unique instances of text in a list

**WadeSansing** · 06-01-2005, 01:07 PM

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** · 06-01-2005, 01:17 PM

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

**Ron Coderre** · 06-01-2005, 01:17 PM

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?

Ron

**WadeSansing** · 06-01-2005, 01:32 PM

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** · 06-01-2005, 01:36 PM

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.