[macgirl27]

I need to write a macro that will copy data from one spreadsheet into a separate spreadsheet (not in the same file). Here is the scenario...

On the first spreadsheet I have a customer's name in one column and a customer's address in another column. On a separate spreadsheet a have those same customer's names without the addresses. On the second spreadsheet the customer's name may be listed more than once. What I need to do is add the corresponding address to the second spreadsheet beside the name of the customer (identical data from both spreadsheets). The reason copy and paste won't work is because the customer may appear numerous times on the second spreadsheet and their are approximately 50,000 rows of data.

The macro needs to basically say IF Spreadsheet 1, Column A (customer) equals Spreadsheet 2, Column G (customer) then ADD/REPLACE info from Spreadsheet 1, Column B (address) to Spreadsheet 2, Column H (currently blank). I just can't figure out how to make it do that. ANY HELP ANYONE CAN GIVE IS APPRECIATED!

[Squashman]

Might want to ask the moderators to move this thread to the Business Applications section. That is where they deal with most of these types of questions.

[slurpee55]

You don't really need a macro. If you have your list of names and addresses in a book called Book2 on Sheet1 in columns A and B and the list of names only in column A of Sheet1 in Book1, enter this formula in cell B1 on Sheet1 of Book1 (next to the names):
=VLOOKUP(A1,[Book2.xls]Sheet1!$A:$B,2,0)
This tells excel to look in Book2, Sheet1 for the values.
If you do not have Book2 open when you open Book1 you will get a warning that the formula is trying to update from an outside source - click okay. This will actually alter the formula by inserting the full address for the Excel book it is linked to - in this case, Book2.
Thus, it changed my formula to
=VLOOKUP(A1,'C:\Documents and Settings\username\Desktop\[Book2.xls]Sheet1'!$A:$B,2,0)

If you prefer, you can Copy all of the addresses found in this way then Paste Special, Values back in - that will replace the formula with the actual addresses.

[macgirl27]

Thank you so much! I've been beating my head against the wall writing every "IF" formula I could think of and I couldn't make it work. Your solution worked like a charm.

[slurpee55]

Glad to help - welcome to the forum!!!

[slurpee55]

There's probably some way to do it with an Index, Match, but it isn't coming to me.