2 Files with Different Fields

[bdehning]

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?

 

[OssieMac]

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

 

[bdehning]

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.

 

[OssieMac]

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.

 

[bdehning]

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,FALSE)

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

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

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.

 

[OssieMac]

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,FALSE)

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

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,FALSE)

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

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

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


:

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


:

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?

 

[bdehning]

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

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,FALSE)

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

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,FALSE)

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

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


:

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


:

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


:

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?