counting occurences of a country name in a specific column

[tryer]

I have a sheet of variable numbers of rows that will have a variety of countries listed on one column.
without sorting the sheet i want to have a list of each country (listed on a seperate sheet) with the number of occurences of each.
i.e.
Germany 547
France 325
UK 679
I can do it by looping down every row numerous times but as the sheet is a few thousand lines long this is not a good way to do it. can anyone suggest a smarter way?

[judgeh59]

can you use a pivot table....if your data is setup as shown a pivot table would work very easily....

[AB33]

Use countif function.

[tryer]

Thanks for the reply Ernest but I need to use vba as this will form part of a larger routine

[judgeh59]

could you attach a clean sample data sheet?

[tryer]

Use countif function.

thanks for the reply, how can I use countif when I don't know which country names might be present?

[tryer]

could you attach a clean sample data sheet?

Thanks Ernest, here is a stripped down sample sheet with the required results on sheet 2

[judgeh59]

so is the country list static on the results page?...

[AB33]

Please Login or Register  to view this content.

[oeldere]

With an pivot table.

Notice: the results on your sheet don't match with the answer of the pivot table.

[judgeh59]

@ oeldere ---his data is not contiguous....I had the same problem then I realized it's not contguous

[oeldere]

@judgeh59

Great your pointing the OP on the possibility of Pivot Table.

It a realy powerfull tool.

I suggest we let the OP decide if he want's this solution.

[judgeh59]

@ oeldere - agreed on all point....I'm a very big fan of PT and try to get people to head that direction....even creating them using VBA...Thanks

[AB33]

@ oeldere ,
Could we use a countif function? I mean a formula.

[oeldere]

@AB33

Yes (Yep) you can (of course).

See the attached file.

Now I see why the results in the pivot table don't match, with the expected results.

There are gaps in the data (row 477 is empty).

Then the table will not expand and the pivot table, won't take this row in the counting of data.

[judgeh59]

@oeldere --- doesn't that assume that the country list is static....which is why I asked that in #8...if it is, then the countif is a great solution....unless you wanted to add the new country when you update the sample....

[AB33]

@ oeldere ,
Thank you very much!
I was wondering if it can be done in one go( One formula). Or is it impossible? It looks like you need to have unique lists on a separate column first and then count them on another separate column.

[oeldere]

@AB33

That's the way I have done it.

Probably it can be done in one formula, but I doubt if it gets easier to understand.

I copied column B to column J.

Then I used data => remove duplicated (excel 2007) to get the unique countries.

After that I used an countif formula.

But instead of countif I would prefer using pivot table, since that will select the unique countries by it's own.

If you have questions or comments on this item just ask.

[oeldere]

@judgeh59

In this case, I, should have looked better and noticed that there are gaps in the data.

There are macro's to solve that problem.

Another option would be to filter on the data and delete the whole rows the filter selected.

Never the less pivot table will be a good solution for this problem.

[AB33]

@ oeldere ,

"Probably it can be done in one formula, but I doubt if it gets easier to understand."
Come on mate!
Can not get complicated than a code. Please share with us if it could be done in single formula.

[oeldere]

I will take a look at it, but it will not be today, and probably also not tomorrow.

I come back to you on this item.

But as already said I would go for the (easy) pivot table solution.

[oeldere]

@AB33

Please Login or Register  to view this content.

If it can be done with an formula (and I bet it can) I will also suggest to use built in functionality of excel (excel 2007 => remove duplicates).

I'm personly not interested in the formula option (in a single formula) if there are better options.

In this case smaller is not always better (to my opinion).

So I stick by the given answers (solutions).

[AB33]

@ oeldere ,
Thanks!

[tryer]

Sorry for not replying earlier guys, I have been offline for a few days. I very much appreciate your responses and comments, reading through this thread has enabled me to increase my understanding already. Pivot tables are something I don't really use much and consequently don't have much knowledge of - I must learn more
The code supplied by AB33 in #9 works perfectly - thank you so much. I don't really understand how it works but will keep on trying.
Thank you all again for your time and responses.