Sort/Count a list of places

**Frazzfreeman** · 12-04-2007, 09:19 AM

Hi there everyone!!

I have a list of approx 10000 UK towns (not all different towns they do repeat themselves through out the list) and i would like to find out which ten towns feature the most in my list. Could someone please tell me an easy way of finding this out, apposed to me individually counting them all!

Thanks in advance!!

Apologies for the rubbish title but i am unsure of what this process is called!!

**NBVC** · 12-04-2007, 10:02 AM

The best way would probably be to use a Pivot Table.

Select the range of towns, including the column Title.

Go to Data|Pivot Table and Pivot Charts Report

Click Next on first and second windows

Click Existing Worksheet on 3rd window and enter where you want the table to go, then click Layout and drag the column title to the Data area (a field called "count of ...." should appear) and drag the same column title to the Row area...

Click Ok and click Finish... the table should appear in your sheet. This shows the count of each town.

To sort....first go to View|Toolbars|Pivot Table to get the Pivot Table toolbar.

Click on any town in your Pivot table and then click on "Pivot Table" in the toolbar. Select Sort and Top 10. Select Ascending or Descending on the left side and click Ok.

You should have your top 10 most frequent towns listed in the table.

**reggie1000** · 12-04-2007, 10:10 AM

hi frazzfreeman,

i have used in the past data/consolidate, if you add a 1 to the next cell on each of the towns,

click into a spare cell
open consolidate
in the consolidate control box enter

function = sum
reference = a1:b10000

click add and tick "left column" then click OK

this will add together all the identical towns and count them at the same time
then just sort to find the highest 5.
this will only work if the town data is identical, otherwise each individual entry will be count as a 1

hope it helps, if you need any assistance post a copy of your sheet

cheers reg