VBA - Compare lists based on two criteria + add to table

THEsewingmaster

New Member
Joined
Mar 14, 2022
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Hello,

I had a previous post that solved my application of comparing two lists and adding the missing item to the list. It works flawlessly! However, I have found another application for this macro, but need help adjusting it slightly. Currently, the macro will compare column C on DATA (starting in C2) with column F on DESTINATION (starting with F5), then any missing data from DESTINATION, it adds them to the bottom of the table in column F on DESTINATION.

The change:
Same functionality, however, I want to compare items based on a unique combination of two criteria, and add both of those data entries to the end of the table in DESTINATION.

Example...
DATA
Criteria 1 (In Column C)Criteria 2 (In Column E)
ABC1
ABC2
DEF1

DESTINATION
Criteria 1 (In Column F)Criteria 2 (In Column H)
ABC1
DEF1

As you can see it is missing ABC - 2 from DESTINATION. So, I'd like the macro to recognize the combination of ABC from column C and 2 from column E from DATA is missing in DESTINATION and add ABC into column F and 2 into column H. It's important that it stays together (both ABC and 2).

I hope that makes sense. I think the code is close, I'm just not familiar with how I'd write in the change to look at multiple columns for unique combinations. Any help is much appreciated, thank you!!

VBA Code:
Sub AddMissingItems()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
    Dim Dic As Object
    Dim Arr() As Variant, outArr() As Variant
    Dim i As Long, k As Long, iRow As Long
   
    Set Dic = CreateObject("Scripting.dictionary")
    With Sheets("DESTINATION")
        Arr = .Range("F5:F" & .Range("F" & .Rows.Count).End(xlUp).Row).Value
        For i = 1 To UBound(Arr, 1)
            If Dic.exists(Arr(i, 1)) = False Then
                Dic.Add (Arr(i, 1)), ""
            End If
        Next
    End With
    With Sheets("DATA")
        Arr = .Range("C2:C" & .Range("C" & .Rows.Count).End(xlUp).Row).Value
        ReDim outArr(1 To UBound(Arr), 1 To 1)
        
        For i = 1 To UBound(Arr)
            If Dic.exists(Arr(i, 1)) = False Then
                k = k + 1
                outArr(k, 1) = Arr(i, 1)
            End If
        Next
    End With
    iRow = Sheets("DESTINATION").Range("F" & Rows.Count).End(3).Row + 1
    If k <> 0 Then
    Sheets("DESTINATION").Range("F" & iRow).Resize(k).Value = outArr
    End If
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True
End Sub
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,514
Messages
5,854,172
Members
431,623
Latest member
ncorkren

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top