Replacing data

[Bkana]

Hello all,

If this is not the correct forum - please re-direct me.

I have two Excel files. One file contains information on roughly 10,000
people, including name, address, phone number, and a person id, the person id
being the unique identifier for each person. In this file the phone number
field contains a number (NOT THE ACTUAL PHONE NUMBER) that is like a pointer
or place-holder for the actual phone number.

To find what that person's phone number is I have to "link out" to the other
excel file, look up that place-holder number (in field #1) , and get the
phone number( in field #2), respectively. I would like to replace that
"place-holder" number with the actual phone number that belongs to that
person in that first file. How can I do this?

Will provide more info, if need be,

Thank you,
Bill

[tim m]

Have you used VLOOKUP before? I would insert a column into our sheet and use
VLOOKUP to find the proper phone number from the other sheet. Then i would
copy and paste special....values over top of the vlookup formula column.
this should give you a column with the phone numbers, you could then delete
the column with the indicator number from your main sheet if you wish.

(Of course always make a back up of your files before doing major data
manipulation.)

"Bkana" wrote:

>
> Hello all,
>
> If this is not the correct forum - please re-direct me.
>
> I have two Excel files. One file contains information on roughly 10,000
> people, including name, address, phone number, and a person id, the person id
> being the unique identifier for each person. In this file the phone number
> field contains a number (NOT THE ACTUAL PHONE NUMBER) that is like a pointer
> or place-holder for the actual phone number.
>
> To find what that person's phone number is I have to "link out" to the other
> excel file, look up that place-holder number (in field #1) , and get the
> phone number( in field #2), respectively. I would like to replace that
> "place-holder" number with the actual phone number that belongs to that
> person in that first file. How can I do this?
>
> Will provide more info, if need be,
>
> Thank you,
> Bill

[Toppers]

Bill,
One way is add an extra ("helper") column in your main file and
then use VLOOKUP to retrieve the actual telehone number.

The VLOOKUP statement would be of the form:

=VLOOKUP(reference#,[Telephone.xls]data!A1:b1000,2,false)

Where <reference#> is cell containing this data e.g. E2
<Telephone.xls> is your telephone data w/book
<Data> is sheet contain data which is in columns A1 to B100, (reference
number & telephone number)

Put this formula in your helper column and copy down

If successful, copy/past special=>values and replace/delete your reference
number column.

HTH

"Bkana" wrote:

>
> Hello all,
>
> If this is not the correct forum - please re-direct me.
>
> I have two Excel files. One file contains information on roughly 10,000
> people, including name, address, phone number, and a person id, the person id
> being the unique identifier for each person. In this file the phone number
> field contains a number (NOT THE ACTUAL PHONE NUMBER) that is like a pointer
> or place-holder for the actual phone number.
>
> To find what that person's phone number is I have to "link out" to the other
> excel file, look up that place-holder number (in field #1) , and get the
> phone number( in field #2), respectively. I would like to replace that
> "place-holder" number with the actual phone number that belongs to that
> person in that first file. How can I do this?
>
> Will provide more info, if need be,
>
> Thank you,
> Bill

[Bkana]

Hi Tim,

I have not used VLOOKUP before, could you give me an example using the info
I have provided below?

Excel file 1 Excel file 2


personid phoneid phoneid phone
number
1 10 10
(703)111-2222
2 20 20
(301)222-3333
3 30 30
(401)222-4444

The phoneid field in Excel file 1 has the pointer I need to replace with
it's corresponding data in Excel file 2, 10 would become (703)111-2222, 20
would become (301)222-3333, and so on. Also, these are two separate files -
does that matter? Would it be easier if I just copied the data from Excel
file 2 and made a new sheet (paste) in Excel file 1 to work from? Please
excuse the inexperience, but I am a Network Engineer and don't use Excel too
often.

Thank you very much for your response and help with this!


"tim m" wrote:

> Have you used VLOOKUP before? I would insert a column into our sheet and use
> VLOOKUP to find the proper phone number from the other sheet. Then i would
> copy and paste special....values over top of the vlookup formula column.
> this should give you a column with the phone numbers, you could then delete
> the column with the indicator number from your main sheet if you wish.
>
> (Of course always make a back up of your files before doing major data
> manipulation.)
>
> "Bkana" wrote:
>
> >
> > Hello all,
> >
> > If this is not the correct forum - please re-direct me.
> >
> > I have two Excel files. One file contains information on roughly 10,000
> > people, including name, address, phone number, and a person id, the person id
> > being the unique identifier for each person. In this file the phone number
> > field contains a number (NOT THE ACTUAL PHONE NUMBER) that is like a pointer
> > or place-holder for the actual phone number.
> >
> > To find what that person's phone number is I have to "link out" to the other
> > excel file, look up that place-holder number (in field #1) , and get the
> > phone number( in field #2), respectively. I would like to replace that
> > "place-holder" number with the actual phone number that belongs to that
> > person in that first file. How can I do this?
> >
> > Will provide more info, if need be,
> >
> > Thank you,
> > Bill

[Bkana]

Please excuse the formatting of my last post. The phone numbers should be
under the phone number field in Excel file 2. as well should the word
"number" be right after the word "phone".

Also, to verify: Is reference# the first cell of the new helper colum I
created in the first Excel file correct?.

"Toppers" wrote:

> Bill,
> One way is add an extra ("helper") column in your main file and
> then use VLOOKUP to retrieve the actual telehone number.
>
> The VLOOKUP statement would be of the form:
>
> =VLOOKUP(reference#,[Telephone.xls]data!A1:b1000,2,false)
>
> Where <reference#> is cell containing this data e.g. E2
> <Telephone.xls> is your telephone data w/book
> <Data> is sheet contain data which is in columns A1 to B100, (reference
> number & telephone number)
>
> Put this formula in your helper column and copy down
>
> If successful, copy/past special=>values and replace/delete your reference
> number column.
>
> HTH
>
> "Bkana" wrote:
>
> >
> > Hello all,
> >
> > If this is not the correct forum - please re-direct me.
> >
> > I have two Excel files. One file contains information on roughly 10,000
> > people, including name, address, phone number, and a person id, the person id
> > being the unique identifier for each person. In this file the phone number
> > field contains a number (NOT THE ACTUAL PHONE NUMBER) that is like a pointer
> > or place-holder for the actual phone number.
> >
> > To find what that person's phone number is I have to "link out" to the other
> > excel file, look up that place-holder number (in field #1) , and get the
> > phone number( in field #2), respectively. I would like to replace that
> > "place-holder" number with the actual phone number that belongs to that
> > person in that first file. How can I do this?
> >
> > Will provide more info, if need be,
> >
> > Thank you,
> > Bill

[Toppers]

If "phoneid" in File 1 is in cell B2 then in row 2 of your helper column put:

=VLOOKUP(B2,[File2]Sheet1!$A:$B,2,False)

Where <File2> is the name of your <EXCEL File 2> and the PhoneID & Phone
number are in Columns A & B of this file on Sheet1.

Copy this formula down until you reach the end of the list in file 1.

HTH



"Bkana" wrote:

> Please excuse the formatting of my last post. The phone numbers should be
> under the phone number field in Excel file 2. as well should the word
> "number" be right after the word "phone".
>
> Also, to verify: Is reference# the first cell of the new helper colum I
> created in the first Excel file correct?.
>
> "Toppers" wrote:
>
> > Bill,
> > One way is add an extra ("helper") column in your main file and
> > then use VLOOKUP to retrieve the actual telehone number.
> >
> > The VLOOKUP statement would be of the form:
> >
> > =VLOOKUP(reference#,[Telephone.xls]data!A1:b1000,2,false)
> >
> > Where <reference#> is cell containing this data e.g. E2
> > <Telephone.xls> is your telephone data w/book
> > <Data> is sheet contain data which is in columns A1 to B100, (reference
> > number & telephone number)
> >
> > Put this formula in your helper column and copy down
> >
> > If successful, copy/past special=>values and replace/delete your reference
> > number column.
> >
> > HTH
> >
> > "Bkana" wrote:
> >
> > >
> > > Hello all,
> > >
> > > If this is not the correct forum - please re-direct me.
> > >
> > > I have two Excel files. One file contains information on roughly 10,000
> > > people, including name, address, phone number, and a person id, the person id
> > > being the unique identifier for each person. In this file the phone number
> > > field contains a number (NOT THE ACTUAL PHONE NUMBER) that is like a pointer
> > > or place-holder for the actual phone number.
> > >
> > > To find what that person's phone number is I have to "link out" to the other
> > > excel file, look up that place-holder number (in field #1) , and get the
> > > phone number( in field #2), respectively. I would like to replace that
> > > "place-holder" number with the actual phone number that belongs to that
> > > person in that first file. How can I do this?
> > >
> > > Will provide more info, if need be,
> > >
> > > Thank you,
> > > Bill