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]

I mean what if the recorded dates are not in column B but some random column? Does that warrant a change to script? Cause I tried and if I put the recorded dates next to vehicle reg columns, it works fine but if they are not next column then macro doesn't work.

 

[mumps]

If they will always be in a random column, will the header in row one of that column always be the same and if so, what is the header name?

 

[reseng]

Not necessarily. They can be random but in this instance Sheet1 date header is "Date Gross Weighed" and Sheet2 header is "GD".

 

[mumps]

The macro can find the column using the header name in row 1 but if the header name keeps changing, then the code has no way to find it.

 

[reseng]

Ok I will change header so that they are same. What other changes do I need to make? And goes without saying, I really appreciate your help.

 

[mumps]

Will the vehicle registration always be in column A?

 

[reseng]

Actually it was in column A in sheet1 but in a different column in sheet2 so I changed it to the appropriate column. But as far as date was in next column it worked.

 

[mumps]

The macro assumes that the vehicle registration is always in column A of both sheets and that the headers are "Date Gross Weighed" and "GD".

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
                .Cells(dic(val), hd1.Column + 1) = v2(i, 1)
            End With
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

 

[reseng]

Which column would it display the reg?

 

[mumps]

The column to the right of the date.