Merging two lists

B

Bill Clark

I need to merge two address lists with differing information. Some addresses
in list A are also in list B. List B includes a column with phone numbers
and A does not. How do I merge the two adding the phone number to the
correct address rows and keep all the other info intact?
 
J

JulieD

Hi Bill

so all the addresses are in list A, but list B has additional information
that list A doesn't have?

if so you can use the VLOOKUP to "add" the data from list B to list A
(assuming your spreadsheet is set up in a useable format)

e.g.
list A
.....A............B
1..Name....Address

list B
......A........B.......C
1...Name...Address...Phone

add another column into list A
and type the following formula (assumes list B is on sheet2, adjust as
necessary)
=VLOOKUP(A1,Sheet2!$A$1:$C$1000,3,0)

this looks up the name in column A of list A, finds a match in column A of
list B and then returns the phone number into the new column in list A.

Note: this assumes all names are unique (you might want to lookup on column
B instead of column A)
(to suppress the #NA error you can add the following
=IF(ISNA(VLOOKUP(A1,Sheet2!$A$1:$C$1000,3,0),"",VLOOKUP(A1,Sheet2!$A$1:$C$1000,3,0))

once you've got the phone numbers in column C of list A, you can do a copy /
edit - paste special - values to change them from being a formula to a
value.

hope this helps
cheers
JulieD
 
B

Bill Clark

Hi JulieD:

This information hits the nail right on the head. Thank you for the prompt
posting.

have a great day

Bill
 
B

basstbone

I have a similar question about merging 2 lists on two separate worksheets(ws).
1st worksheet is for repaired equipment
2nd worksheet is for missing equipment.
3rd worksheet is a report listing contents of worksheet 1&2...easy enough
for me using =!'ws1'a1 but things get weird when trying to add to these list
or delete items on these list.
#ref errors when deleting...which I can work around by clearing contents and
moving the data on ws1&ws2
When I add rows to these list, the 3rd ws isn't able to report these because
of the formula I'm using...there must be a better formula for me to use right?

basstbone
 
B

basstbone

argh!! I can't edit my previous post!
current formula..
=IF(MISSING!A5=0,"",MISSING!A5)
going to try this below
=IF(MISSING!$A$5=0,"",MISSING!$A$5)
 
Top