Match 2 different zip code lists

I have a large 42,000 list of US zip codes which each row (42,000 rows) has a 
zip code column,  a counties column and states column that I want to match to 
a smaller 4,000 zip code list- with a zip code column & dollar amount column. 
 Or even, use all the Illinois zip codes matched to a smaller IL zip codes 
list- so I can match same zip codes and corresponding county?  Now, they are 
set up on separate workbooks or worksheets.  thanks.
0
Will123 (5)
9/15/2009 7:29:02 PM
excel 39879 articles. 2 followers. Follow

3 Replies
599 Views

Similar Articles

[PageSpeed] 54

Will123 wrote:
> I have a large 42,000 list of US zip codes which each row (42,000 rows) has a 
> zip code column,  a counties column and states column that I want to match to 
> a smaller 4,000 zip code list- with a zip code column & dollar amount column. 
>  Or even, use all the Illinois zip codes matched to a smaller IL zip codes 
> list- so I can match same zip codes and corresponding county?  Now, they are 
> set up on separate workbooks or worksheets.  thanks.

The simplest approach is to use VLOOKUP, but this might give an 
occasional incomplete result*. First open both workbooks.

Assuming
- Short list has ZIP in col. A, dollar in col. B, data begins in row 2, 
county result desired in col. C
- Long list has ZIP in col. A, county in col. B, data begins in row 2

In the short list C2, type
   =VLOOKUP(A2,
do not press enter yet. Use the mouse or keyboard to switch to the long 
list workbook/worksheet. Select full columns A:B. Continue typing:
   ,2,false)
and press enter.

The formula in C2 can be filled down as far as needed.

*The tricky part might be that ZIP codes are not necessarily unique to a 
county. See http://www.zipinfo.com/products/cz/cz.htm for more on this.
0
smartin108 (170)
9/15/2009 10:48:30 PM
great, this helps... also, what if I want to add one more column of city 
names to go with counties... my long list has column C of city names...how 
would I change the formula and add county and city to my short list.. thanks

"smartin" wrote:

> Will123 wrote:
> > I have a large 42,000 list of US zip codes which each row (42,000 rows) has a 
> > zip code column,  a counties column and states column that I want to match to 
> > a smaller 4,000 zip code list- with a zip code column & dollar amount column. 
> >  Or even, use all the Illinois zip codes matched to a smaller IL zip codes 
> > list- so I can match same zip codes and corresponding county?  Now, they are 
> > set up on separate workbooks or worksheets.  thanks.
> 
> The simplest approach is to use VLOOKUP, but this might give an 
> occasional incomplete result*. First open both workbooks.
> 
> Assuming
> - Short list has ZIP in col. A, dollar in col. B, data begins in row 2, 
> county result desired in col. C
> - Long list has ZIP in col. A, county in col. B, data begins in row 2
> 
> In the short list C2, type
>    =VLOOKUP(A2,
> do not press enter yet. Use the mouse or keyboard to switch to the long 
> list workbook/worksheet. Select full columns A:B. Continue typing:
>    ,2,false)
> and press enter.
> 
> The formula in C2 can be filled down as far as needed.
> 
> *The tricky part might be that ZIP codes are not necessarily unique to a 
> county. See http://www.zipinfo.com/products/cz/cz.htm for more on this.
> 
0
Will123 (5)
9/16/2009 5:33:01 PM
Just a few small changes should do it. Say you want to return city to 
column D of the short list:

In the short list D2, type
   =VLOOKUP(A2,
do not press enter yet. Use the mouse or keyboard to switch to the long 
list workbook/worksheet. Select full columns A:C. Continue typing:
   ,3,false)
and press enter.

The complete formula for city now looks something like

  =VLOOKUP(D2,<long list book or sheet>!$A:$C,3,FALSE)

You can stop here if you like, but you might be interested to know more 
about how to use VLOOKUP.

The county formula I posted yesterday could also be

  =VLOOKUP(D2,<long list book or sheet>!$A:$C,2,FALSE)

When we say "look in columns A:B" we can only return column 1 or 2. When 
we say "look in columns A:C" we can return column 1, 2 or 3.

I mention this as a point of optimization. If you know ahead of time you 
will want to return multiple columns, I suggest you specify the widest 
range you will need to begin with ($A:$whatever). Note I started to use 
$ in front of the column references. This ensures as you copy the 
formula to the right, the range VLOOKUP is looking in does not move to 
the right as well. Then you only need to adjust the index (,2 ,3 etc.) 
for each result county, city, etc.

Hope this helps.




Will123 wrote:
> great, this helps... also, what if I want to add one more column of city 
> names to go with counties... my long list has column C of city names...how 
> would I change the formula and add county and city to my short list.. thanks
> 
> "smartin" wrote:
> 
>> Will123 wrote:
>>> I have a large 42,000 list of US zip codes which each row (42,000 rows) has a 
>>> zip code column,  a counties column and states column that I want to match to 
>>> a smaller 4,000 zip code list- with a zip code column & dollar amount column. 
>>>  Or even, use all the Illinois zip codes matched to a smaller IL zip codes 
>>> list- so I can match same zip codes and corresponding county?  Now, they are 
>>> set up on separate workbooks or worksheets.  thanks.
>> The simplest approach is to use VLOOKUP, but this might give an 
>> occasional incomplete result*. First open both workbooks.
>>
>> Assuming
>> - Short list has ZIP in col. A, dollar in col. B, data begins in row 2, 
>> county result desired in col. C
>> - Long list has ZIP in col. A, county in col. B, data begins in row 2
>>
>> In the short list C2, type
>>    =VLOOKUP(A2,
>> do not press enter yet. Use the mouse or keyboard to switch to the long 
>> list workbook/worksheet. Select full columns A:B. Continue typing:
>>    ,2,false)
>> and press enter.
>>
>> The formula in C2 can be filled down as far as needed.
>>
>> *The tricky part might be that ZIP codes are not necessarily unique to a 
>> county. See http://www.zipinfo.com/products/cz/cz.htm for more on this.
>>
0
smartin108 (170)
9/17/2009 12:18:09 AM
Reply:

Similar Artilces:

Problem with Generic List Remove method
I am having a strange problem with a generic list. Maybe someone can spot my error. Specifically I am trying to remove an item when the list is a list of class object instances. public class myClass { public string myString { get; set; } public int myInt { get; set; } public myClass() { this.myString = string.Empty; this.myInt = 0; } } public class DoSomeWork { public List<myClass> myList = new List<myClass>(); public DoSomeWork() { this.myList = new List<myClass>(); } public void SomeWork() { myClass myClassInstance = null; ...

Matching function or no match
I am using this formula to compare 2 list to see which numbers are not included in both column A and Col C. The formula I am using below pulls out the ones that i do have a match . Is there another function that I could use that would only pull out the ones that there is not a match? =IF(ISERROR(MATCH(A1,$C$1:$C$5,0)),"",A1) Thanks Uh =IF(ISERROR(MATCH(A1,$C$1:$C$5,0)),A1,"") -- HTH Bob "Donna" <donna@yahoo.com> wrote in message news:BE688A2B-3D42-45A0-AA3F-B65034C685E1@microsoft.com... > > I am using this for...

Client E-Mail and Web E-Mail not matching
I've got a few complaints from about 4 users where e-mail they see in their Outlook Client is not showing up in their Outlook Web Client when they check their e-mail from a remote location. We're using Exchange 2003 with SP2 installed and all the users have Office Outlook 2003 with SP1 Installed also. This seems to only have been a problem since we upgraded to Exchange SP2 two weeks ago. (Although I can't confirm it, but I wasn't told of any problems before th upgrade.) We had to upgrade to SP2 because of the 16 Gig limit so rollback is not an option. Best as I can t...

shapes #2
in publisher 2003, when I insert a shape it moves the text over. I want the shape to go around the text. Send the object to the back. (Arrange menu). Or select the text box, on the picture toolbar, click the dog icon, click none. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "school girl" <schoolgirl@discussions.microsoft.com> wrote in message news:6AB5DF10-A2E1-4BE3-AD64-0C85BD6BBCA8@microsoft.com... > in publisher 2003, when I insert a shape it moves the text over. I want the > shape to go around the text...

print preview not working #2
When I print preview a sheet in xl only 9 of 13 pages preview. When printing the same sheet to a printer, they all print. When printing to pdf only 9 of the pages print. ...

city, state, zip in same cell
I ha ve been given a large data base with city, state and zip in same cell. How can I seperate the city, state and zip without doing it manually? Good afternoon, u may want to try this. First insert a few columns right after the column that contains the city state, etc. info. Then click on the column letter to highlight the column that contains the city, state, etc. data u would like to split into cells, then goto DATA menu toward the top of your screen and click on TEXT TO COLUMNS. A box will pop up, make sure DELIMITED button is on and click NEXT, Then put a check in boxes by COMMA a...

customize outlook #2
I can not open customize outlook. Itried the suggestion in Microsoft Knowledge Base Article 820575. It did not help. Any suggestions. Nothing happens when you click on Customize Outlook Today,=20 even after changing the DWORD value as suggested in the=20 article. >-----Original Message----- >What errors are you getting? What is not working? Be=20 specific. > >--=81 >Milly Staples [MVP - Outlook] > >Post all replies to the group to keep the discussion=20 intact. > > >After searching google.groups.com and finding no answer >Jeb Bankert <jebesq@bankertpc....

Matching cells #3
Here's my spreadsheet: A B C 1 1 1 2 3 3 3 4 6 4 5 10 5 8 11 6 9 7 10 8 9 10 11 12 13 How do I get the matching numbers on the same rows, like: A B C 1 1 1 2 3 3 3 4 4 5 5 6 6 7 8 8 9 9 10 10 10 11 11 12 13 13 I would just re-create the columns. 1. Copy column A into D. 2. In E1 put: =IF(COUNTIF(B:B,$A1),$A1,"") 3. Copy it over to F1 and then down as far as needed. 4. Select columns D thru F, copy them, and go to Edit > Paste Special > Value. Press OK. 5. Delete column A thru C. HTH Jason Atlanta, GA &g...

MDI app with different CFrameWnd, searching one
Hello NG, in my MDI application, I use several different CChildFrame windows with its CDocument and CView. CPersonFrame* mit CPersonDoc und CPersonView CAdressesFrame* mit CAdressesDoc und CAdressesView CPreferencesFrame* mit CPreferencesDoc und CPreferencesView The PreferencesFrame and AdressesFrame should be opened only once at a time in my programme. The PersonFrame is a single window concerning each person in the AdressesFrame. Now I want to open a special window in my application menu. If it's already opened, it should appear in front. So I first look for the child window if it is...

CRM 1.2 -> 3.0 Upgrade failure
Hi, I am trying to get an install of CRM 1.2 upgraded to 3.0 but am running in to some issues when I try to. The installer fails with the following warnings and errors: Warning: The indexes on the Microsoft CRM database are not consistent with Microsoft CRM 1.2 indexes. Warning: Discrepancies were found in the read-only metadata. Error: Microsoft CRM 1.2 database export failed. The installer log files has the following line: 14:09:13| Error| Check ExportXmlValidator : Failure: Microsoft CRM 1.2 database export failed. We have previously been able to upgrade 1.2 to 3.0 successfully in ...

CInternetSession #2
Hello! For two days now, Iam strugling with some dificulties using CInternetSession.OpenURL() function. Iam writing simple code for separate thread function such as: while(true) { CInternetSession csiSession; CStdioFile* MyFile = csiSession.OpenURL(_T("http://www.someurl.com")); // working on retrieved data MyFile->Close(); delete MyFile; } Iam using VC2k5 and while building solution Ive received warning: First-chance exception at 0x7c90eb74 in Project1.exe: 0xC0000008: An invalid handle was specified. Warning is poiting to "OpenURL", but I don&#...

Lookup Formula: Return 1st match, then 2nd match, then 3rd match
I have a spreadsheet that looks like the following. Tab 1. Col A=Name. Col B = Task. A B Bob Reconcile Cash Tim Do Sales Report Bob Create presentation Tim Prepare financial statements Bob Hire staff person Now on tab two, I want to create another list that pulls all the tasks together by person. For example, Tab 2 would look like this: Bob Reconcile Cash Bob Create presentation Bob Hire staff person Tim Do Sales Report Tim Prepare financial statement. I realize I can just sor...

Can I have each series be a different field in a Pivot Chart?
I'd like to create a stacked column chart using a Pivot Chart. But each of the series that I'd like to stack are in different field (columns) in my source data. Is it possible to do this? I can't rearrange the source data, because then I'll exceed Excel's 64K row limitation. I am quite sure you can do it, but need more info about your set up before I will be aboe to give any recommendations Where is the data for each series? "tyson12" wrote: > I'd like to create a stacked column chart using a Pivot Chart. But each of > the series that I'd...

Index Match Functions
Has anyone ever combined the Index and Match functions to do lookups? ...

Combine lists
Hi, There is a way to make a combination of a table with a set of values resulting in another table? For example I have a table with 3 rows, so I combine it with 3 values that will generate a new table with 9 rows. For example: Name | age ------------------------ John | 23 Mike | 29 Bobby | 26 Combined with: Girl -------------- Alice Sara Susan Would result automatically in a final combination table like: Name | age| Girl -------------------------------- John | 23 | Alice Mike | 29 | Alice Bobby | 26 | Alice John | 23 | Sara Mike | 29 | Sara Bobby | 26 | Sara John | 23 | Susan Mike ...

Duplicate Attachments #2
We have come across a problem whereby I have sent an email with several attachments, one being a word doc and another being a ..publisher doc. Having sent the email the recipients have reported that he word doc has duplicated itself. Even in my sent items the emails shows the word doc having duplicated even though I attached it the once. Other colleagues have reported the same problem whereby attachments are duplicated on an email. We are working in outlook 2002 and have just upgraded to Exchange 2000. Has anyone seen this problem before. Many Thanks Lisa ...

Not matching transactions
I have it set to not automatically do anything. I've tried resetting the account quite a few times with the help of technicians, but it's too long and involved, and I'm tired of rebalancing the account every time. I have 3 accounts, 1 works without passport and does fine. One of the others I can manually download from the web site, and have no problems doing it like so and decided this is the best way, prior tries it wouldn't work with passport. But the other still insists they are new transactions, and I can't download from their site directly. They only allow...

news group #2
I know that this is not a Usenet group but I wonder if anyone can help me find a Usenet group that does not cost a fortune. I have had internet access for over 10 years and enjoyed the newsgroups via freeserve then wanadoo until just over a year ago they cut off the service. I then had an unsolicited offer from <news.ak47erg> to use the Usenet through them for a one off payment of �8 (or thereabouts). This has now expired as well. I have tried to find a reasonably priced access but the cheapest I have found so far was $10 per month, that is too much for me. If anyone can point me to a c...

help please #2
Hi anyone, In VBA how can I check a cell in one sheet against a range of cells in another sheet and then copy values from the 2nd sheet where the cells matched ? eg: sheetA range: A1 contains a list of codes sheetB range: B10:H100 each row contains a code in column b and then relevant data in columns c to h. I would want to look up aheetA range A1, see what code has been selected then go to sheetB and check for that code in column b, and where it matches put the contents of that row into sheetA range A10:g10 onwards. Thanks. Amy x Hi Amy do you really need a VBA solution as the VLOOKUP f...

data in row didnt match import type 12-30-05
while importing almost 800 accounts , i got the following error for majority of accounts 'Data in row did not match the import type' what is the solution? How can i match it? whats is the way of comparision? It looks like a data problem. You need to check if all dates are correct (and filled), if numbers are numbers (not letters), etc... Regards, -- Erik van Hoof CWR Mobility Check our weblog at: http://www.cwrmobility.com/weblog "Aam" <Aam@discussions.microsoft.com> wrote in message news:C51E8ED0-443D-4B35-B436-0A9EDB09B419@microsoft.com... > while impo...

Manually matching transactions
Hi, I'm new to Money 2007 so maybe this is an easy question but I don't find an answer after searching help and recent posts. I have setup a checking account and a bill for Tuition. My transactions are automatically downloaded to my checking account. One of these transactions was my Tuition payment, but the bill still shows as being past due. Can't I simply mark that transaction as being a Tuition payment somehow? I'm sure I could record payment from the bill with the same info as that transaction has then delete the downloaded transaction that wasn't associated ...

Keyboard Differs
Hi All, Having recently changed from OE to Outlook 2002, I have a small problem. When I enter a contact the @ key is not in it's normal place. It is at <Shift> <2> instead of at <Shift> <'>. This is a common fault with UK and US keyboard settings. Unfortunately my global keyboard setting is to UK as I require and all other programs work as expected, it is just when entering a contact that it switches position. Is there a simple seting that I am missing? Any help would be greatly appreciated. ...

formula for a column #2
I'm working on a daily worksheet where all the values for a column will be the same ex. L2/C2, L3/C3, L4/C4 How do I set it up? Assuming the column you want to compute the values is in say, col M Put in M2: =IF(C2=0,"",L2/C2) and press ENTER Point the cursor to the bottom right corner of M2 (cursor will turn into a "black cross") Drag to fill as far down as required .. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "msdobe" <msdobe@discussions.microsoft.com> wrote in message news:529B3703-9D9A-4560-80D...

Difference between HATB and ATB
I am running the historical aged trial balance (as of today) and aged trial balance and seeing different results. The aged trial balance is correct. The HATB is showing historical transactions (although they have been fully applied and moved to history) which should not be there. I am using the standard great plains reports (ver 8) with no modifications. Trying to create a new reports dictionary and rebooting the server did not help. "Jack" wrote: > I am running the historical aged trial balance (as of today) and aged trial > balance and seeing different results. The...

Message Routing #2
Knock, Knock, Hello No ideas at all? Come on dont let me fall at the first hurdle here, i am beggining to seriously regret installing Exchange. It is vastly more difficult to administer than MDaemon and at least on the MDaemon forum you get a prompt response, come on guy's please help........ snip - earlier post - sni Hi I am a newbie with a brand new SBS2003 install. I am usin the MS POP collector to retrive mails from both my domai hosting co and ISP. The mails from my ISP work OK, they ar collected into the respective individuals mailboxes. It i the mail from my domain hosting compa...