Creating a VBA Macro to Extract Data for matches

Question

I am looking for guidance on how to build a VBA Macro in Excel (I have never created one before) that would look for a unique identifier match in two separate excel files and then would extract the accompanying row data for the match.

Put more plainly:

1. I have two separate excel files, they each have a column present in them for a unique identifier.

2. I want the VBA Macro to find the matches in one of the files where the unique identifier is the same as it is in the other file.

3. Once a match is found in the Excel file, I want to extract the data for that specific row where the match is found.

4. Ideally, I would like the extracted data to be put into a new excel worksheet.

Answer 1

This is an example that will lead you to doing what you want to do. Here're the steps you will have to take or think about:

• Enable developer tools
• Create a module in VBA
• Add some constants to the top of the module
• Create code that will check if a workbook is open;
• -- if it is not, open it
• -- if workbook does not exist, create it and open it
• Create code that will use the above code to open one or more books
• Create code that will loop through rows in file 1, check in file 2 and where a match is found writes to file 3

Enable developer tools in Excel

Use this article: https://msdn.microsoft.com/en-us/library/bb608625.aspx

Open the first file. Then create a module as the next topic shows

Create module

Use this article and follow it till step 3 - create module: https://www.ablebits.com/office-addins-blog/2013/12/06/add-run-vba-macro-excel/

Create constants

Write the code below to declare constants of file names and sheet names.

Const FIRST_FILE_NAME As String = "Book1.xlsx" ' This current file
Const SECOND_FILE_NAME As String = "Book2.xlsx"
Const RESULTANT_FILE_NAME As String = "Result.xlsx"

Const wstFirst As String = "Sheet1" ' Sheet name of first file
Const wstSecond As String = "Sheet1"
Const wstResultant As String = "Sheet1"

Create code that will check if workbook is open

Write this code below the constant declaration code

' Check if a workbook is open; if inexistant, create one
Function Isworkbookopen(FileName As String)

    Dim ff As Long, ErrNo As Long
    Dim wkb As Workbook
    Dim nam As String

    wbname = FileName

    On Error Resume Next
    ff = FreeFile()
    Open FileName For Input Lock Read As #ff
    Close ff

    ErrNo = Err

    On Error GoTo 0
    Select Case ErrNo
        Case 0: Isworkbookopen = False
        Case 70: Isworkbookopen = True
        Case 53:
            Workbooks.Add
            ActiveWorkbook.SaveAs FileName:=RESULTANT_FILE_NAME
            Isworkbookopen = False
        Case Else: Error ErrNo
    End Select

End Function

In case where a file does not exist, create a new file and report that the file is not open. I just used the code by Dan Wagner in VBA macro to copy data from one excel file to another answer. Rest of the code is a modification of Dan's code also.

Create code that will open a book

Write this code next below your rest of the code. This code will take a file name and a reference variable. If workbook is not open, open it and assign it to reference variable. You will have to do some reading on ByRef yourself.

' Open a workbook and pass the reference back
Private Sub OpenBook(FileName As String, ByRef wkb As Workbook)
    ret = Isworkbookopen(FileName)
    If ret = False Then
        Set wkb = Workbooks.Open(FileName)
    Else
        Set wkb = Workbooks(FileName)
    End If
End Sub

Create code that will do looping and insert data in resultant file

Write this code at the bottom of your current code. This code will open all 3 books (First book, second book and resultant book in which data will be pasted). First file is read row by row (only 1st cell is read assuming that is the common field between first and second file). Then, file 1

Sub copydata()

    Dim wkbFirst As Workbook
    Dim wkbSecond As Workbook
    Dim wkbResultant As Workbook

    ' open all 3 files
    OpenBook FIRST_FILE_NAME, wkbFirst
    OpenBook SECOND_FILE_NAME, wkbSecond
    OpenBook RESULTANT_FILE_NAME, wkbResultant

    Dim First_File_Counter As Integer, Second_File_Counter As Integer, Resultant_File_Counter As Integer
    Dim First_Value As String, Second_Value As String
    Resultant_File_Counter = 1

    ' loop from row 1 to a large number for file #1
    For First_File_Counter = 1 To 10000

        ' get value of A1, then A2 and so on during each loop
        ' if that cell does not have a value, assume that there're no more rows of data
        First_Value = wkbFirst.Worksheets(wstFirst).Range("A" & First_File_Counter).Value
        If IsNull(First_Value) Or Len(Trim(First_Value)) = 0 Then Exit For

        ' loop from row 1 to a large number for file #2
        ' and look up information obtained from file #1 in file #2
        For Second_File_Counter = 1 To 10000
            Second_Value = wkbSecond.Worksheets(wstSecond).Range("A" & Second_File_Counter).Value
            If IsNull(Second_Value) Or Len(Trim(Second_Value)) = 0 Then Exit For

            ' if first file's A1 matches any of the rows in this second file
            ' copy the row from first file into the resultant file
            If First_Value = Second_Value Then
                wkbFirst.Worksheets(wstFirst).Rows(First_File_Counter).EntireRow.Copy
                wkbResultant.Worksheets(wstResultant).Rows(Resultant_File_Counter).Select
                wkbResultant.Worksheets(wstResultant).Paste
                Resultant_File_Counter = Resultant_File_Counter + 1
                Exit For
            End If
        Next
    Next

End Sub

Example

I created Book1.xlsx. In that I have:

    A    B
  ----- --------
1  UID  Name
2   1   John
3   2   Matt
4   3   Katie

Book2.xlsx has

    A    B
  ----- --------
1  UID  Address
2   1   100 2nd St, Chicago
3   3   Lukas Tower, Houston

When I click on any line of copycode and press F5, copycode subroutine will run. It will go through the code and then the resultant file will look like this:

    A    B
  ----- --------
1  UID  Name
2   1   John
3   3   Katie

Note that data from file 1 went to file 3 but only those rows that had matching UID in file 2 did. Matt's row from file 1 did not go to resultant file because file 2 does not have UID 2.

Hopefully that will get you stated.