Help with Macro: finding and copying data between worksheets

I'm working on a project for an extracurricular I'm involved in that requires manipulating a large Excel sheet with personal information of members. I'm only literate in the basics of Excel, but I really need help making a macro to automate the transfer of data for this project.


Basically, I have two worksheets, one with member names and blank contact info, and the other with names and contact info filled in. I need some way of automating the following:

1. Search for each listed name in the blank document
2. Copy the associated contact information from the completed doc to the blank doc

One issue is that the name format for the two documents is different, unfortunately. The blank doc has a column with full names (first middle last, suffix), the doc with contact info is formatting with three name columns: (first MI last), (suffix), and (last name). There are definitely non-unique last names.


I'm a newbie when it comes to macros and VBA, so hopefully you all can lend me a helping hand! Please let me know if you need more detail information. I can post a blank copy of the worksheets if necessary.


Thanks,
Neal

Hello
This could be a difficult task, as when people are entering text, they are inevitably inconsistent, prone to typing errors, mis-spelling etc. This is why its common practice to allocate a code rather than just use the name, especially where you are dealing with a lot of people. After all, with a common name, You could find two people called (e.g.) John Smith.
Looking at your (few) examples, there is a consistency in the two tables, in that each name starts with a matching value, and each also finishes with a matching value. As a result an algorithm based on that would match these examples correctly. Your first aim should be to insert the correct name from the "Has Contacts" into an extra column in the "Needs Contacts", so that this can be used to look up the correct row in "Has Contacts" to transfer across the correct contact data. Going forward I would recommend that you try to establish if these persons already have a coding system that you could adopt, or alternatively get them to use the same source for the names.
Take a look at your data overall, and see if you think in the immediate my idea would solve the problem, and then we can devise a suitable formula to match the first and last characters.

Thanks for your reply, Hercules. Your idea about assigning a unique code to each person certainly would have made me life a lot easier, however it's basically to late. Neither list is going to change/update drastically in the future, but if you think assigning codes to each existing person would help solve my problem (and not create a separate problem), then I'm open to suggestions on how to accomplish that. Having the codes may end up being helpful in the future if I have to manipulate these tables again.

Your idea about using an algorithm to match the first and last couple of values is great, and I think would work perfectly! Please let me know if you need a more detailed sample sheet than the one I provided, since it has relatively few names.

Thanks again!

I don't need any more data at the moment, provided its safe to assume that the majority follows the matching pattern we have identified. There may be some exceptions when we build the extra "key" column and we will need to sort those out manually.
I will get back to you a little later, I don't have a library of "off the shelf" formulae to reference.

Hello Simsmac
Sorry Ive been delayed a little. I hope the attachments will do what you need for a high percentage of the records. I couldn't relate the columns across the two workbooks, so Ive just added some to the "Needs" book, and populated them from the "Has" book.
Adjust the columns and the dimensions of the table array as required/to suit your data set. Make sure you load both workbooks.

This is amazing! Thank you so much. Lastly, I'm trying to implement this into the actual documents, but when I copy the formulas that refers to the "Has Contacts" document into my actual document, it gives me an error. I changed the formula to refer to the actual name of my "Has" document, but it says "Value not available error" and N/A for the contact info cells. Like I said, excel is not my forte. Any suggestions?

Hello simsmac
Its difficult transporting workbooks that are linked because the location of the (source data) book is recorded by Excel, and this often means that it can't adjust the links automatically when the two books are downloaded to a new location.
Firstly, can I ask:
1. You say that you are having problems copying the formulae into your actual document. Looking at the example workbooks I posted (#6), when you load them into your computer, do they show the correct information ?
2. In the examples you will see that Ive added an extra column in both sheets headed "key", that join together the first and last names. These keys are used to match the records in the two books. Have you managed to add these keys OK in your actual documents? If you have, then make sure that the Key column in the "Has Contacts" book is also the first column in Array table defined in your VLOOKUP formulae. In the example LOOKUP has Cols A to J for the Array Table, and the keys are in Col A of that table. If you don't get that right, the formulae will report errors.

If checking the above doesn't fix the problems, you will need to give me exact information about your actual documents
a) Workbook Names
b) Sheet Names
c) List of Col Codes and Labels used in each book. (Use the same labels for like data, so I know where to extract from/To.
d) The columns and the number of rows in the actual "Has Contacts" sheet used for your data.

I can then work out the correct formulae and post as text for you.

I think I have figured out the issue... The formula necessitates that both lists of names are in the exact same order, and that "Has Contacts" doesn't have any names the "Needs Contacts" list does not. If this is indeed the issue, it's my mistake for not creating templates that reflected this. Putting the two documents into the same order is a snap, but I'm guessing the issue will be that the "Has Contacts" list has many names that the "Needs Contacts" list does not.

I got the formula to work using my document, by making sure the first couple of names are the same and in the same order on my lists, so I know the formula works.

Hello Simsmac
It should not matter what order the records are in, or that you have fewer records in the "Needs" workbook. If your formulae are correct and the keys built are unique everything should be OK.
Can you post the two workbooks as they now are, showing just the following:
1. Column Labels for each column used. (In the "Needs" book, make sure cols to be taken from "Has" are given the same label as in "has")
2. In each Book create one example on Row 2 including the formulae you have. Make sure that the First and Last Names in both books are the same, so that the data should extract correctly from "Has" to "Needs".
3. If you like, give me the number of entries that you have in the real "Has" sheet, so I can dimension the table correctly if you haven't done this. If not I'll make an estimate.

Don't worry if the formulae don't work. As long as I can understand where everything goes I should be able to fix it.

Another point I omitted in #10:
If you have entries in the "Needs" book that don't exist in "Has", then VLOOKUP returns a default value (e.g 0 or #N/A) to mean "Not Found". This might be due to a data error, or that the entry is genuinely not in "Has".
If the latter, you can override the default using IFERROR(.... [Office 2007 or later] otherwise the dreaded IF(ISERROR(...

Ah, I was being stupid and didn't change the range fro the table array value. Now it's working great! Unfortunately, my documents are both a little quirky, so it hasn't matched all the names (some don't have matches, and some have inconsistent names). I'll have to sort through it and fix the names. Thankfully, it matched about half of them for me.

I really appreciate your help with this! Your solution works perfectly, and I'll most likely use it again in the future.

Glad I was able to help!