Return a value if data matches

[reseng]

I have two separate spreadsheets with vehicle registrations and dates. The two spreadsheets correspond to two different locations. The dates correspondence to when a vehicle made a trip to that location.



I want to find out when a particular vehicle made a trip to both locations on same date and return vehicle reg. Like matching dates for a particular vehicle for two locations. Really appreciate your help in advance.

 

[Anthony47]

And which is the layout of the info on the two worksheets? Possibly a view of them via the XL2BB addind

 

[reseng]

The two spreadsheets have a lot of data.
I am uploading the sample data.
When a vehicle makes a trip to both locations on same day, I want to be able to pull the registration of that vehicle. The image on left (first two columns) is from workbook location 1 and the image on right (last two columns) is from workbook location 2.

 

[mumps]

You haven't stated where you want to return the vehicle registration so this macro assumes that you want to return the data to a third workbook which will contain the macro. Change the sheet names (in red) and the workbook names (in blue) to suit your needs. If this is not the case, then please clarify in detail referring to specific workbooks and sheets.

Sub CompareData()
    Application.ScreenUpdating = False
    Dim WS1 As Worksheet, WS2 As Worksheet, v1 As Variant, v2 As Variant, dic As Object, val As String
    Set WS1 = Workbooks("Workbook1.xlsx").Sheets("Sheet1")
    Set WS2 = Workbooks("Workbook2.xlsx").Sheets("Sheet1")
    v1 = WS1.Range("A2", WS1.Range("A" & Rows.Count).End(xlUp)).Resize(, 2).Value
    v2 = WS2.Range("A2", WS2.Range("A" & Rows.Count).End(xlUp)).Resize(, 2).Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = LBound(v1) To UBound(v1)
        val = v1(i, 1) & "|" & v1(i, 2)
        If Not dic.exists(val) Then
            dic.Add val, Nothing
        End If
    Next i
    For i = LBound(v2) To UBound(v2)
        val = v2(i, 1) & "|" & v2(i, 2)
        If dic.exists(val) Then
            With ThisWorkbook.Sheets("Sheet1")
                .Cells(.Rows.Count, "A").End(xlUp).Offset(1) = v2(i, 1)
            End With
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

 

[reseng]

Thanks a lot. It pulls the vehicle reg that makes a trip to both locations.
Like you said in your reply, where do I want to display this data? I didn't pay attention to that detail.
Is it possible to display it in either sheet against the dates when it made that trip to both location?

 

[mumps]

Place this macro in a regular module in Workbook1. The vehicle registration will be placed in column D of "Sheet1".

Sub CompareData()
    Application.ScreenUpdating = False
    Dim WS1 As Worksheet, WS2 As Worksheet, v1 As Variant, v2 As Variant, dic As Object, val As String
    Set WS1 = ThisWorkbook.Sheets("Sheet1")
    Set WS2 = Workbooks("Workbook2.xlsx").Sheets("Sheet1")
    v1 = WS1.Range("A2", WS1.Range("A" & Rows.Count).End(xlUp)).Resize(, 2).Value
    v2 = WS2.Range("A2", WS2.Range("A" & Rows.Count).End(xlUp)).Resize(, 2).Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = LBound(v1) To UBound(v1)
        val = v1(i, 1) & "|" & v1(i, 2)
        If Not dic.exists(val) Then
            dic.Add val, Nothing
        End If
    Next i
    For i = LBound(v2) To UBound(v2)
        val = v2(i, 1) & "|" & v2(i, 2)
        If dic.exists(val) Then
            With WS1
                .Cells(.Rows.Count, "D").End(xlUp).Offset(1) = v2(i, 1)
            End With
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

 

[reseng]

Place this macro in a regular module in Workbook1. The vehicle registration will be placed in column D of "Sheet1".

Sub CompareData()
    Application.ScreenUpdating = False
    Dim WS1 As Worksheet, WS2 As Worksheet, v1 As Variant, v2 As Variant, dic As Object, val As String
    Set WS1 = ThisWorkbook.Sheets("Sheet1")
    Set WS2 = Workbooks("Workbook2.xlsx").Sheets("Sheet1")
    v1 = WS1.Range("A2", WS1.Range("A" & Rows.Count).End(xlUp)).Resize(, 2).Value
    v2 = WS2.Range("A2", WS2.Range("A" & Rows.Count).End(xlUp)).Resize(, 2).Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = LBound(v1) To UBound(v1)
        val = v1(i, 1) & "|" & v1(i, 2)
        If Not dic.exists(val) Then
            dic.Add val, Nothing
        End If
    Next i
    For i = LBound(v2) To UBound(v2)
        val = v2(i, 1) & "|" & v2(i, 2)
        If dic.exists(val) Then
            With WS1
                .Cells(.Rows.Count, "D").End(xlUp).Offset(1) = v2(i, 1)
            End With
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

Thanks a lot. I managed to display the data in the next available empty column. However, it still doesn't display in line with dates. For example EFX vehicle made a trip to both locations on 11/05/2024. But this Macro lists it at the top of the column without taking the date into consideration. If will be wonderful if displayed next to the date it made the trip. I hope it makes sense.

 

[mumps]

Try:

Sub CompareData()
    Application.ScreenUpdating = False
    Dim WS1 As Worksheet, WS2 As Worksheet, v1 As Variant, v2 As Variant, dic As Object, val As String
    Set WS1 = ThisWorkbook.Sheets("Sheet1")
    Set WS2 = ThisWorkbook.Sheets("Sheet2")
    v1 = WS1.Range("A2", WS1.Range("A" & Rows.Count).End(xlUp)).Resize(, 2).Value
    v2 = WS2.Range("A2", WS2.Range("A" & Rows.Count).End(xlUp)).Resize(, 2).Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = LBound(v1) To UBound(v1)
        val = v1(i, 1) & "|" & v1(i, 2)
        If Not dic.exists(val) Then
            dic.Add val, i + 1
        End If
    Next i
    For i = LBound(v2) To UBound(v2)
        val = v2(i, 1) & "|" & v2(i, 2)
        If dic.exists(val) Then
            With WS1
                .Range("D" & dic(val)) = v2(i, 1)
            End With
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

 

[reseng]

Try:

Sub CompareData()
    Application.ScreenUpdating = False
    Dim WS1 As Worksheet, WS2 As Worksheet, v1 As Variant, v2 As Variant, dic As Object, val As String
    Set WS1 = ThisWorkbook.Sheets("Sheet1")
    Set WS2 = ThisWorkbook.Sheets("Sheet2")
    v1 = WS1.Range("A2", WS1.Range("A" & Rows.Count).End(xlUp)).Resize(, 2).Value
    v2 = WS2.Range("A2", WS2.Range("A" & Rows.Count).End(xlUp)).Resize(, 2).Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = LBound(v1) To UBound(v1)
        val = v1(i, 1) & "|" & v1(i, 2)
        If Not dic.exists(val) Then
            dic.Add val, i + 1
        End If
    Next i
    For i = LBound(v2) To UBound(v2)
        val = v2(i, 1) & "|" & v2(i, 2)
        If dic.exists(val) Then
            With WS1
                .Range("D" & dic(val)) = v2(i, 1)
            End With
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

Thanks a lot. It works when I have the date column next to vehicle reg. What if date column is further away from vehicle reg? What needs changing in script. For example if date column is 6 columns down from vehicle reg, where and what needs changing?

 

[mumps]

Change the letter “D” in the code to the desired column letter.