automatic counting and ranking of text-only content

alina753

New Member
Joined
Nov 24, 2005
Messages
2
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.
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

shodan

Active Member
Joined
Jul 6, 2005
Messages
481
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,473
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFG
1NameCountryCountryCountRank
2FredArgentinaArgentina33
3BillArgentinaAustralia71
4SueAustraliaAustria42
5AnnAustralia
6AnnaAustralia
7SueAustralia
8BillAustralia
9FredAustralia
10SamAustralia
11TomArgentina
12BobAustria
13TomAustria
14BobAustria
15SueAustria
16
Sheet1
 

alina753

New Member
Joined
Nov 24, 2005
Messages
2
1000 thanks!

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

this is exactly what i was looking for.

keep up the great work :)

a.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,083
Messages
5,570,122
Members
412,305
Latest member
Mozz
Top