Replacing values based on a set list
 

Hi Could anyone help me with this one: -

I have an Excel workbook with two worksheets: sheet1 and sheet2.

On sheet1, I have 2 columns of data:-
Column 1 - CityID
Column2 - CityName

On sheet2, I have a list of 500 office locations, some of which are in the
same city. Sheet2 has several columns, one of which is the city location of
the office.

I am trying to prepare the Excel sheet ready to import into Access and want
to replace each instance of the city location listed on sheet2 with the
CityID number from sheet1 where the city is matched. For example: -

Sheet 1
CityID CityName
1 Aberdeen

Sheet2
In the city column, replace all instances of Aberdeen with a 1

Can anyone point me in the right direction of how to go about this?

Thanks for all and any help!

Kindest Regards

Lee

Sounds like you need to use VLOOKUP, then paste the
CityID over the CityName. To learn how to use it, see:

http://www.contextures.com/xlFunctions02.html

HTH
Jason
Atlanta, GA

-----Original Message-----
Hi Could anyone help me with this one: -

I have an Excel workbook with two worksheets: sheet1 and
sheet2.

On sheet1, I have 2 columns of data:-
Column 1 - CityID
Column2 - CityName

On sheet2, I have a list of 500 office locations, some
of which are in the
same city. Sheet2 has several columns, one of which is
the city location of
the office.

I am trying to prepare the Excel sheet ready to import
into Access and want
to replace each instance of the city location listed on
sheet2 with the
CityID number from sheet1 where the city is matched. For
example: -

Sheet 1
CityID CityName
1 Aberdeen

Sheet2
In the city column, replace all instances of Aberdeen
with a 1

Can anyone point me in the right direction of how to go
about this?

Thanks for all and any help!

Kindest Regards

Lee
.

Jason,

You are a re star! This worked like a dream!!

Thanks for your help, you saved me hours!!

Regards

Lee

"Jason Morin" wrote:

Sounds like you need to use VLOOKUP, then paste the
CityID over the CityName. To learn how to use it, see:

http://www.contextures.com/xlFunctions02.html

HTH
Jason
Atlanta, GA

-----Original Message-----
Hi Could anyone help me with this one: -

I have an Excel workbook with two worksheets: sheet1 and
sheet2.

On sheet1, I have 2 columns of data:-
Column 1 - CityID
Column2 - CityName

On sheet2, I have a list of 500 office locations, some
of which are in the
same city. Sheet2 has several columns, one of which is
the city location of
the office.

I am trying to prepare the Excel sheet ready to import
into Access and want
to replace each instance of the city location listed on
sheet2 with the
CityID number from sheet1 where the city is matched. For
example: -

Sheet 1
CityID CityName
1 Aberdeen

Sheet2
In the city column, replace all instances of Aberdeen
with a 1

Can anyone point me in the right direction of how to go
about this?

Thanks for all and any help!

Kindest Regards

Lee
.

I only pointed you to one of the better Excel websites
out there. Thank Debra Dalgleish.
Jason

-----Original Message-----
Jason,

You are a re star! This worked like a dream!!

Thanks for your help, you saved me hours!!

Regards

Lee

"Jason Morin" wrote:

Sounds like you need to use VLOOKUP, then paste the
CityID over the CityName. To learn how to use it, see:

http://www.contextures.com/xlFunctions02.html

HTH
Jason
Atlanta, GA

-----Original Message-----
Hi Could anyone help me with this one: -

I have an Excel workbook with two worksheets: sheet1
and
sheet2.

On sheet1, I have 2 columns of data:-
Column 1 - CityID
Column2 - CityName

On sheet2, I have a list of 500 office locations,
some
of which are in the
same city. Sheet2 has several columns, one of which
is
the city location of
the office.

I am trying to prepare the Excel sheet ready to
import
into Access and want
to replace each instance of the city location listed
on
sheet2 with the
CityID number from sheet1 where the city is matched.
For
example: -

Sheet 1
CityID CityName
1 Aberdeen

Sheet2
In the city column, replace all instances of Aberdeen
with a 1

Can anyone point me in the right direction of how to
go
about this?

Thanks for all and any help!

Kindest Regards

Lee
.


.