Eliminate Duplicates in Arrays

[cstimart]

Suppose I have 2 Arrays: Example1(1 to 20) and Example2(1 to 35)

Is there a quick an easy was to compare these two and remove any records that are in BOTH of the arrays?

Also, the "20" and "35" will vary each time the macro is executed.

 

[cstimart]

The original arrays contained duplicates and also in the combination of the 2 arrays have duplicates.
Ik did not remove the duplicates from the original arrays.
When you click the button: Merge.... you will see the result of the merged arrays in column C without duplicates.
Did you click the button ?

Yes, when I worked with the macro, it did merge the two without any duplication.

 

[taurean]

Thank you trunten. I did not know about this reasoning. Got something new to learn.

I have revised code to use Dictionary Object.

Sub UniqueArray()
Dim a(), b()
Dim objDn As Object

'Two Arrays
a = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20)
b = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35)

Set objDn = CreateObject("Scripting.Dictionary")

'Here we load up the third one from first 2
For i = LBound(b) To UBound(b)
    If Not IsNumeric(Application.Match(b(i), a, 0)) And Not (objDn.Exists(b(i))) Then
    objDn.Add b(i), b(i)
    End If
Next i

'Printing the list
MsgBox "Total Items Found :" & (objDn.Count) & ". They Are:" & vbCrLf & Join(objDn.keys, vbCrLf)
End Sub

 

[cstimart]

Is this useful?

Sub UniqueArray()
Dim a(), b(), c(), iCount As Long
'Two Arrays
a = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20)
b = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, _
21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35)
 
'Here we load up the third one from first 2
For i = LBound(b) To UBound(b)
    If Not IsNumeric(Application.Match(b(i), a, 0)) Then
    ReDim Preserve c(iCount)
    c(iCount) = b(i)
    iCount = iCount + 1
    End If
Next i
 
'Printing the list
MsgBox "Total Items Found :" & (UBound(c) + 1) & ". They Are:" & vbCrLf & Join(c, vbCrLf)
End Sub

Yes, that is useful. However, since I am a neophyte with arrays, how would I fill the 'a' array with the values in A2:A20, for example?

 

[trunten]

Yes, that is useful. However, since I am a neophyte with arrays, how would I fill the 'a' array with the values in A2:A20, for example?

Dim a()
a = Application.Transpose(Range("A2:A20").value)
'....

 

[trunten]

Thank you trunten. I did not know about this reasoning. Got something new to learn.

Np. Glad you took it in the spirit it was meant too.
I'm quite fond of ArrayLists tbh, easy to get an array from them if need be, indexable, unordered and no limits.

 

[taurean]

To work with ranges you can do something like.

Sub UniqueArray()
Dim r As Range
Dim objDn As Object

Set objDn = CreateObject("Scripting.Dictionary")

For Each r In Range("B1:B35") 'Set Larger Range Reference Here
    If Not IsNumeric(Application.Match(r.Value, Range("A1:A20"), 0)) _
    And Not objDn.exists(r.Value) Then 'Set Smaller Range Reference Here
    objDn.Add r.Value, r.Value
    End If
Next r

'Printing the list
MsgBox "Total Items Found :" & (objDn.Count) & ". They Are:" & vbCrLf & Join(objDn.keys, vbCrLf)
End Sub

 

[cstimart]

To work with ranges you can do something like.

Sub UniqueArray()
Dim r As Range
Dim objDn As Object
 
Set objDn = CreateObject("Scripting.Dictionary")
 
For Each r In Range("B1:B35") 'Set Larger Range Reference Here
    If Not IsNumeric(Application.Match(r.Value, Range("A1:A20"), 0)) _
    And Not objDn.exists(r.Value) Then 'Set Smaller Range Reference Here
    objDn.Add r.Value, r.Value
    End If
Next r
 
'Printing the list
MsgBox "Total Items Found :" & (objDn.Count) & ". They Are:" & vbCrLf & Join(objDn.keys, vbCrLf)
End Sub

OK, one last question. How do I get the objDn results to populate into cells, rather than a msgbox?

 

[taurean]

Just change the last line like:

Range("C1").Resize(objDn.Count, 1).Value = Application.Transpose(objDn.keys)

 

[Ingolf]

OK, one last question. How do I get the objDn results to populate into cells, rather than a msgbox?

Hi,

In Taurean code replace:

 MsgBox "Total Items Found :" & (objDn.Count) & ". They Are:" & vbCrLf & Join(objDn.keys, vbCrLf)

With:

 Range("C2:C" & objDn.Count) = Application.Transpose(Array(objDn.keys))

 

[cstimart]

Thank You to all!