Compare two arrays to find the difference

[Vincent88]

Hi Guys,
I use this code to find the difference and create a new array to list in column P. It's stuck. Please help.

Sub test7()
Dim ws As Worksheet
Set ws = Worksheets("Data")
Dim v1 As Variant, v2 As Variant, v3 As Variant
Dim coll As Collection
Dim i As Long

'Assumes 0-based Variants
v1 = ws.Range("N1", ws.Range("N1").End(xlDown))
v2 = ws.Range("O1", ws.Range("O1").End(xlDown))

ReDim v3(LBound(v1) To Abs(UBound(v2) - UBound(v1)) - 1)

Set coll = New Collection
For i = LBound(v1) To UBound(v1)
    coll.Add v1(i), v1(i)
Next i
For i = LBound(v2) To UBound(v2)
    On Error Resume Next
    coll.Add v2(i), v2(i)
    If Err.Number <> 0 Then
        coll.Remove v2(i)
    End If
    On Error GoTo 0
Next i
For i = LBound(v3) To UBound(v3)
    v3(i) = coll(i + 1) 'Collections are 1-based
    Debug.Print v3(i)
Next i
End Sub

TestDropDownList_2.xlsm
	N	O	P
1	Agent	From ColumnA	Find Missing bet N & O
2	Cat G	Cat G
3	Jack S	Ken C
4	John G	Larry Q
5	Ken C	Mandy H
6	Larry Q	Mary K
7	Mandy H	Nancy L
8	Mary K	Peter B
9	Nacy L	Robert M
10	Peter B	Viola C
11	Robert M	Warus O
12	Viola C	Zita V
13	Warus O
14	Zita V
Data

 

[Marc L]

In fact those values in column N and O are extracted from two arrays (see my codes)

Why did you create the thread without this important information ?!​

 

[Marc L]

How to find the differences between these two arrays

According to your attachment in this new demonstration variables V & W are the two arrays :​

Sub Demo2()
        Dim R&, V, W
    With Sheets("Data")
        R = .Cells(.Rows.Count, 16).End(xlUp).Row:  If R > 1 Then .Range("P2:P" & R).Clear
        V = .Range("N2", .[N1].End(xlDown)).Value2
        W = .Range("O2", .[O1].End(xlDown)).Value2
        W = Application.Match(V, W, 0)
    For R = 1 To UBound(V)
        If IsNumeric(W(R, 1)) Then V(R, 1) = False
    Next
        V = Filter(Application.Transpose(V), False, False)
        If UBound(V) > -1 Then .[P2].Resize(UBound(V) + 1).Value2 = Application.Transpose(V)
    End With
End Sub

 

[Marc L]

The 1D arrays variation for starters :​

Sub Demo2v()
        Dim R&, V, W, C&
    With Sheets("Data")
        R = .Cells(.Rows.Count, 16).End(xlUp).Row:  If R > 1 Then .Range("P2:P" & R).Clear
        V = [{"Clever","Dumb","Genius","Idiot","Smart"}]
        W = [{"Genius","Smart","Clever"}]
        W = Application.Match(V, W, 0)
        C = Application.Count(W)
     If C < UBound(V) Then
        For R = 1 To UBound(V)
            If IsNumeric(W(R)) Then V(R) = False
        Next
           .[P2].Resize(UBound(V) - C).Value2 = Application.Transpose(Filter(V, False, False))
     End If
    End With
End Sub

 

[Vincent88]

The 1D arrays variation for starters :​

Sub Demo2v()
        Dim R&, V, W, C&
    With Sheets("Data")
        R = .Cells(.Rows.Count, 16).End(xlUp).Row:  If R > 1 Then .Range("P2:P" & R).Clear
        V = [{"Clever","Dumb","Genius","Idiot","Smart"}]
        W = [{"Genius","Smart","Clever"}]
        W = Application.Match(V, W, 0)
        C = Application.Count(W)
     If C < UBound(V) Then
        For R = 1 To UBound(V)
            If IsNumeric(W(R)) Then V(R) = False
        Next
           .[P2].Resize(UBound(V) - C).Value2 = Application.Transpose(Filter(V, False, False))
     End If
    End With
End Sub

Hi Marc L,
this code does not need Column N and O involvement. What should be changed to refer V and W as the two already existed arrays - something like

Sub Demo2v()
        Dim R&, V, W, C&
    With Sheets("Data")
        R = .Cells(.Rows.Count, 17).End(xlUp).Row:  If R > 1 Then .Range("Q2:Q" & R).Clear
        V = Application.Transpose(Sorted_array)
        W = Application.Transpose(SortedColA_array)
        W = Application.Match(V, W, 0)
        C = Application.Count(W)
     If C < UBound(V) Then
        For R = 1 To UBound(V)
            If IsNumeric(W(R)) Then V(R) = False
        Next
           .[Q2].Resize(UBound(V) - C).Value2 = Application.Transpose(Filter(V, False, False))
     End If
    End With
End Sub

 

[Marc L]

​

Just use your array variables instead of V & W.​

​

By the way it's useless to sort any array before comparing both arrays …​

And as it seems the source of the arrays is located within a worksheet so you do not ever need those arrays according to my post #4 first demonstration …​

 

[Vincent88]

​

Just use your array variables instead of V & W.​

​

By the way it's useless to sort any array before comparing both arrays …​

And as it seems the source of the arrays is located within a worksheet so you do not ever need those arrays according to my post #4 first demonstration …​

Hi Marc L,
Thank you for your valueable advice.
The origins of my two arrays are not sorted so I use the codes to sort them. See my extraction of the codes.
What I want to learn is that
IS IT POSSIBLE TO APPLY THESE TWO VARIANTS - Sorted_array AND SortedColA_array DIRECTLY TO FIND THE DIFFERENCE BETWEEN THEM INSTEAD OF TRANSPOSE THEM INTO COLUMN "N" and COLUMN "O" THEN FIND THE DIFFERENCE FROM THE SAID COLUMNS. (IN OTHER WORD, I WANT TO ELIMINATE THESE TWO LINES IN MY CODES BUT STILL ABLE TO FIND THE DIFFERENCE, IF POSSIBLE)
Range("N1").Resize(UBound(Sorted_array) + 1, 1).Value = Application.Transpose(Sorted_array)
Sheets("Data").Range("O2").Resize(UBound(SortedColA_array) + 1, 1).Value = Application.Transpose(SortedColA_array)

'Physical Source in Column L
    lr = Range("L1").End(xlDown).Row
    Debug.Print lr

    For Each cl In Range("L1:L" & lr)
        If Not Agtarray.contains(cl.Value) Then Agtarray.Add cl.Value
    Next cl
      
    Agtarray.Sort
   [COLOR=rgb(235, 107, 86)] Sorted_array[/COLOR] = Agtarray.toarray


'Physical Source in Column L
lrA = Range("A1").End(xlDown).Row
Debug.Print lrA
Set rngA = Range("A3:A" & lrA)


   For Each clA In rngA
      If Not colAarray.contains(clA.Value) Then colAarray.Add clA.Value
   Next clA
 
colAarray.Sort
[COLOR=rgb(235, 107, 86)]SortedColA_array[/COLOR] = colAarray.toarray

 

[Marc L]

Yes as I yet wrote in my previous post with post #4 Demo1 !​

​

Again it is very not necessary to waste any ressource and time to sort any array to compare them as you wrote :​

How to find the differences between these two arrays directly without putting then into columns (Leave Column N and O blank, just show the result in Column P).

​

So again just using Demo1 … Maybe just the result should be sorted ?​