Excel Formulas to sort a large number of postcodes

Question

Hi,

I have a large number of post codes (about 900 or so) and I want to find out how many times each of the post codes occurs within that data. There are about 135 odd different post codes, so don't want to write 135 different 'countif' formulas.... any ideas on a fast and easy way?

Answer 1

Lets say your 900 postcodes are in A2:A901, with a column label (POSTCODE) in A1.

Select A1:A901, then on the Data tab, Sort and Filter group choose Advanced Filter

Select 'Copy to another location', tick 'Unique records only', and enter B1 in the 'Copy to:' box

In C2 enter the formula: =COUNTIF(A$2:A$901,B2) and copy down to the end of the unique list.

Answer 2

The best and fastest way will be use pivot table.

Select your range and insert a pivot table. Put your postal code in row area as well as in Value area. It will give you desired result.

See the snap shot.