| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find and copy data from 2 worksheets
I have two worksheets; one with company names and balance amounts, the other
with company names and addresses. I want to merge these together. Not all of the companies on the balance sheet are on the addresses sheet. How can I get these to come together? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find and copy data from 2 worksheets
Hallo Kclyn
Assuming company names and amounts in sheet1, columns A and B, company names and adresses in sheet2, columns A and B, titles in line 1 and data starting in line 2, and merged result in sheet3, balance amounts in Column B and adresses in Column C: 1. Copy company names from sheet1 to column A, sheet3 2. sheet3, B2, insert formula: =IF(ISERROR(VLOOKUP($A2;SHEET1!$A$2:$B$100;2;0));" ";VLOOKUP($A2;SHEET1!$A$2:$B$100;2;0)) 3. sheet3, C2, insert formula: =IF(ISERROR(VLOOKUP($A2;SHEET2!$A$2:$B$100;2;0));" ";VLOOKUP($A2;SHEET2!$A$2:$B$100;2;0)) Adust ranges as required an copy formulas down to last company name. -- Regards Joachim "Kclyn" wrote: I have two worksheets; one with company names and balance amounts, the other with company names and addresses. I want to merge these together. Not all of the companies on the balance sheet are on the addresses sheet. How can I get these to come together? |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find and copy data from 2 worksheets
Thank you for the help, but when I insert your formula in B2 it is showing
invalid. Can you please advise? "Joachim" wrote: Hallo Kclyn Assuming company names and amounts in sheet1, columns A and B, company names and adresses in sheet2, columns A and B, titles in line 1 and data starting in line 2, and merged result in sheet3, balance amounts in Column B and adresses in Column C: 1. Copy company names from sheet1 to column A, sheet3 2. sheet3, B2, insert formula: =IF(ISERROR(VLOOKUP($A2;SHEET1!$A$2:$B$100;2;0));" ";VLOOKUP($A2;SHEET1!$A$2:$B$100;2;0)) 3. sheet3, C2, insert formula: =IF(ISERROR(VLOOKUP($A2;SHEET2!$A$2:$B$100;2;0));" ";VLOOKUP($A2;SHEET2!$A$2:$B$100;2;0)) Adust ranges as required an copy formulas down to last company name. -- Regards Joachim "Kclyn" wrote: I have two worksheets; one with company names and balance amounts, the other with company names and addresses. I want to merge these together. Not all of the companies on the balance sheet are on the addresses sheet. How can I get these to come together? |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find and copy data from 2 worksheets
Hallo Kclyn,
Oops, my apologies. Please replace all semicolons between the formula arguments with commas. -- Regards Joachim "Kclyn" wrote: Thank you for the help, but when I insert your formula in B2 it is showing invalid. Can you please advise? "Joachim" wrote: Hallo Kclyn Assuming company names and amounts in sheet1, columns A and B, company names and adresses in sheet2, columns A and B, titles in line 1 and data starting in line 2, and merged result in sheet3, balance amounts in Column B and adresses in Column C: 1. Copy company names from sheet1 to column A, sheet3 2. sheet3, B2, insert formula: =IF(ISERROR(VLOOKUP($A2;SHEET1!$A$2:$B$100;2;0));" ";VLOOKUP($A2;SHEET1!$A$2:$B$100;2;0)) 3. sheet3, C2, insert formula: =IF(ISERROR(VLOOKUP($A2;SHEET2!$A$2:$B$100;2;0));" ";VLOOKUP($A2;SHEET2!$A$2:$B$100;2;0)) Adust ranges as required an copy formulas down to last company name. -- Regards Joachim "Kclyn" wrote: I have two worksheets; one with company names and balance amounts, the other with company names and addresses. I want to merge these together. Not all of the companies on the balance sheet are on the addresses sheet. How can I get these to come together? |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find and copy data from 2 worksheets
I fixed all of the semicolons, but it is still giving me an N/A value. Any
other help? "Joachim" wrote: Hallo Kclyn, Oops, my apologies. Please replace all semicolons between the formula arguments with commas. -- Regards Joachim "Kclyn" wrote: Thank you for the help, but when I insert your formula in B2 it is showing invalid. Can you please advise? "Joachim" wrote: Hallo Kclyn Assuming company names and amounts in sheet1, columns A and B, company names and adresses in sheet2, columns A and B, titles in line 1 and data starting in line 2, and merged result in sheet3, balance amounts in Column B and adresses in Column C: 1. Copy company names from sheet1 to column A, sheet3 2. sheet3, B2, insert formula: =IF(ISERROR(VLOOKUP($A2;SHEET1!$A$2:$B$100;2;0));" ";VLOOKUP($A2;SHEET1!$A$2:$B$100;2;0)) 3. sheet3, C2, insert formula: =IF(ISERROR(VLOOKUP($A2;SHEET2!$A$2:$B$100;2;0));" ";VLOOKUP($A2;SHEET2!$A$2:$B$100;2;0)) Adust ranges as required an copy formulas down to last company name. -- Regards Joachim "Kclyn" wrote: I have two worksheets; one with company names and balance amounts, the other with company names and addresses. I want to merge these together. Not all of the companies on the balance sheet are on the addresses sheet. How can I get these to come together? |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find and copy data from 2 worksheets
N/A means the formula doesn't find some data. For any further comment I would
need to see examples of the first lines of your sheets. -- Regards Joachim "Kclyn" wrote: I fixed all of the semicolons, but it is still giving me an N/A value. Any other help? "Joachim" wrote: Hallo Kclyn, Oops, my apologies. Please replace all semicolons between the formula arguments with commas. -- Regards Joachim "Kclyn" wrote: Thank you for the help, but when I insert your formula in B2 it is showing invalid. Can you please advise? "Joachim" wrote: Hallo Kclyn Assuming company names and amounts in sheet1, columns A and B, company names and adresses in sheet2, columns A and B, titles in line 1 and data starting in line 2, and merged result in sheet3, balance amounts in Column B and adresses in Column C: 1. Copy company names from sheet1 to column A, sheet3 2. sheet3, B2, insert formula: =IF(ISERROR(VLOOKUP($A2;SHEET1!$A$2:$B$100;2;0));" ";VLOOKUP($A2;SHEET1!$A$2:$B$100;2;0)) 3. sheet3, C2, insert formula: =IF(ISERROR(VLOOKUP($A2;SHEET2!$A$2:$B$100;2;0));" ";VLOOKUP($A2;SHEET2!$A$2:$B$100;2;0)) Adust ranges as required an copy formulas down to last company name. -- Regards Joachim "Kclyn" wrote: I have two worksheets; one with company names and balance amounts, the other with company names and addresses. I want to merge these together. Not all of the companies on the balance sheet are on the addresses sheet. How can I get these to come together? |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find and copy data from 2 worksheets
Thank you for the help, but when I insert the formula into B2 it shows
invalid. Can you please advise? "Kclyn" wrote: I have two worksheets; one with company names and balance amounts, the other with company names and addresses. I want to merge these together. Not all of the companies on the balance sheet are on the addresses sheet. How can I get these to come together? |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find and copy data from 2 worksheets
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Urgent:Find match between two worksheets and copy to another sheet | Excel Worksheet Functions | |||
| Copy Data between worksheets | Excel Discussion (Misc queries) | |||
| how do i find data in several worksheets at one time | Excel Discussion (Misc queries) | |||
| Find and copy data | Excel Worksheet Functions | |||
| How do I find data in separate worksheets and normalise? | Excel Worksheet Functions | |||
Hybrid Mode
