automatic counting and ranking of text-only content

[alina753]

hello, this is my first post & i'm a total beginner, so please be kind...

i tried the forum search but didn't find a solution to my problem.

i have a pretty large database (yeah, i know i probably shouldn't use excel for stuff like that, but i don't have a lot of software) of persons with their names in column A and their home country in column B. what i would like to have is

1) a count of instances of all the countries that appear in column B,

which should ideally look like this:

argentina | 16
australia | 25
austria | 7
belgium | 11

etc.

2) a ranking and a diagram of all the countries with their respective values.

i think i know how to do the second part (it should be in "analysis-functions", right?), so i'd be happy if someone could help me with item 1).

thank you very much in advance

a.

 

[shodan]

I think you can obtain this with a pivot table. make sure that your list has a header row. than click on the menu bar data/pivotable .
follow this wizard and click finish immediately. now an empty pivot table appears. now on the pivot table toolbar click "pivot table wizard" and than click on lay out. now put your contry where you see ROW, and put country again where you see DATA. click ok and finish. Now you have a pivot table with the frequency of the countries. dubbelclik on the number in the pivot table, and a second page is created where you'll have a list with the data per country.

Hope this helps,
regards,

 

[Peter_SSs]

alina753

Welcome to the Mr Excel board!

Try this:

1. Data|Filter|Advanced Filter...
2. Copy to another location
3. List range: B:B
4. Criteria range: B:B
5. Copy to: D:D (or some vacant column)
6. Tick Unique records only and click OK
7. Formula in E2 (copied down): =COUNTIF(B:B,D2)

Note: If you have a lot of countries in the list, a quick way to copy down the formulas is to select cell E2 after the formula has been entered and double click the 'Fill Handle' (little black square at the bottom right of the selected cell).

Edit: For the second part, try in F2 (copied down):
=RANK(E2,E:E)

Mr Excel.xls
	A	B	C	D	E	F	G
1	Name	Country		Country	Count	Rank
2	Fred	Argentina		Argentina	3	3
3	Bill	Argentina		Australia	7	1
4	Sue	Australia		Austria	4	2
5	Ann	Australia
6	Anna	Australia
7	Sue	Australia
8	Bill	Australia
9	Fred	Australia
10	Sam	Australia
11	Tom	Argentina
12	Bob	Austria
13	Tom	Austria
14	Bob	Austria
15	Sue	Austria
16
Sheet1

 

[alina753]

1000 thanks!

wow, thanks a lot for the quick help!!!

this is exactly what i was looking for.

keep up the great work

a.