Macro to pull AP within a specific zip code

[Catherine]

I have an excel 2010 spreadsheet that lists all of our vendors and the amount we spent with them over the last year.

I want to know how many of them were local vendors.

I have a list of all the zip codes within a 100 mile radius (there are about 500 zip codes). I would like to write a macro or sort function that searches the entire vendor list and only reports back those vendors that are included in the zip codes I specify.

The columns are as follows:

A B C D E
Vendor City State Zip Code Total AP
Vendor 1 TROY AL 36082 527.37
Vendor 2 PHOENIX AZ 85054 100
Vendor 3 TUCSON AZ 85714 16255

I want to keep the all the columns, I just want to eliminate all of the vendors that do not fall within the zip code criteria I set.

Thanks in advance for any guidance.

 

[texasalynn]

where do you have the list of zip codes you want to compare with?

 

[Catherine]

where do you have the list of zip codes you want to compare with?

I was able to get the list online, I can paste it in any column/row in the spreadsheet or into any formula. It is set up as 11111,22222,33333, etc. comma delimited with no spaces.

 

[texasalynn]

if you want a macro to compare to this then that information would need to be in Excel as a column of data. Would you be able to create that within your existing spreadsheet as a separate tab (sheet)? Then a macro would be able to use that information to run against.

But I would suggest another method without macros where you could use your list of zip codes and do a vlookup. Then wherever you have #N/A is not a match and can be eliminated.

 

[mecg96]

you could use a VLOOKUP and some error trapping.

Assuming you had your list of desired zip codes in "Sheet2" Column A
and
Assuming you had your data like this starting in cell A1:

Vendor	City	State	Zip	Total AP
V1	Troy	AL	36082	527.37
V2	Phoenix	AZ	85054	100
V3	Tucson	AZ	85714	16255

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

if you entered this formula in column F next to your data =IF(ISERROR(VLOOKUP(D2,Sheet2!$A$2:$A$10,1,0)),"N","Y")
It would return "Y" if your vendor's zip code was in your list and "N" if it wasn't.
Then you could delete all the ones with "N".

 

[Catherine]

Excellent, thank you so much. That's exactly what I was looking for.