Need to merge two lists(worksheets) into one

[Starfishjoe]

I have a list of names with addresses.
I have a separate list of names with Phone numbers.

These two lists are in separate workbooks. I can copy the work sheet of one
into the the same workbook of the other, but I want to find a function,
create a formula or write a VBA macro that will search for the phone number
of a person in one and copy it to a new field called "phone" next to the
address field of that person in the other work sheet.

The two lists may or may not have common data on them.
For example
John Doe is on both lists. He has a phone number on one and an address on
the other. My script needs to copy the phone number from the phone list and
place it next to the address.
Jane Jones has given her name and phone number but not an address. I will
skip her this time since she is only on one list.

Susan Smith has an address but no phone number. I will skip her because she
is only listed on one list.

There may be other scenarios, but this will get me started and solve the
immediate problem.

(If the name is found on both lists, merge phone on one to the address list
on the other.)

I think this can be done with a formula in the cells, but it has been a long
time since i figured one of these out.
Can anyone help?

 

[anon]

I have a list of names with addresses.
I have a separate list of names with Phone numbers.

These two lists are in separate workbooks. I can copy the work sheet of one
into the the same workbook of the other, but I want to find a function,
create a formula or write a VBA macro that will search for the phone number
of a person in one and copy it to a new field called "phone" next to the
address field of that person in the other work sheet.

The two lists may or may not have common data on them.
For example
John Doe is on both lists. He has a phone number on one and an address on
the other. My script needs to copy the phone number from the phone list and
place it next to the address.
Jane Jones has given her name and phone number but not an address. I will
skip her this time since she is only on one list.

Susan Smith has an address but no phone number. I will skip her because she
is only listed on one list.

There may be other scenarios, but this will get me started and solve the
immediate problem.

(If the name is found on both lists, merge phone on one to the address list
on the other.)

Assume you have two worksheets, Sheet1 and Sheet2. Assume your names
are in column A, your Sheet1 has your address in column B and Sheet2
has your phone number in column B.

In cell C1 of Sheet 1 put the following:

=INDEX(Sheet2!$B$1:$B$10000,MATCH(A1,Sheet2!$A$1:$A$10000,0),1)

Then copy that down to every row in Column C

Jim

 

[elby]

I have a list of names with addresses.
I have a separate list of names with Phone numbers.

These two lists are in separate workbooks. I can copy the work sheet of one
into the the same workbook of the other, but I want to find a function,
create a formula or write a VBA macro that will search for the phone number
of a person in one and copy it to a new field called "phone" next to the
address field of that person in the other work sheet.

The two lists may or may not have common data on them.
For example
John Doe is on both lists. He has a phone number on one and an address on
the other. My script needs to copy the phone number from the phone list and
place it next to the address.
Jane Jones has given her name and phone number but not an address. I will
skip her this time since she is only on one list.

Susan Smith has an address but no phone number. I will skip her because she
is only listed on one list.

There may be other scenarios, but this will get me started and solve the
immediate problem.

(If the name is found on both lists, merge phone on one to the address list
on the other.)

I think this can be done with a formula in the cells, but it has been a long
time since i figured one of these out.
Can anyone help?

Create a QueryTable ( Data > External Data, ecc ) with the source
workbooks closed. The tables ( Named Intervals ) must be related on
the name field with an Inner Join
Elio

 

[shanedevenshire]

I have a list of names with addresses.
I have a separate list of names with Phone numbers.

These two lists are in separate workbooks. I can copy the work sheet of one
into the the same workbook of the other, but I want to find a function,
create a formula or write a VBA macro that will search for the phone number
of a person in one and copy it to a new field called "phone" next to the
address field of that person in the other work sheet.

The two lists may or may not have common data on them.
For example
John Doe is on both lists. He has a phone number on one and an address on
the other. My script needs to copy the phone number from the phone list and
place it next to the address.
Jane Jones has given her name and phone number but not an address. I will
skip her this time since she is only on one list.

Susan Smith has an address but no phone number. I will skip her because she
is only listed on one list.

There may be other scenarios, but this will get me started and solve the
immediate problem.

(If the name is found on both lists, merge phone on one to the address list
on the other.)

I think this can be done with a formula in the cells, but it has been a long
time since i figured one of these out.
Can anyone help?

Question - suppose you have two people in both data sets with the name
John Smith, how do you distinguish them?

You need a unique identifier such as Social Security Number or
Employee ID.

Shane Devenshire

 

[Starfishjoe]

Question - suppose you have two people in both data sets with the name
John Smith, how do you distinguish them?

Answer:
This will not be a problem. It will be assumed they are the same person. If
I do run accross this situation, though, I'll re-think this. Currently
there are no SSN's or ID numbers-- just names on the 2 pre-existing
worksheets.

My main goal was to try to get both, the telephone data, and the address
data on the same sheet. I haven't tried it yet, but I think Anon's solution
above, may be closest to what I am looking for. I'll know for sure once I
am able to try it.

Thank you for taking the time to ask though.
Sincerely,
StarfishJoe