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.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

shodan

Active Member
Joined
Jul 6, 2005
Messages
486
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
54,320
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,990
Messages
5,834,761
Members
430,318
Latest member
ZackChua95

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top