Suggestions on filtering zip codes

[goodvibe]

Greetings!
I have a mailing list in excel with about 2000 addresses and zip codes. The problem is that only 239 of those zip codes pertain to me. To filter out the zip codes I need, I have been sorting by zip code and manually going through all of the ones I need and deleting the rest.

I have tried to filter with a custom list, but it says that I have too many items to make a custom list. Can anyone suggest a formula or another way to easily filter the zip codes. I am going to get a new membership list each month and I am going to need to do this often.

Thanks in advance for your help!

 

[Joe4]

Welcome to the Board!

There are a few ways of doing this. One is to create a list of all possible zip codes that pertain to you. Then you can "bounce" each month's file against this list using the VLOOKUP function. All the ones that pertain to you will return a value, while the others will have errors.

Another way (and my preferred method) is to use Microsoft Access, if you have access to it. Follow the same logic above where you have a table of all zips that pertain to you, and then another table of your monthly data. Then all you need to do is a simple Matched query between the two tables to return the new records that pertain to you.

 

[Saagar]

Have you tried using a "Pivot Table"?

I have never used a Pivot Table with 2000 unique values, so not sure if you are able to filter out around 240 required values. Worth a try.

 

[Aladin Akyurek]

Since you need the process to be dynamic, a formula approach might better suit. Care to provide a fake sample of zip codes and the criteria for selecting from the code data?

 

[goodvibe]

Thanks for all of your input. I tried the pivot table and it didn't seem to give me the results I was looking for. I am going to try to input my file into Access and see if that gives me the accurate results.

A formula with the vlookup might work, but I am having trouble configuring it. Also, some of the zip codes have the zip+4. Is there any way to make the search just look at the first 5 characters in the field?

Sampling of zip codes:
<table border="0" cellpadding="0" cellspacing="0" width="47"><col style="mso-width-source:userset;mso-width-alt:1718;width:35pt" width="47"> <tbody><tr style="height:15.6pt" height="20"> <td class="xl65" style="height:15.6pt;width:35pt" height="20" width="47">07001</td> </tr> <tr style="height:15.6pt" height="20"> <td class="xl66" style="height:15.6pt;border-top:none" height="20">07008</td> </tr> <tr style="height:15.6pt" height="20"> <td class="xl66" style="height:15.6pt;border-top:none" height="20">07016</td> </tr> <tr style="height:15.6pt" height="20"> <td class="xl66" style="height:15.6pt;border-top:none" height="20">07023</td> </tr> <tr style="height:15.6pt" height="20"> <td class="xl66" style="height:15.6pt;border-top:none" height="20">07027</td> </tr> <tr style="height:15.6pt" height="20"> <td class="xl66" style="height:15.6pt;border-top:none" height="20">07033</td> </tr> <tr style="height:15.6pt" height="20"> <td class="xl66" style="height:15.6pt;border-top:none" height="20">07036</td> </tr> <tr style="height:15.6pt" height="20"> <td class="xl66" style="height:15.6pt;border-top:none" height="20">07059</td> </tr> <tr style="height:15.6pt" height="20"> <td class="xl66" style="height:15.6pt;border-top:none" height="20">07060</td> </tr> <tr style="height:15.6pt" height="20"> <td class="xl66" style="height:15.6pt;border-top:none" height="20">07061</td> </tr> <tr style="height:15.6pt" height="20"> <td class="xl66" style="height:15.6pt;border-top:none" height="20">07062</td> </tr> <tr style="height:15.6pt" height="20"> <td class="xl66" style="height:15.6pt;border-top:none" height="20">07063</td> </tr> <tr style="height:15.6pt" height="20"> <td class="xl66" style="height:15.6pt;border-top:none" height="20">07064</td> </tr> <tr style="height:15.6pt" height="20"> <td class="xl66" style="height:15.6pt;border-top:none" height="20">07065</td> </tr> <tr style="height:15.6pt" height="20"> <td class="xl66" style="height:15.6pt;border-top:none" height="20">07066</td> </tr> <tr style="height:15.6pt" height="20"> <td class="xl66" style="height:15.6pt;border-top:none" height="20">07067</td> </tr> <tr style="height:15.6pt" height="20"> <td class="xl66" style="height:15.6pt;border-top:none" height="20">07069</td> </tr> <tr style="height:15.6pt" height="20"> <td class="xl66" style="height:15.6pt;border-top:none" height="20">07076</td> </tr> <tr style="height:15.6pt" height="20"> <td class="xl66" style="height:15.6pt;border-top:none" height="20">07077</td> </tr> <tr style="height:15.6pt" height="20"> <td class="xl66" style="height:15.6pt;border-top:none" height="20">07080</td> </tr> <tr style="height:15.6pt" height="20"> <td class="xl66" style="height:15.6pt;border-top:none" height="20">07081</td> </tr> <tr style="height:15.6pt" height="20"> <td class="xl66" style="height:15.6pt;border-top:none" height="20">07083</td> </tr> <tr style="height:15.6pt" height="20"> <td class="xl66" style="height:15.6pt;border-top:none" height="20">07088</td> </tr> <tr style="height:15.6pt" height="20"> <td class="xl66" style="height:15.6pt;border-top:none" height="20">07090</td> </tr> <tr style="height:15.6pt" height="20"> <td class="xl66" style="height:15.6pt;border-top:none" height="20">07091</td> </tr> <tr style="height:15.6pt" height="20"> <td class="xl66" style="height:15.6pt;border-top:none" height="20">07092</td> </tr> <tr style="height:15.6pt" height="20"> <td class="xl66" style="height:15.6pt;border-top:none" height="20">07095</td> </tr> <tr style="height:15.6pt" height="20"> <td class="xl66" style="height:15.6pt;border-top:none" height="20">07201</td> </tr> <tr style="height:15.6pt" height="20"> <td class="xl66" style="height:15.6pt;border-top:none" height="20">07202</td> </tr> <tr style="height:15.6pt" height="20"> <td class="xl66" style="height:15.6pt;border-top:none" height="20">07203</td> </tr> <tr style="height:15.6pt" height="20"> <td class="xl66" style="height:15.6pt;border-top:none" height="20">07204</td> </tr> <tr style="height:15.6pt" height="20"> <td class="xl66" style="height:15.6pt;border-top:none" height="20">07205</td> </tr> <tr style="height:15.6pt" height="20"> <td class="xl66" style="height:15.6pt;border-top:none" height="20">07206</td> </tr> <tr style="height:15.6pt" height="20"> <td class="xl66" style="height:15.6pt;border-top:none" height="20">07207</td> </tr> <tr style="height:15.6pt" height="20"> <td class="xl66" style="height:15.6pt;border-top:none" height="20">07208</td> </tr> <tr style="height:15.6pt" height="20"> <td class="xl66" style="height:15.6pt;border-top:none" height="20">07701</td> </tr> <tr style="height:15.6pt" height="20"> <td class="xl66" style="height:15.6pt;border-top:none" height="20">07702</td> </tr> <tr style="height:15.6pt" height="20"> <td class="xl66" style="height:15.6pt;border-top:none" height="20">07703</td> </tr> <tr style="height:15.6pt" height="20"> <td class="xl66" style="height:15.6pt;border-top:none" height="20">07704</td> </tr> <tr style="height:15.6pt" height="20"> <td class="xl66" style="height:15.6pt;border-top:none" height="20">07709</td> </tr> <tr style="height:15.6pt" height="20"> <td class="xl66" style="height:15.6pt;border-top:none" height="20">07710</td> </tr> <tr style="height:15.6pt" height="20"> <td class="xl66" style="height:15.6pt;border-top:none" height="20">07711</td> </tr> <tr style="height:15.6pt" height="20"> <td class="xl66" style="height:15.6pt;border-top:none" height="20">07712</td> </tr> <tr style="height:15.6pt" height="20"> <td class="xl66" style="height:15.6pt;border-top:none" height="20">07715</td> </tr> <tr style="height:15.6pt" height="20"> <td class="xl66" style="height:15.6pt;border-top:none" height="20">07716</td> </tr> <tr style="height:15.6pt" height="20"> <td class="xl66" style="height:15.6pt;border-top:none" height="20">07717</td> </tr> <tr style="height:15.6pt" height="20"> <td class="xl66" style="height:15.6pt;border-top:none" height="20">07718</td> </tr> <tr style="height:15.6pt" height="20"> <td class="xl66" style="height:15.6pt;border-top:none" height="20">07719</td> </tr> <tr style="height:15.6pt" height="20"> <td class="xl66" style="height:15.6pt;border-top:none" height="20">07720</td> </tr> <tr style="height:15.6pt" height="20"> <td class="xl66" style="height:15.6pt;border-top:none" height="20">07721</td> </tr> <tr style="height:15.6pt" height="20"> <td class="xl66" style="height:15.6pt;border-top:none" height="20">07722</td> </tr> <tr style="height:15.6pt" height="20"> <td class="xl66" style="height:15.6pt;border-top:none" height="20">07723</td> </tr> </tbody></table>

 

[Joe4]

Is there any way to make the search just look at the first 5 characters in the field?

Sure.

If your zip code was in cell A1, here is how you would just lookup the first five characters of it.
=VLOOKUP(LEFT(A1,5),...)

 

[Jonmo1]

I would create a list of zip codes that pertain to you, then use a match formula in an adjescent column and filter that column..

Say:
Column A = ALL zip codes
Z1:Z200 = Zip codes that pertain to you

In an available column, put the formula
=ISNUMBER(MATCH(A2,Z$1:Z$200,0))
And fill down to the end of data in column A

Then filter that column for either True or False
True = zip pertains to you
False = zip does not pertain to you

Hope that helps.

 

[Saagar]

Thanks for all of your input. I tried the pivot table and it didn't seem to give me the results I was looking for.

What seemed to be the problem with the pivot table? Were you not able to filter, or were you not able to format the data in a way you want?

 

[goodvibe]

Unfortunately, my excel skills are very lacking. I have been trying the "=ISNUMBER(MATCH(A2,Z$1:Z$200,0))" formula, and the "=VLOOKUP(LEFT(A1,5),...)" formula, but I keep getting errors such as "I have entered too few arguments for this function. I am good with simple mathematical formulas, but this stuff is very confusing.

I am going to search to see if I can find the formulas in action on a spreadsheet.

 

[goodvibe]

What seemed to be the problem with the pivot table? Were you not able to filter, or were you not able to format the data in a way you want?

With the pivot Table, I put the available zip codes in the row fields section, the filtered zip codes in the column fields section and the e-mail addresses in the value fields and I could only get it to display the count of the number of e-mail addresses. I am going to search you tube for more advanced lessons on making pivot tables.