Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Aug 2014
    Posts
    1
    Thanks
    0
    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.

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    La Jolla,CA
    Posts
    1,100
    Thanks
    13
    Thanked 37 Times in 36 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.

    HTH
    Attached Files Attached Files
    Last edited by kweaver; 2014-08-04 at 19:34.

  3. #3
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,294
    Thanks
    47
    Thanked 257 Times in 237 Posts
    Nokin,

    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

    =INDEX(CountyData!F$2:G$12,MATCH(F2,CountyData!F$2 :F$12,0),2)

    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

    =IF(ISERROR(VLOOKUP(formula)),"",VLOOKUP(formula))

    or you can use the Index/match formula above. Both will place a blank instead
    Last edited by Maudibe; 2014-08-04 at 22:35.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •