Merging Data Into Spreadsheet

[Icehearted]

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 .

 

[smartin]

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.

 

[Icehearted]

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]

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]

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]

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!