Sort by how many addresses per zip code

[Susienak]

I have a very large list of addresses that I have sorted by zip code. Now I
want to take the zip code column and find out how many addresses per zip
code. Example: there are 200 different zip codes, zip code 12478 has 14
repeats, zip code 12903 has 67 repeats...... and can it chart each zip code
with its repeat?. Is this a sort, filter, or a combin function. Can someone
give me a formula and instruct me where to go in Excel 2007. I know this
should be a simple formula but I hardly ever work with excel.

 

[Duke Carey]

Since you're in 2007 - copy the ZIP code column to an empty place on the
worksheet or a new worksheet, then select the entire column. Now go to the
Data tab and click on the Remove Duplicates button. That'll leave you with
the unique ZIPS.

Now, next to the first ZIP in the unique list, use the formula

=COUNTIF(range with ALL THE ZIPS, cell with the first unique zip)

copy it down

 

[Susienak]

Duke... Started doing that and ran into a glitch: after copying it onto a new
worksheet and going to data/remove duplicates, I found hundreds of the zip
codes have that four digit extension after them... how do I delete the four
digit extension so I can continue...

 

[Susienak]

and after I get the unique zips I dont understand what youre saying to do: in
the cell next to the first zip I type "=CONTIF"........ and then copy it down
for all the other zips... and then what? How is that going to tell me how
many addresses I had per each zip code wehn I removed all of the duplicates?
Am I taking ths back to the first list? As I stated, I dont work very often
with excel so can you explain this more clearly. Thanks.

 

[Duke Carey]

Susie -

To get rid of the 4 digit extensions do this:

Copy the zip code column to an isolated area, say it is in column O, with
the ZIPs starting in row 2

This formula in P2 will give you just the first 5 digits

=LEFT(o2,5)

Copy that down for all the ZIP codes. Now select all these new formulas and
copy them. Without moving the selection, choose Edit->Paste Special, Values.
You now have a column of all 5 digit ZIPS. Use the Remove Duplicates
command on the data in column P

I'm going to assume your original data is in columns A:G, with the Zips in
G. Use this formula in Q2

=COUNTIF(G$2:G$5000,P2)

That tells you how many addresses have the same ZIP as appears in P2. Copy
that formula down for every ZIP code in column P