THEsewingmaster
New Member
- Joined
- Mar 14, 2022
- Messages
- 9
- Office Version
- 365
- Platform
- 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
DESTINATION
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!!
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) |
ABC | 1 |
ABC | 2 |
DEF | 1 |
DESTINATION
Criteria 1 (In Column F) | Criteria 2 (In Column H) |
ABC | 1 |
DEF | 1 |
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