Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
2 Files with Different Fields
I have an Excel file with fields City, State, Zipcode and Consultant which
has a limited number of records and another Excel File that has the fields City, State, Zipcode and County and includes all zip codes in the US. How do I go about adding/merging or using the second file to add the County field in the first file so that I can have the first file with Consultant show the appropriate County for each Zipcode listed? -- Brian |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
2 Files with Different Fields
Hi Brian,
You should be able to use Vlookup function. The Zip code and County will be the table array and do the lookup on the zip code and insert the column with the county. The Zipcode and County columns will need to be arranged so that the zipcode is the first column. Insert the formula in the column where you want the County. Assuming there are only 2 columns (Zip and County) in the table array then column index number in the formula will be 2 which represents the County column. Set the last parameter to false so that you only get exact matches and not the nearest match. Also note that the table array must be in absolute format with the $ signs in front of column and rows like the below sample. =VLOOKUP(D2,Sheet2!$A$2:$B$27,2,FALSE) In the above sample, cell D2 contains the zip code in the table to have the county codes inserted. Sheet2!$A$2:$B$27 is the table array with zip and county with zip in first column 2 is the second column of the zip/county table false says exact matches only -- Regards, OssieMac "bdehning" wrote: I have an Excel file with fields City, State, Zipcode and Consultant which has a limited number of records and another Excel File that has the fields City, State, Zipcode and County and includes all zip codes in the US. How do I go about adding/merging or using the second file to add the County field in the first file so that I can have the first file with Consultant show the appropriate County for each Zipcode listed? -- Brian |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
2 Files with Different Fields
Of course I am confused. Trying to figure our formula you provided.
=VLOOKUP(D2,Sheet2!$A$2:$B$27,2,FALSE) File 1 with Sheet - CONSULTANT with Columns and ROW 1 Header Zip Code, County, City, State, Consultant, SVC Office - 14435 total rows File 2 - with Sheet ZIP CODE with columns and Row 1 header Zip Code, County, City, State, A/C, FIPS, PREF?, Type - 65536 total rows File 1 Sheet Consultant has the County Field that needs to be filled out. Can you help with Formula based on this as I am not sure about D2 and array? Thanks. -- Brian "OssieMac" wrote: Hi Brian, You should be able to use Vlookup function. The Zip code and County will be the table array and do the lookup on the zip code and insert the column with the county. The Zipcode and County columns will need to be arranged so that the zipcode is the first column. Insert the formula in the column where you want the County. Assuming there are only 2 columns (Zip and County) in the table array then column index number in the formula will be 2 which represents the County column. Set the last parameter to false so that you only get exact matches and not the nearest match. Also note that the table array must be in absolute format with the $ signs in front of column and rows like the below sample. =VLOOKUP(D2,Sheet2!$A$2:$B$27,2,FALSE) In the above sample, cell D2 contains the zip code in the table to have the county codes inserted. Sheet2!$A$2:$B$27 is the table array with zip and county with zip in first column 2 is the second column of the zip/county table false says exact matches only -- Regards, OssieMac "bdehning" wrote: I have an Excel file with fields City, State, Zipcode and Consultant which has a limited number of records and another Excel File that has the fields City, State, Zipcode and County and includes all zip codes in the US. How do I go about adding/merging or using the second file to add the County field in the first file so that I can have the first file with Consultant show the appropriate County for each Zipcode listed? -- Brian |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
2 Files with Different Fields
Hi again Brian,
Firstly I am not sure if File 1 and File 2 refers to separate worksheets in the same workbook or totally separate workbooks. Therefore I will give you formulas for both conditions. I am assuming from the column headers that you have given me that "Zip Code" is in column A in both the tables and "County" is in column B in both tables. If both tables are in the SAME workbook then assume that the worksheet name for the table with Consultant is Sheet1 and the lookup table is Sheet2. Enter the following formula in cell B2 in Sheet1 (the County column to be populated.) =VLOOKUP(A2,Sheet2!$A$2:$B$65536,2,FALSE) If the tables are in totally separate workbooks, then assuming that the lookup table is in Book2 then the formula will be like this:- =VLOOKUP(A2,[Book2]Sheet1!$A$2:$B$65536,2,FALSE) Copy the formula to the bottom of the data in the column. Note: You can select the range for the lookup during creation of the formula. After you enter =VLOOKUP( you can click on cell A2 to enter it in the formula. After you enter the comma after A2 you can change worksheets or workbooks and select the range for the table array. If the table array is not in absolute format immediately after you select it, then press F4 and it will insert the $ signs for you. Selecting the range is the best way to go because Excel handles the syntax for you and even inserts single quotes around workbook and worksheet names which have spaces in their names. -- Regards, OssieMac "bdehning" wrote: Of course I am confused. Trying to figure our formula you provided. =VLOOKUP(D2,Sheet2!$A$2:$B$27,2,FALSE) File 1 with Sheet - CONSULTANT with Columns and ROW 1 Header Zip Code, County, City, State, Consultant, SVC Office - 14435 total rows File 2 - with Sheet ZIP CODE with columns and Row 1 header Zip Code, County, City, State, A/C, FIPS, PREF?, Type - 65536 total rows File 1 Sheet Consultant has the County Field that needs to be filled out. Can you help with Formula based on this as I am not sure about D2 and array? Thanks. -- Brian "OssieMac" wrote: Hi Brian, You should be able to use Vlookup function. The Zip code and County will be the table array and do the lookup on the zip code and insert the column with the county. The Zipcode and County columns will need to be arranged so that the zipcode is the first column. Insert the formula in the column where you want the County. Assuming there are only 2 columns (Zip and County) in the table array then column index number in the formula will be 2 which represents the County column. Set the last parameter to false so that you only get exact matches and not the nearest match. Also note that the table array must be in absolute format with the $ signs in front of column and rows like the below sample. =VLOOKUP(D2,Sheet2!$A$2:$B$27,2,FALSE) In the above sample, cell D2 contains the zip code in the table to have the county codes inserted. Sheet2!$A$2:$B$27 is the table array with zip and county with zip in first column 2 is the second column of the zip/county table false says exact matches only -- Regards, OssieMac "bdehning" wrote: I have an Excel file with fields City, State, Zipcode and Consultant which has a limited number of records and another Excel File that has the fields City, State, Zipcode and County and includes all zip codes in the US. How do I go about adding/merging or using the second file to add the County field in the first file so that I can have the first file with Consultant show the appropriate County for each Zipcode listed? -- Brian |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
2 Files with Different Fields
Ok all have county names but all seem to be the wrong name. Here is what
syntax had to be to get values! I was getting o and N/A previously. =VLOOKUP(ZipCodes!A2,ZipCodes!$A$2:$B$65536,2,FALS E) The problem I am having is that most Zip Codes are returning incorrect Counties. See anything wrong with Syntax that is causing my issue. The above syntax returned values for all 14435 records but all county names seem to be wrong. The issue may be that USPS Table uses the same zip code many times for multiple cities due to the growth of ZIp Codes over the years. Therefore there is not a unique zip code in many cases. Any idea how to deal with that if that is what is causing my issue. -- Brian "OssieMac" wrote: Hi again Brian, Firstly I am not sure if File 1 and File 2 refers to separate worksheets in the same workbook or totally separate workbooks. Therefore I will give you formulas for both conditions. I am assuming from the column headers that you have given me that "Zip Code" is in column A in both the tables and "County" is in column B in both tables. If both tables are in the SAME workbook then assume that the worksheet name for the table with Consultant is Sheet1 and the lookup table is Sheet2. Enter the following formula in cell B2 in Sheet1 (the County column to be populated.) =VLOOKUP(A2,Sheet2!$A$2:$B$65536,2,FALSE) If the tables are in totally separate workbooks, then assuming that the lookup table is in Book2 then the formula will be like this:- =VLOOKUP(A2,[Book2]Sheet1!$A$2:$B$65536,2,FALSE) Copy the formula to the bottom of the data in the column. Note: You can select the range for the lookup during creation of the formula. After you enter =VLOOKUP( you can click on cell A2 to enter it in the formula. After you enter the comma after A2 you can change worksheets or workbooks and select the range for the table array. If the table array is not in absolute format immediately after you select it, then press F4 and it will insert the $ signs for you. Selecting the range is the best way to go because Excel handles the syntax for you and even inserts single quotes around workbook and worksheet names which have spaces in their names. -- Regards, OssieMac "bdehning" wrote: Of course I am confused. Trying to figure our formula you provided. =VLOOKUP(D2,Sheet2!$A$2:$B$27,2,FALSE) File 1 with Sheet - CONSULTANT with Columns and ROW 1 Header Zip Code, County, City, State, Consultant, SVC Office - 14435 total rows File 2 - with Sheet ZIP CODE with columns and Row 1 header Zip Code, County, City, State, A/C, FIPS, PREF?, Type - 65536 total rows File 1 Sheet Consultant has the County Field that needs to be filled out. Can you help with Formula based on this as I am not sure about D2 and array? Thanks. -- Brian "OssieMac" wrote: Hi Brian, You should be able to use Vlookup function. The Zip code and County will be the table array and do the lookup on the zip code and insert the column with the county. The Zipcode and County columns will need to be arranged so that the zipcode is the first column. Insert the formula in the column where you want the County. Assuming there are only 2 columns (Zip and County) in the table array then column index number in the formula will be 2 which represents the County column. Set the last parameter to false so that you only get exact matches and not the nearest match. Also note that the table array must be in absolute format with the $ signs in front of column and rows like the below sample. =VLOOKUP(D2,Sheet2!$A$2:$B$27,2,FALSE) In the above sample, cell D2 contains the zip code in the table to have the county codes inserted. Sheet2!$A$2:$B$27 is the table array with zip and county with zip in first column 2 is the second column of the zip/county table false says exact matches only -- Regards, OssieMac "bdehning" wrote: I have an Excel file with fields City, State, Zipcode and Consultant which has a limited number of records and another Excel File that has the fields City, State, Zipcode and County and includes all zip codes in the US. How do I go about adding/merging or using the second file to add the County field in the first file so that I can have the first file with Consultant show the appropriate County for each Zipcode listed? -- Brian |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
2 Files with Different Fields
Hi Brian,
The formula does not look right. Did you insert it in the worksheet where you want to place the County name? Because that is where it should be. =VLOOKUP(ZipCodes!A2,ZipCodes!$A$2:$B$65536,2,FALS E) A2 does not need a worksheet name in front of it. It should be in the County column of the worksheet where you want to insert the County names (that is the County column of the worksheet with Consultant, SVC Office - 14435 total rows.) You have A2 referring to the ZipCodes worksheet where you are looking up the codes. I would expect it to look like this:- =VLOOKUP(A2,ZipCodes!$A$2:$B$65536,2,FALSE) If you have multiple lisitings of Zip codes then it will find the first match only. Can you post a small sample of the lookup table where the zip codes are the same for a particular County. Include all the fields of the table. I am wondering if it might be possible to concatenate some fields like County, City, State if say you have multiple zip codes for a County but when you look at all three fields together the zip codes are unique. (Concatenation can be removed later) I am not fully conversant with your zip code system because I am Down Under in Australia. However, we do experience similar problems here. -- Regards, OssieMac "bdehning" wrote: Ok all have county names but all seem to be the wrong name. Here is what syntax had to be to get values! I was getting o and N/A previously. =VLOOKUP(ZipCodes!A2,ZipCodes!$A$2:$B$65536,2,FALS E) The problem I am having is that most Zip Codes are returning incorrect Counties. See anything wrong with Syntax that is causing my issue. The above syntax returned values for all 14435 records but all county names seem to be wrong. The issue may be that USPS Table uses the same zip code many times for multiple cities due to the growth of ZIp Codes over the years. Therefore there is not a unique zip code in many cases. Any idea how to deal with that if that is what is causing my issue. -- Brian "OssieMac" wrote: Hi again Brian, Firstly I am not sure if File 1 and File 2 refers to separate worksheets in the same workbook or totally separate workbooks. Therefore I will give you formulas for both conditions. I am assuming from the column headers that you have given me that "Zip Code" is in column A in both the tables and "County" is in column B in both tables. If both tables are in the SAME workbook then assume that the worksheet name for the table with Consultant is Sheet1 and the lookup table is Sheet2. Enter the following formula in cell B2 in Sheet1 (the County column to be populated.) =VLOOKUP(A2,Sheet2!$A$2:$B$65536,2,FALSE) If the tables are in totally separate workbooks, then assuming that the lookup table is in Book2 then the formula will be like this:- =VLOOKUP(A2,[Book2]Sheet1!$A$2:$B$65536,2,FALSE) Copy the formula to the bottom of the data in the column. Note: You can select the range for the lookup during creation of the formula. After you enter =VLOOKUP( you can click on cell A2 to enter it in the formula. After you enter the comma after A2 you can change worksheets or workbooks and select the range for the table array. If the table array is not in absolute format immediately after you select it, then press F4 and it will insert the $ signs for you. Selecting the range is the best way to go because Excel handles the syntax for you and even inserts single quotes around workbook and worksheet names which have spaces in their names. -- Regards, OssieMac "bdehning" wrote: Of course I am confused. Trying to figure our formula you provided. =VLOOKUP(D2,Sheet2!$A$2:$B$27,2,FALSE) File 1 with Sheet - CONSULTANT with Columns and ROW 1 Header Zip Code, County, City, State, Consultant, SVC Office - 14435 total rows File 2 - with Sheet ZIP CODE with columns and Row 1 header Zip Code, County, City, State, A/C, FIPS, PREF?, Type - 65536 total rows File 1 Sheet Consultant has the County Field that needs to be filled out. Can you help with Formula based on this as I am not sure about D2 and array? Thanks. -- Brian "OssieMac" wrote: Hi Brian, You should be able to use Vlookup function. The Zip code and County will be the table array and do the lookup on the zip code and insert the column with the county. The Zipcode and County columns will need to be arranged so that the zipcode is the first column. Insert the formula in the column where you want the County. Assuming there are only 2 columns (Zip and County) in the table array then column index number in the formula will be 2 which represents the County column. Set the last parameter to false so that you only get exact matches and not the nearest match. Also note that the table array must be in absolute format with the $ signs in front of column and rows like the below sample. =VLOOKUP(D2,Sheet2!$A$2:$B$27,2,FALSE) In the above sample, cell D2 contains the zip code in the table to have the county codes inserted. Sheet2!$A$2:$B$27 is the table array with zip and county with zip in first column 2 is the second column of the zip/county table false says exact matches only -- Regards, OssieMac "bdehning" wrote: I have an Excel file with fields City, State, Zipcode and Consultant which has a limited number of records and another Excel File that has the fields City, State, Zipcode and County and includes all zip codes in the US. How do I go about adding/merging or using the second file to add the County field in the first file so that I can have the first file with Consultant show the appropriate County for each Zipcode listed? -- Brian |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
2 Files with Different Fields
Actually I used:
=VLOOKUP(A2,ZipCodes!$A$2:$B$65536,2) and it worked fine. I did not need the false at the end at this point. I am not sure why I had the first part of the code messed up but with your help and others I now have it working just fine. Thanks -- Brian "OssieMac" wrote: Hi Brian, The formula does not look right. Did you insert it in the worksheet where you want to place the County name? Because that is where it should be. =VLOOKUP(ZipCodes!A2,ZipCodes!$A$2:$B$65536,2,FALS E) A2 does not need a worksheet name in front of it. It should be in the County column of the worksheet where you want to insert the County names (that is the County column of the worksheet with Consultant, SVC Office - 14435 total rows.) You have A2 referring to the ZipCodes worksheet where you are looking up the codes. I would expect it to look like this:- =VLOOKUP(A2,ZipCodes!$A$2:$B$65536,2,FALSE) If you have multiple lisitings of Zip codes then it will find the first match only. Can you post a small sample of the lookup table where the zip codes are the same for a particular County. Include all the fields of the table. I am wondering if it might be possible to concatenate some fields like County, City, State if say you have multiple zip codes for a County but when you look at all three fields together the zip codes are unique. (Concatenation can be removed later) I am not fully conversant with your zip code system because I am Down Under in Australia. However, we do experience similar problems here. -- Regards, OssieMac "bdehning" wrote: Ok all have county names but all seem to be the wrong name. Here is what syntax had to be to get values! I was getting o and N/A previously. =VLOOKUP(ZipCodes!A2,ZipCodes!$A$2:$B$65536,2,FALS E) The problem I am having is that most Zip Codes are returning incorrect Counties. See anything wrong with Syntax that is causing my issue. The above syntax returned values for all 14435 records but all county names seem to be wrong. The issue may be that USPS Table uses the same zip code many times for multiple cities due to the growth of ZIp Codes over the years. Therefore there is not a unique zip code in many cases. Any idea how to deal with that if that is what is causing my issue. -- Brian "OssieMac" wrote: Hi again Brian, Firstly I am not sure if File 1 and File 2 refers to separate worksheets in the same workbook or totally separate workbooks. Therefore I will give you formulas for both conditions. I am assuming from the column headers that you have given me that "Zip Code" is in column A in both the tables and "County" is in column B in both tables. If both tables are in the SAME workbook then assume that the worksheet name for the table with Consultant is Sheet1 and the lookup table is Sheet2. Enter the following formula in cell B2 in Sheet1 (the County column to be populated.) =VLOOKUP(A2,Sheet2!$A$2:$B$65536,2,FALSE) If the tables are in totally separate workbooks, then assuming that the lookup table is in Book2 then the formula will be like this:- =VLOOKUP(A2,[Book2]Sheet1!$A$2:$B$65536,2,FALSE) Copy the formula to the bottom of the data in the column. Note: You can select the range for the lookup during creation of the formula. After you enter =VLOOKUP( you can click on cell A2 to enter it in the formula. After you enter the comma after A2 you can change worksheets or workbooks and select the range for the table array. If the table array is not in absolute format immediately after you select it, then press F4 and it will insert the $ signs for you. Selecting the range is the best way to go because Excel handles the syntax for you and even inserts single quotes around workbook and worksheet names which have spaces in their names. -- Regards, OssieMac "bdehning" wrote: Of course I am confused. Trying to figure our formula you provided. =VLOOKUP(D2,Sheet2!$A$2:$B$27,2,FALSE) File 1 with Sheet - CONSULTANT with Columns and ROW 1 Header Zip Code, County, City, State, Consultant, SVC Office - 14435 total rows File 2 - with Sheet ZIP CODE with columns and Row 1 header Zip Code, County, City, State, A/C, FIPS, PREF?, Type - 65536 total rows File 1 Sheet Consultant has the County Field that needs to be filled out. Can you help with Formula based on this as I am not sure about D2 and array? Thanks. -- Brian "OssieMac" wrote: Hi Brian, You should be able to use Vlookup function. The Zip code and County will be the table array and do the lookup on the zip code and insert the column with the county. The Zipcode and County columns will need to be arranged so that the zipcode is the first column. Insert the formula in the column where you want the County. Assuming there are only 2 columns (Zip and County) in the table array then column index number in the formula will be 2 which represents the County column. Set the last parameter to false so that you only get exact matches and not the nearest match. Also note that the table array must be in absolute format with the $ signs in front of column and rows like the below sample. =VLOOKUP(D2,Sheet2!$A$2:$B$27,2,FALSE) In the above sample, cell D2 contains the zip code in the table to have the county codes inserted. Sheet2!$A$2:$B$27 is the table array with zip and county with zip in first column 2 is the second column of the zip/county table false says exact matches only -- Regards, OssieMac "bdehning" wrote: I have an Excel file with fields City, State, Zipcode and Consultant which has a limited number of records and another Excel File that has the fields City, State, Zipcode and County and includes all zip codes in the US. How do I go about adding/merging or using the second file to add the County field in the first file so that I can have the first file with Consultant show the appropriate County for each Zipcode listed? -- Brian |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I output csv files from Excel with quotes round text fields | Excel Discussion (Misc queries) | |||
How do I tie fields from tables of diferente files? | Excel Worksheet Functions | |||
Excel SHOULD NOT AUTO-CHANGE formated text fields to DATE FIELDS! | Excel Worksheet Functions | |||
using macro, how can i copy similar fields from other xls files? | Excel Discussion (Misc queries) | |||
using macro, how can i copy similar fields from other xls files? | Excel Discussion (Misc queries) |