Consulting

Results 1 to 12 of 12

Thread: Combine 2 ranges, compare and take the unique values

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    VBAX Mentor
    Joined
    Nov 2020
    Location
    Cochin, Kerala
    Posts
    314
    Location

    Combine 2 ranges, compare and take the unique values

    Dear Experts,
    Request your support on the below where I was trying to figure out the issue with the code and could not succeed.

    Attached the sample file where I wanted to combine the values in column A & B in both the sheets and compare and take the unique values from column F of Sheet("Detailed") to column F of Sheet("Summary").

    Sheet("Detailed") - Invoice numbers repeating with either same and/or different rejection remarks.
    Sheet("Summary")- Pivoted by CompanyName and InvoiceNumber

    I have done it using the filter formula in column G of Sheet("Summary"). But I wanted the same to be done using the VBA code and seeking help in correcting and guiding what I'm doing wrong.

    Thanks,

    Sub Check()
        Dim Sh1    As Worksheet, Sh2 As Worksheet
        Dim Sh1_LR As Long, Sh2_LR As Long, x As Long, y As Long
        Dim rng1, rng2, rng3
        Dim dict
        Dim rngResults As Range
            
        Set Sh1 = Sheets("Summary")
        Set Sh2 = Sheets("Detailed")
        Sh1_LR = Sh1.UsedRange.Rows.Count
        Sh2_LR = Sh2.UsedRange.Rows.Count
        
        Set rngResults = Sh1.Range("F2:F" & Sh1_LR)
        ReDim Results(1 To Sh1_LR - 1, 1 To 1)
        rng1 = Sh1.Range("A2:A" & Sh1_LR).Value & Sh1.Range("B2:B" & Sh1_LR).Value
        rng2 = Sh2.Range("A2:A" & Sh2_LR).Value & Sh2.Range("B2:B" & Sh2_LR).Value
        rng3 = Sh2.Range("F2:F" & Sh2_LR).Value
        
        Set dict = CreateObject("Scripting.Dictionary")
        For x = 1 To UBound(rng1)
            dict.RemoveAll
            For y = 1 To UBound(rng2)
                If rng1(x) = rng2(y) Then
                    dict(rng3(y, 1)) = 0
                End If
            Next y
            If dict.Count > 0 Then Results(x, 1) = Join(dict.keys, " | ")
        Next x
        rngResults.Value = Results
        Erase rng1, rng2, Results
            
        MsgBox "DONE"
    End Sub
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •