2014-08-04, 17:48 #1
- Join Date
- Aug 2014
- Thanked 0 Times in 0 Posts
Excel- Auto fill specific info from one doc to another
I have a document in Excel which contains the postal information for every town in the United States. Each town has its own row, with zip code, latitude, longitude, city, state and county in columns in that order.
The mail file I am working on has the postal information for all of our customers in the U.S. with a similar layout. However, none of the contacts have county information, which I would like to add into an addition column.
Question: Is there a way to automate the input of the counties in the mail file using the document with the national information? Perhaps using the zip code column of the mail file to located the proper town on the national document, then filling the new column on the mail file with the information in the county column on the national document?
This would make a huge impact and any information would be helpful. Thanks so much.
Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!
+ Get this BONUS — free!
Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!
2014-08-04, 18:25 #2
- Join Date
- Jan 2001
- La Jolla,CA
- Thanked 36 Times in 35 Posts
Welcome to the lounge.
If a county has a unique zip code in the mail file, you should be able to do a vlookup of the zip code in the customer file and populate the county column.
Something like: =VLOOKUP(cust_file!F1,mail_file!$A$1:$F$1000,6,fal se) filled down. More specifics could be provided with two sample files attached.
A dummy file is attached to illustrate what I you're trying to do.
Last edited by kweaver; 2014-08-04 at 18:34.
2014-08-04, 20:31 #3
- Join Date
- Aug 2010
- Pa, USA
- Thanked 228 Times in 211 Posts
Kw's solution should work just fine but if you run into difficulty, the following formula will work using index and match.
Using KW's sample file, in cell G2, enter the following formula and copy down
There is a caveat using Vlookup. If you delete one of the zipcode values in the table array (national information sheet), delete a row, or if the zipcode is not listed, it will result in a #N/A error. To avoid this, you can check using ISERROR in an IF statement
or you can use the Index/match formula above. Both will place a blank instead
Last edited by Maudibe; 2014-08-04 at 21:35.