VBA to compare values in two columns i.e (Id Number and Dates) on separate sheets for unique values then extract row content to third sheet

[Argh_Work]

Hi All,

I had some amazing help from @Flashbond on a query earlier this week in comparing values between worksheets and extracting rows to a third. I wasn't sure of the etiquette of building onto an initial query so have created a new post. The results from the first query have opened my thinking to what might be possible. And I was wondering if the below would be possible?

In the initial query I needed any unique numbers from the "Compare" worksheet Column A that didn't appear in the "Master" worksheet Column A to extract the row across to the "Result" worksheet. This worked amazingly (thanks again @Flashbond ).

Would it be possible for a query to check against two columns. For example in the screenshot below 897661827 appears in both the "Master" and "Compare" Worksheet. But in the "Compare" worksheet there is a third date.
So I wouldn't need Row 3 897661827 01/07/2023 and Row 4 897661827 05/07/2023 extracted but I would need the Row 5 897661827 17/07/2023 extracted? I'd also still need any unique numbers from the "Compare" worksheet column A to be extracted to the results worksheet.

I really appreciate the help of everyone. I'm not at the stage where I can contribute solutions but hopefully with the learning, I'm doing it won't be too long.

Thanks again everyone.

 

[Legacy 143009]

Hi,

Something like this should work fine if I understood correctly.

Sub test()
  Dim compareRange As Variant, masterRange As Variant, lRow As Long, i As Long, j As Long
  Dim tempRange As Variant
  Dim dict As Object
  Set dict = CreateObject("Scripting.Dictionary")
 
  With Worksheets("Master")
  masterRange = .Range("A1:C" & .Cells(Rows.Count, 1).End(xlUp).Row)
  End With
  For i = 1 To UBound(masterRange, 1)
    If Not dict.Exists(masterRange(i, 1)&masterRange(i, 2)&masterRange(i, 3)) Then
      dict.Add masterRange(i, 1)&masterRange(i, 2)&masterRange(i, 3), 1
    End If
  Next
 
  compareRange = Worksheets("Compare").UsedRange
  With Worksheets("Result")
  ReDim tempRange(1 To UBound(compareRange, 2), 1 To 1)
  For i = 1 To UBound(compareRange, 1)
    If Not dict.Exists(compareRange(i, 1)&compareRange(i, 2)&compareRange(i, 3)) Then
      For j = 1 To UBound(compareRange, 2)
        tempRange(j, UBound(tempRange, 2)) = compareRange(i, j)
      Next
      ReDim Preserve tempRange(1 To UBound(tempRange, 1), 1 To UBound(tempRange, 2) + 1)
    End If
  Next
  ReDim Preserve tempRange(1 To UBound(tempRange, 1), 1 To UBound(tempRange, 2) - 1)
  .Range("A3").Resize(UBound(tempRange, 2), UBound(tempRange, 1)).Value = Application.Transpose(tempRange)
  End With
End Sub

 

[Argh_Work]

Thanks so much this worked perfectly! You're an absolute hero.

 

[Legacy 143009]

I am glad it did work Thanks for the feedback!