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.

 

[reseng]

Is it possible to keep it like before in terms of column where reg is displayed. Like a designated column instead of +1

 

[mumps]

What is the column?

 

[reseng]

Column Y

 

[reseng]

It works perfectly but I am trying not to mess up the spreadsheet as it belongs to someone else. Thanks for putting up with me.

 

[mumps]

Replace this line of code:

.Cells(dic(val), hd1.Column + 1) = v2(i, 1)

with this line:

.Range("Y" & dic(val)) = v2(i, 1)

 

[reseng]

I have a column with heading "tonnage" in sheet2, when the two dates match and matching date is displayed I want to be able to display the corresponding tonnage in Sheet 1 as well. Please help!

 

[mumps]

In which column in Sheet2 is the heading "Tonnage"? Will this column always be the same? In which column in Sheet1 do you want to display the tonnage?

 

[reseng]

The tonnage is in column "O" in sheet 2. And I want to display it next to column where I displayed the matching dates in sheet 1. Thanks

 

[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, hd1 As Range, hd2 As Range
    Set WS1 = ThisWorkbook.Sheets("Sheet1")
    Set WS2 = ThisWorkbook.Sheets("Sheet2")
    Set hd1 = WS1.Rows(1).Find("Date Gross Weighed", LookIn:=xlValues, lookat:=xlWhole)
    Set hd2 = WS2.Rows(1).Find("GD", LookIn:=xlValues, lookat:=xlWhole)
    v1 = WS1.Range("A2", WS1.Range("A" & Rows.Count).End(xlUp)).Resize(, hd1.Column).Value
    v2 = WS2.Range("A2", WS2.Range("A" & Rows.Count).End(xlUp)).Resize(, hd2.Column).Value
    Set dic = CreateObject("Scripting.Dictionary")
    For i = LBound(v1) To UBound(v1)
        val = v1(i, 1) & "|" & v1(i, hd1.Column)
        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, hd2.Column)
        If dic.exists(val) Then
            With WS1
                .Range("Y" & dic(val)) = v2(i, 1)
                .Range("Z" & dic(val)) = WS2.Range("O" & i + 1)
            End With
        End If
    Next i
    Application.ScreenUpdating = True
End Sub