Cross check two different spreadsheets and transfer value from one to the other

Hi,

I have two separate spreadsheets with lists of email addresses. Each spreadsheet has email addresses in column A, followed by different properties in subsequent columns to Column D. I'm looking for a way in excel to search one spreadsheet to see if the same email is present in both spreadsheets. Then, if it finds a match, I'm hoping to transfer the cell contents from one of the properties to the other spreadsheet.

In excel terms its something like this.

If an email address in Spreadsheet#1 cell A2 is found in the range A1:A10000 of Spreadsheet#2, then grab the information relevant to that email address from column B in Spreadsheet #2, and add it to E2 in Spreadsheet #1.

I'm sorry that I couldn't find a better way to explain this, but I'm fairly new to excel!

Thank you for your help!

Try this:

'Run this code in Spreadsheet#1

Sub Copy_Email_Info()
Dim Wb1 as Workbook, Wb2 as Workbook, i as integer

Application.ScreenUpdating = False
Set Wb1 = ThisWorkbook
Set Wb2 = Workbooks.Open(Filename:="C:\Example\Spreadsheet#2.xlsx") ' Change "C:\Example\Spreadsheet#2.xlsx" to actual file path with name & file extension

With Wb1.Worksheets(1)
     For i = 1 to 10000 Step 1 'change 10000 to the actual number of email addresses in Spreadsheet1
          .Range("B" & i) = Application.WorksheetFunction.VLookUp(.Range("A" & i), Wb2.Worksheets(1).Range("A1:A10000"), 2, False)
          If .Range("B" & i) = "" Then .Range("B" & i) = "Email not found in Spreadsheet#2"
          Next i
     End With

Wb2.Close
Set Wb1 = Nothing
Set Wb2 = Nothing

Application.ScreenUpdating = True
End Sub

Thanks for your help jewelsharma. I ran the line of code, but kept getting Runtime error '1004' on the line If .Range("B"..... saying that it was unable to get the Vlookup property of the WorksheetFunctionClass. I attached an example spreadsheet (without actual email addresses) to see if we can sort it with references that are a little less abstract.

So for example: I'm looking to see if the email address in A2 of 'Email List 1' exists in the range (A2:A5)of sheet 'Email List 2'. If so, I want it to put the corresponding "Car" value for that email address into column F of 'Email List 1'.


Test Spreadsheet.xlsx

Looks like this is a Vlookup problem, I think I figured it out.

Hi thewhawk,

Notes:

• i renamed the files as Test1 and Test2.
• i renamed worksheets as "Sheet1" in both the files.
• the code is to be run from Test1 workbook.

Try this modified code:

Sub Copy_Email_Info()
Dim Wb1 As Workbook, Wb2 As Workbook, i As Integer
Dim sEmail1 As String, sEmail2 As Variant

Application.ScreenUpdating = False

Set Wb1 = ThisWorkbook
Set Wb2 = Workbooks.Open(Filename:="C:\Example\Test2.xlsx") ' Change "C:\Example\Test2.xlsx" to actual file path with name & file extension

For i = 2 To 6 Step 1 'change 2 & 6 to the actual number of email addresses in Spreadsheet1
     sEmail1 = ThisWorkbook.Worksheets("Sheet1").Range("A" & i)
     With Wb2.Worksheets("Sheet1")
          sEmail2 = Application.VLookup(sEmail1, .Range("A1:B5"), 2, False)
          End With
     If IsError(sEmail2) Then
               ThisWorkbook.Worksheets("Sheet1").Range("F" & i) = "Email not found in Test2"
          Else
               ThisWorkbook.Worksheets("Sheet1").Range("F" & i) = sEmail2
          End If
     Next i

Wb2.Close
Set Wb1 = Nothing
Set Wb2 = Nothing
Application.ScreenUpdating = True
End Sub

This works fine when I tested. Let me know if this worked for you.

Warm regards,
Jewel