none
Create way to query 2 tables for common value and display data from both tables RRS feed

  • Question

  • I have an excel workbook with 2 sheets, one with time card data and the other with expense data both in tables. They both have a common column called the service call ID and there could be multiple items with the same service call ID.

    I would like a way to query both data sets for the service call ID and report on all the rows found.

    Thanks for your help!

    Sean

    Friday, January 12, 2018 3:18 PM

Answers

  • Hi Kucster,

    Please try to refer example below.

    Code:

    Sub demo()
    Dim lRow, x As Long
    
    Sheets("Sheet1").Select
    lRow = Range("A1").End(xlDown).Row
    
    For Each cell In Range("A2:A" & lRow)
        x = 2
        Do
            If cell.Value = Sheets("Sheet2").Cells(x, "A").Value Then
                cell.EntireRow.Copy Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
                Sheets("Sheet3").Range("D" & Rows.Count).End(xlUp).Offset(1, 0).Value = Sheets("Sheet2").Cells(x, "B").Value
                 Sheets("Sheet3").Range("E" & Rows.Count).End(xlUp).Offset(1, 0).Value = Sheets("Sheet2").Cells(x, "C").Value
            End If
            x = x + 1
        Loop Until IsEmpty(Sheets("Sheet2").Cells(x, "A"))
    Next
        
    End Sub
    

    This code is just for an example, further you can modify it according to your requirement.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 15, 2018 2:17 AM
    Moderator

All replies

  • Hi Kucster,

    Please try to refer example below.

    Code:

    Sub demo()
    Dim lRow, x As Long
    
    Sheets("Sheet1").Select
    lRow = Range("A1").End(xlDown).Row
    
    For Each cell In Range("A2:A" & lRow)
        x = 2
        Do
            If cell.Value = Sheets("Sheet2").Cells(x, "A").Value Then
                cell.EntireRow.Copy Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
                Sheets("Sheet3").Range("D" & Rows.Count).End(xlUp).Offset(1, 0).Value = Sheets("Sheet2").Cells(x, "B").Value
                 Sheets("Sheet3").Range("E" & Rows.Count).End(xlUp).Offset(1, 0).Value = Sheets("Sheet2").Cells(x, "C").Value
            End If
            x = x + 1
        Loop Until IsEmpty(Sheets("Sheet2").Cells(x, "A"))
    Next
        
    End Sub
    

    This code is just for an example, further you can modify it according to your requirement.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 15, 2018 2:17 AM
    Moderator
  • That worked great! Thank you so much for your help
    Thursday, January 18, 2018 4:40 PM