Zip Code Macro Needed - New to Programing

[vgaranchon]

I am rather new to macros with very limited programing knowledge. I am working in Excel 2003.

Two questions:

First, I have a workbook with the zip codes in A column and the corresponding state in B column. I have a seperate workbook with payments by zip code. I would like to be able to use a macro to put the state in a seperate column next to the corresponding zip code in the payments workbook.

Second, I would like a second macro, if necessary, to add up all payments within each state so that I have a total per state.

Any help would greatly be appreciated.

Thx,

 

[acw]

Hi

Do you really want macros for this. The first can be covered by the VLOOKUP function, and the second by the SUMIF function.


Tony

 

[vgaranchon]

If it can be done easier in VLOOKUP and SUMIF, great. I thought a macro might be better because I will have to perform both the zip to state function and the totaling by state function for roughly 60 worksheets.

I have no problem copying and pasting a formula if that ultimately will be easier. Would you be able to guide me through easy VLOOKUP and SUMIF formulas?

Thx for the reply,

 

[vgaranchon]

ok - I think I understand VLOOKUP. I've never worked the SUMIF function though.

I didn't want to look totally helpless

 

[acw]

Hi

Don't know how the second workbook is set up, but say you have the zip in column A, the states in column B (brought in by the VLOOKUP function) and the payments in column C.

In column D, starting in D1 create a unique list of states. In E1 the formula would be along the lines of

=SUMIF(B:B,D1,C:C)

This will give you the sum of column C for the state in D1.


Tony

 

[vgaranchon]

Thanks, Tony - I will give it a try.

 

[vgaranchon]

Tony,

It worked! Took a little manipulating to get right but it works and will make life so much easier.

Thank you again,

Viena

 

[vinvin]

ZIP Code Macro Still Needed

Hi
I would like to use the following list to validate the US ZIP codes to the state (assuming that typos are usu. with the ZIP code) within a Office2K3 spreadsheet ::

State	ST	Zip Min	Zip Max
Alaska	AK	99501	99950
Alabama	AL	35004	36925
Arkansas	AR	71601	72959
[Arkansas (Texarkana)	AR	75502	75502
Arizona	AZ	85001	86556
California	CA	90001	96162
Colorado	CO	80001	81658
Connecticut	CT	6001	6389
Connecticut	CT	6401	6928
Dist of Columbia	DC	20001	20039
Dist of Columbia	DC	20042	20599
Dist of Columbia	DC	20799	20799
Delaware	DE	19701	19980
Florida	FL	32004	34997
Georgia	GA	30001	31999
Georga (Atlanta)	GA	39901	39901
Hawaii	HI	96701	96898
Iowa	IA	50001	52809
Iowa (OMAHA)	IA	68119	68120
Idaho	ID	83201	83876
Illinois	IL	60001	62999
Indiana	IN	46001	47997
Kansas	KS	66002	67954
Kentucky	KY	40003	42788
Louisiana	LA	70001	71232
Louisiana	LA	71234	71497
Massachusetts	MA	1001	2791
Massachusetts (Andover)	MA	5501	5544
Maryland	MD	20331	20331
Maryland	MD	20335	20797
Maryland	MD	20812	21930
Maine	ME	3901	4992
Michigan	MI	48001	49971
Minnesota	MN	55001	56763
kc96 DataMO	MO	63001	65899
Mississippi	MS	38601	39776
Mississippi (Warren)	MS	71233	71233
Montana	MT	59001	59937
North Carolina	NC	27006	28909
North Dakota	ND	58001	58856
Nebraska	NE	68001	68118
Nebraska	NE	68122	69367
New Hampshire	NH	3031	3897
New Jersey	NJ	7001	8989
New Mexico	NM	87001	88441
Nevada	NV	88901	89883
New York (Fishers Is)	NY	6390	6390
New York	NY	10001	14975
Ohio	OH	43001	45999
Oklahoma	OK	73001	73199
Oklahoma	OK	73401	74966
Oregon	OR	97001	97920
Pennsylvania	PA	15001	19640
Puerto Rico	PR	0	0
Rhode Island	RI	2801	2940
South Carolina	SC	29001	29948
South Dakota	SD	57001	57799
Tennessee	TN	37010	38589
Texas (Austin)	TX	73301	73301
Texas	TX	75001	75501
Texas	TX	75503	79999
Texas (El Paso)	TX	88510	88589
Utah	UT	84001	84784
Virginia	VA	20040	20041
Virginia	VA	20040	20167
Virginia	VA	20042	20042
Virginia	VA	22001	24658
Vermont	VT	5001	5495
Vermont	VT	5601	5907
Washington	WA	98001	99403
Wisconsin	WI	53001	54990
West Virginia	WV	24701	26886
Wyoming	WY	82001	83128

I'm picturing something like this ::
Case1
if ZIP is not in STATE's range of ZIP codes
color cell red
Case2
if ZIP is non-numeric ZIP code // for foreign postal codes
color cell yellow
if ZIP is IN STATE's range of ZIP codes
color cell green

Has this ever been done? I'm sure the USPS would love if businesses started implementing such a tool
Thx
Vin