Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort by how many addresses per zip code
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort by how many addresses per zip code
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" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort by how many addresses per zip code
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... "Duke Carey" wrote: 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" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort by how many addresses per zip code
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" wrote: 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" wrote: 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sort by how many addresses per zip code
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 "Susienak" wrote: 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" wrote: 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" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I sort e-mail addresses? | Excel Discussion (Misc queries) | |||
How do I sort mailing addresses in Excel? | Excel Discussion (Misc queries) | |||
Help! How to sort addresses by street then by #...... | New Users to Excel | |||
How do I sort a column of IP addresses ? | Excel Worksheet Functions | |||
How do I sort addresses in excel? | Excel Worksheet Functions |