Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Merging two lists
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? |
#2
|
|||
|
|||
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),"",VL OOKUP(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 "Bill Clark" wrote in message ... 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? |
#3
|
|||
|
|||
Hi Bill
thanks for the feedback, do post back if you get stuck. Cheers julieD "Bill Clark" wrote in message ... Hi JulieD: This information hits the nail right on the head. Thank you for the prompt posting. have a great day Bill "JulieD" wrote: 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),"",VL OOKUP(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 "Bill Clark" wrote in message ... 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? |
#4
|
|||
|
|||
Hi JulieD:
This information hits the nail right on the head. Thank you for the prompt posting. have a great day Bill "JulieD" wrote: 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),"",VL OOKUP(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 "Bill Clark" wrote in message ... 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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging two lists
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 -- where''''''''d my sig go to? :P "JulieD" wrote: Hi Bill thanks for the feedback, do post back if you get stuck. Cheers julieD "Bill Clark" wrote in message ... Hi JulieD: This information hits the nail right on the head. Thank you for the prompt posting. have a great day Bill "JulieD" wrote: 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),"",VL OOKUP(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 "Bill Clark" wrote in message ... 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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging two lists
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) -- where''''''''d my sig go to? :P "basstbone" wrote: 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 -- where''''''''d my sig go to? :P "JulieD" wrote: Hi Bill thanks for the feedback, do post back if you get stuck. Cheers julieD "Bill Clark" wrote in message ... Hi JulieD: This information hits the nail right on the head. Thank you for the prompt posting. have a great day Bill "JulieD" wrote: 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),"",VL OOKUP(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 "Bill Clark" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I compare two lists of names in excel? | Excel Discussion (Misc queries) | |||
Creating Combinations from Two Lists | Excel Discussion (Misc queries) | |||
Data Validation Lists | Links and Linking in Excel | |||
Aligning Two Lists in Excel | Excel Discussion (Misc queries) | |||
comapre two lists | Excel Discussion (Misc queries) |