I'm trying to move data from two of our old systems into a new system. Problem is, somewhere along the line the 'state' column of our DB was corrupted. I have a column of ZIP codes I need to determine to state from. I've found a number of solutions for Excel, but they don't seem to play nicely with OpenOffice. I'm fairly certain there's a way to do it with the CSV from here but I'm not really sure where to start. Any ideas?
3 Answers
This is quite the long way around, but it worked for what I needed. Hopefully someone else can use this info. I could have used additional OR statements for some of the states, but it was easier for me to lay out my dataset without it.
I had this all as one single formula, but it was much too long for OO's tastes, so I had to split it up into 4 formulas, paste the formulas into 4 columns, create a 5th column that concatenates the first 4, then paste the values only from the 5th column into the original 'state' column.
=IF(AND(S3>=99501;S3<=99950);"AK";IF(AND(S3>=35004;S3<=36925);"AL";IF(AND(S3>=71601;S3<=72959);"AR";IF(AND(S3>=75502;S3<=75502);"AR";IF(AND(S3>=85001;S3<=86556);"AZ";IF(AND(S3>=90001;S3<=96162);"CA";IF(AND(S3>=80001;S3<=81658);"CO";IF(AND(S3>=06001;S3<=06389);"CT";IF(AND(S3>=06401;S3<=06928);"CT";IF(AND(S3>=20001;S3<=20039);"DC";IF(AND(S3>=20042;S3<=20599);"DC";IF(AND(S3>=20799;S3<=20799);"DC";IF(AND(S3>=19701;S3<=19980);"DE";IF(AND(S3>=32004;S3<=34997);"FL";IF(AND(S3>=30001;S3<=31999);"GA";IF(AND(S3>=39901;S3<=39901);"GA";IF(AND(S3>=96701;S3<=96898);"HI";IF(AND(S3>=50001;S3<=52809);"IA";IF(AND(S3>=68119;S3<=68120);"IA";IF(AND(S3>=83201;S3<=83876);"ID";0))))))))))))))))))))
=IF(AND(S3>=60001;S3<=62999);"IL";IF(AND(S3>=46001;S3<=47997);"IN";IF(AND(S3>=66002;S3<=67954);"KS";IF(AND(S3>=40003;S3<=42788);"KY";IF(AND(S3>=70001;S3<=71232);"LA";IF(AND(S3>=71234;S3<=71497);"LA";IF(AND(S3>=01001;S3<=02791);"MA";IF(AND(S3>=05501;S3<=05544);"MA";IF(AND(S3>=20331;S3<=20331);"MD";IF(AND(S3>=20335;S3<=20797);"MD";IF(AND(S3>=20812;S3<=21930);"MD";IF(AND(S3>=03901;S3<=04992);"ME";IF(AND(S3>=48001;S3<=49971);"MI";IF(AND(S3>=55001;S3<=56763);"MN";IF(AND(S3>=63001;S3<=65899);"MO";IF(AND(S3>=38601;S3<=39776);"MS";IF(AND(S3>=71233;S3<=71233);"MS";IF(AND(S3>=59001;S3<=59937);"MT";IF(AND(S3>=27006;S3<=28909);"NC";IF(AND(S3>=58001;S3<=58856);"ND";0))))))))))))))))))))
=IF(AND(S3>=68001;S3<=68118);"NE";IF(AND(S3>=68122;S3<=69367);"NE";IF(AND(S3>=03031;S3<=03897);"NH";IF(AND(S3>=07001;S3<=08989);"NJ";IF(AND(S3>=87001;S3<=88441);"NM";IF(AND(S3>=88901;S3<=89883);"NV";IF(AND(S3>=06390;S3<=06390);"NY";IF(AND(S3>=10001;S3<=14975);"NY";IF(AND(S3>=43001;S3<=45999);"OH";IF(AND(S3>=73001;S3<=73199);"OK";IF(AND(S3>=73401;S3<=74966);"OK";IF(AND(S3>=97001;S3<=97920);"OR";IF(AND(S3>=15001;S3<=19640);"PA";IF(AND(S3>=02801;S3<=02940);"RI";IF(AND(S3>=29001;S3<=29948);"SC";IF(AND(S3>=57001;S3<=57799);"SD";IF(AND(S3>=37010;S3<=38589);"TN";IF(AND(S3>=73301;S3<=73301);"TX";IF(AND(S3>=75001;S3<=75501);"TX";IF(AND(S3>=75503;S3<=79999);"TX";0))))))))))))))))))))
=IF(AND(S3>=88510;S3<=88589);"TX";IF(AND(S3>=84001;S3<=84784);"UT";IF(AND(S3>=20040;S3<=20041);"VA";IF(AND(S3>=20040;S3<=20167);"VA";IF(AND(S3>=20042;S3<=20042);"VA";IF(AND(S3>=22001;S3<=24658);"VA";IF(AND(S3>=05001;S3<=05495);"VT";IF(AND(S3>=05601;S3<=05907);"VT";IF(AND(S3>=98001;S3<=99403);"WA";IF(AND(S3>=53001;S3<=54990);"WI";IF(AND(S3>=24701;S3<=26886);"WV";IF(AND(S3>=82001;S3<=83128);"WY";0))))))))))))
The concatenate formula is simply =CONCATENATE(T3;U3;V3;W3)
Hopefully this question will save someone else some time.
-
1for the record, you are using a spreadsheet, not a database (you refer to a DB in your original question)– horatioOct 27, 2011 at 20:52
-
No, I used a CSV dump of the database in question to rebuild a few corrupted columns. I edited the CSV in OpenOffice, added these formulas to a series of new columns, pasted the values that resulted into the original state column, deleted the formula columns, saved out the CSV again, and imported back into the DB. Nov 2, 2011 at 4:26
In the short term, you could import a fresh and updated listing of zip codes to States using a qualified source such as the Center for Medicare and Medicare Services. This is oriented to payer data, and is actively maintained. After that you could use a Pivot Table or handle the work through Open Office Base, and some SQL join magic to create a view that you could dump out to Calc or a CSV. What type of DB is behind your system? What type of import facility is available?
Another free tool would be to use Quest TOAD for DA Freeware to join the tables. This is very user friendly option with a small learning curve. If you find yourself doing data plumbing/reporting, this is a very nice tool, and worth the cost of a license.
Browse to https://www.cms.gov/prospmedicarefeesvcpmtgen/
Download the following zip archive: Zip Code to Carrier Locality File [ZIP, 4.08MB] - Updated 8/17/11
I haven't looked at the ZIP Code CSV, but I'm fairly certain you can use a VLOOKUP() formula that looks through that data.
=VLOOKUP(zipcode,csvdata,column number for state relative to zip in csvdata,FALSE)
