working with large list of company names, need a bar chart

**sweetbpf** · 04-22-2009, 06:21 PM

Everytime a user downloads my software their name and profile information gets added to an excel spreadsheet. So, if they come back multiple times, I can see that they have done so by the number of times their name appears in the spreadsheet.

I need to be able to count each company name and chart how many times it repeats in the list... thereby counting how many times companyX downloaded my software.

I could use the "countif" function, but I have 5,458 entries and ~1800 of those are unique entries AND they are TEXT not numbers, otherwise I could solve this by doing the countif on the uniques and apply it to the larger full list. But, I don't know how to use the countif function for each of those entries when they are text without laboriously typing, in quotes, each unique name.

Do I need to conver the text into numbers and then work with the data? It would be another trick to convert it back to text so that I knew what I was looking at.

Here's an example of my list:
alpha medium group
Alpha Square LLC
Alpine
AlpineSoft
Alsdorf
Altera
altibase
AMD
AMD India
AMD Japan
amf
AMI
AMnesty
Amphora
amsah
AMST
Anatomical Travelogue
Anita Grant Ltd
anonym
Anonymous
Ansys Fluent India Pvt Ltd
Applanix
apple
Apple Inc.
Applied Acoustics Systems DVM Inc.
Applied Materials
Apprender
apstudio

There are a ton of uniques... another thing that would be helpful would be if i could just sort by the number of repeats... so for example AMD, AMD, AMD, Intel, Intel, Apple.

Is there any easy way to do that at least?

thanks,
Sharon

**ConneXionLost** · 04-22-2009, 06:34 PM

Hi Sharon,

Perhaps I'm not understanding the problem, but would this work for you?

If your list is in column A, then in cell B1 paste this formula:

Please Login or Register  to view this content.

and copy/fill down as needed.

Cheers,

**martindwilson** · 04-22-2009, 09:58 PM

you have excel2007 ,i believe there is a remove duplicate function in there some where.
copy the list to another sheet and remove duplicates so you have an unique list
then use the countif using that new list as your criteria aginst the original. then its just a matter of house keeping to make sure new users are added to the unique list
you could do that by matching from the added to list aginst the unique on and flagging it if its not found so you can add it
see attached
for an idea

**sweetbpf** · 04-23-2009, 12:39 PM

The countif function would work like that if I was working with numbers. If I try count the cell if the text in the cell = X, then I have to actually put quote marks around the actual text in each cell... so it would look like

countif(A1:A5000, "microsoft").

I did try to create a list of the uniques and place the countif function next to it, then drag the formula down so that it picked up each cell - this would have been perfect - but, it always returned zero... I'm guessing its because it was text. If I actually spelled out the text that was inside the cell and put quotes around it, then it would return the right value.

Someone else had suggested I use a pivot table. Do you think that would work for this problem? I have never used pivot tables before. I have no idea where to start and if it would, indeed, solve my problem.

**ConneXionLost** · 04-23-2009, 01:10 PM

Hi sweetbpf,

For what it's worth, I confirmed that COUNTIF does work just fine with text. (In fact, I tested it with your list). Perhaps there is a different reason why the formula did not work for you. Is there a chance you could post a sample worksheet for someone here to have a look at?

Cheers,

Note ~ I'm behind a firewall at the moment, so won't be able to see the worksheet myself.