Query full card number from other Excel sheet

Question

I have two Excel sheets in same Workbook. One includes cards's transaction information, like Sheet1

Transaction Date  Card Number   Amount  (To be fill) Owner Information
11/01/2017        5678        $39.99    -
11/02/2017        1234        $39.99    -

The other includes cards's information, like Sheet2

Create Date        Card Number    Owner Information
10/01/2017         5678           [email protected]
10/01/2017         1234           [email protected]
10/01/2017         2345           [email protected]

I want a fast/batch way to copy the Owner Information from sheet2 to the matching rows in sheet1.

Right now, I do manually search Card Number in Sheet2, and copy paste to Sheet1. But how I can automatically or batch search and achieve this?

(PS. I do have T-SQL background, but ADO does not take column as parameter) And I am using Excel 2011 in MAC.

"ADO does not take column as parameter" on a Mac???" I don't have a MAC but this seems ridiculous. Please post your code. — user6432984

Unknown Unknown · Accepted Answer · 2017-11-02T05:52:38

Use a collection to reference unique values between multiple lists.

Note: Generally you will see a Scripting.Dictionary used for this task but I do not believe it is available on a Mac. So I used a standard VBA Collection.

Sub Batch_Owner_Information()
    Application.ScreenUpdating = False
    Dim c As New Collection
    Dim cell As Range, rInfo As Range

    With ThisWorkbook.Worksheets("Sheet2")
        For Each cell In .Range("B2", .Range("B" & .Columns.Count).End(xlUp))
            On Error Resume Next
            c.Add cell, cell.Text
            On Error GoTo 0
        Next
    End With

    With ThisWorkbook.Worksheets("Sheet1")
        For Each cell In .Range("B2", .Range("B" & .Columns.Count).End(xlUp))
            On Error Resume Next
            Set rInfo = c(cell.Text)
            If Err.Number = 0 Then
                 cell.Offset(0, 2).Value = rInfo.Offset(0, 1).Value
            End If
            On Error GoTo 0
        Next
    End With

    Application.ScreenUpdating = True
End Sub

Query full card number from other Excel sheet

1 Answers