Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging Data Into Spreadsheet
Have a question I hope that someone can answer. I have two excel workbooks
each with one sheet. Spreadsheet A contains solely a customer name in Column L. What I would like to do is import the data from Worksheet B into worksheet A if there is a match found. Worksheet B contains customer names, along with address, city, state, zip, phone, email address, etc, each in their own individual columns. Also its important that if no match is found that an error pops up. Is this easy to do or relatively hard? Appreciate any feedback and happy Friday :). |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging Data Into Spreadsheet
Icehearted wrote:
Have a question I hope that someone can answer. I have two excel workbooks each with one sheet. Spreadsheet A contains solely a customer name in Column L. What I would like to do is import the data from Worksheet B into worksheet A if there is a match found. Worksheet B contains customer names, along with address, city, state, zip, phone, email address, etc, each in their own individual columns. Also its important that if no match is found that an error pops up. Is this easy to do or relatively hard? Appreciate any feedback and happy Friday :). Easy to do with VLOOKUP. Assuming, that is, your customer names are spelled /exactly/ the same way in both sheets, including spaces and punctuation. Can you confirm that much? Also, it would be helpful if you can tell which column in workbook B has the customer name, and how many columns are used. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging Data Into Spreadsheet
Hi,
The customer names may or may not be an exact match. Can V-Lookup do an approximate match or truncated match. The company name (workbook B) is located in column n. "smartin" wrote: Icehearted wrote: Have a question I hope that someone can answer. I have two excel workbooks each with one sheet. Spreadsheet A contains solely a customer name in Column L. What I would like to do is import the data from Worksheet B into worksheet A if there is a match found. Worksheet B contains customer names, along with address, city, state, zip, phone, email address, etc, each in their own individual columns. Also its important that if no match is found that an error pops up. Is this easy to do or relatively hard? Appreciate any feedback and happy Friday :). Easy to do with VLOOKUP. Assuming, that is, your customer names are spelled /exactly/ the same way in both sheets, including spaces and punctuation. Can you confirm that much? Also, it would be helpful if you can tell which column in workbook B has the customer name, and how many columns are used. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging Data Into Spreadsheet
Strictly speaking VLOOKUP only does an exact match (but regardless of
text case). You can truncate the lookup value and the values in the lookup table, but this will return a truncated result. You can work around this little irritation by using INDEX and MATCH. But then I think you will realize another problem, which is that the more you truncate the lookup (in the hopes of catching a match), the less accurate it becomes. IME an exercise in matching text data from disparate sources usually requires a prelude cleansing step, the objective of which is to normalize the two lists as much as possible. E.g., by removing extraneous punctuation and extra spaces, and making sure the names are in a common format such as "Last, First". /Then/ you can proceed with the lookups and merging data. And then you manually review to ensure accuracy and perhaps recover some missed cases. Some things to think about: Do you have things like "SMITH, GEORGE" and "GEORGE SMITH"? How about "SMITH, GEORGE A." and "SMITH, GEORGE"? Are misspellings common? Maybe other have tips as well? Icehearted wrote: Hi, The customer names may or may not be an exact match. Can V-Lookup do an approximate match or truncated match. The company name (workbook B) is located in column n. "smartin" wrote: Icehearted wrote: Have a question I hope that someone can answer. I have two excel workbooks each with one sheet. Spreadsheet A contains solely a customer name in Column L. What I would like to do is import the data from Worksheet B into worksheet A if there is a match found. Worksheet B contains customer names, along with address, city, state, zip, phone, email address, etc, each in their own individual columns. Also its important that if no match is found that an error pops up. Is this easy to do or relatively hard? Appreciate any feedback and happy Friday :). Easy to do with VLOOKUP. Assuming, that is, your customer names are spelled /exactly/ the same way in both sheets, including spaces and punctuation. Can you confirm that much? Also, it would be helpful if you can tell which column in workbook B has the customer name, and how many columns are used. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging Data Into Spreadsheet
Hmmm.. exact match seems like the best solution at this point. I can
manually look up the ones that come back as no match. How would the formula look? Worksheet A (this is the sheet in which data needs to be looked up) Worksheet B (this is the sheet on which the data, if matched) needs to go The lookup would be done by company name strictly. Worksheet B has only a company name. I would take that company name and look it up in Worksheet A returning with it the other columns of information such as street address, city, state and zip. The company names are "generally" an exact match or not. For example, ABC Company, not Company ABC. Hope this makes sense :), thank you for all your comment. "smartin" wrote: Strictly speaking VLOOKUP only does an exact match (but regardless of text case). You can truncate the lookup value and the values in the lookup table, but this will return a truncated result. You can work around this little irritation by using INDEX and MATCH. But then I think you will realize another problem, which is that the more you truncate the lookup (in the hopes of catching a match), the less accurate it becomes. IME an exercise in matching text data from disparate sources usually requires a prelude cleansing step, the objective of which is to normalize the two lists as much as possible. E.g., by removing extraneous punctuation and extra spaces, and making sure the names are in a common format such as "Last, First". /Then/ you can proceed with the lookups and merging data. And then you manually review to ensure accuracy and perhaps recover some missed cases. Some things to think about: Do you have things like "SMITH, GEORGE" and "GEORGE SMITH"? How about "SMITH, GEORGE A." and "SMITH, GEORGE"? Are misspellings common? Maybe other have tips as well? Icehearted wrote: Hi, The customer names may or may not be an exact match. Can V-Lookup do an approximate match or truncated match. The company name (workbook B) is located in column n. "smartin" wrote: Icehearted wrote: Have a question I hope that someone can answer. I have two excel workbooks each with one sheet. Spreadsheet A contains solely a customer name in Column L. What I would like to do is import the data from Worksheet B into worksheet A if there is a match found. Worksheet B contains customer names, along with address, city, state, zip, phone, email address, etc, each in their own individual columns. Also its important that if no match is found that an error pops up. Is this easy to do or relatively hard? Appreciate any feedback and happy Friday :). Easy to do with VLOOKUP. Assuming, that is, your customer names are spelled /exactly/ the same way in both sheets, including spaces and punctuation. Can you confirm that much? Also, it would be helpful if you can tell which column in workbook B has the customer name, and how many columns are used. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Merging Data Into Spreadsheet
Here are a couple formulas you can try. In both cases I assumed company
names are in column A, Worksheet A has data in columns A:G, and the data starts in row 2. The simplest formula goes in cell B2 of Worksheet B and can be filled down: =VLOOKUP($A2,'Worksheet A'!$A:$G,2,FALSE) In cell C2 this would become =VLOOKUP($A2,'Worksheet A'!$A:$G,3,FALSE) ",2," becomes ",3," in order to fetch the next column from Worksheet A. The following more extensible version of the formula also goes in cell B2 of Worksheet B and can be filled both down and right: =VLOOKUP($A2,'Worksheet A'!$A:$G,COLUMNS($A:B),FALSE) In whichever method you choose, the result will be #N/A where no matching company name was found. Hope this helps! Icehearted wrote: Hmmm.. exact match seems like the best solution at this point. I can manually look up the ones that come back as no match. How would the formula look? Worksheet A (this is the sheet in which data needs to be looked up) Worksheet B (this is the sheet on which the data, if matched) needs to go The lookup would be done by company name strictly. Worksheet B has only a company name. I would take that company name and look it up in Worksheet A returning with it the other columns of information such as street address, city, state and zip. The company names are "generally" an exact match or not. For example, ABC Company, not Company ABC. Hope this makes sense :), thank you for all your comment. "smartin" wrote: Strictly speaking VLOOKUP only does an exact match (but regardless of text case). You can truncate the lookup value and the values in the lookup table, but this will return a truncated result. You can work around this little irritation by using INDEX and MATCH. But then I think you will realize another problem, which is that the more you truncate the lookup (in the hopes of catching a match), the less accurate it becomes. IME an exercise in matching text data from disparate sources usually requires a prelude cleansing step, the objective of which is to normalize the two lists as much as possible. E.g., by removing extraneous punctuation and extra spaces, and making sure the names are in a common format such as "Last, First". /Then/ you can proceed with the lookups and merging data. And then you manually review to ensure accuracy and perhaps recover some missed cases. Some things to think about: Do you have things like "SMITH, GEORGE" and "GEORGE SMITH"? How about "SMITH, GEORGE A." and "SMITH, GEORGE"? Are misspellings common? Maybe other have tips as well? Icehearted wrote: Hi, The customer names may or may not be an exact match. Can V-Lookup do an approximate match or truncated match. The company name (workbook B) is located in column n. "smartin" wrote: Icehearted wrote: Have a question I hope that someone can answer. I have two excel workbooks each with one sheet. Spreadsheet A contains solely a customer name in Column L. What I would like to do is import the data from Worksheet B into worksheet A if there is a match found. Worksheet B contains customer names, along with address, city, state, zip, phone, email address, etc, each in their own individual columns. Also its important that if no match is found that an error pops up. Is this easy to do or relatively hard? Appreciate any feedback and happy Friday :). Easy to do with VLOOKUP. Assuming, that is, your customer names are spelled /exactly/ the same way in both sheets, including spaces and punctuation. Can you confirm that much? Also, it would be helpful if you can tell which column in workbook B has the customer name, and how many columns are used. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
merging spreadsheet data | New Users to Excel | |||
Merging New Column Into Spreadsheet Each Month | Excel Discussion (Misc queries) | |||
Merging spreadsheet data | Excel Discussion (Misc queries) | |||
Spreadsheet merging problems | Excel Worksheet Functions | |||
Should Merging workbooks pick up new data or only edited data? | Excel Worksheet Functions |