VBA Dictionary - Compare two Columns and extract unique

[Mallesh23]

Hi Team,

I have two list of transaction ID. Compare it and Extract Unique Values.

Column A , Old list of transaction ID
Column B , New list of transaction ID


Colum C is a expected output. how to extract using Unique values using dictionary or any other method. Thanks.

Below is Table with expected output. Thanks.

Book6
	A	B	C
1	Old Transaction ID	New Transaction ID	Unique Data
2	11173	11173	11246
3	11175	11175	11239
4	11183	11183	11247
5	11185	11185	11260
6	11215	11215	11257
7	11217	11217	11257
8	11225	11246	11259
9	11231	11239
10		11247
11		11185
12		11260
13		11257
14		11183
15		11257
16		11259
17		11231
Sheet1

Thanks
mg

 

[Fluff]

How about

Sub Mallesh()
   Dim Cl As Range
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("B2", Range("B" & Rows.Count).End(xlUp))
         .Item(Cl.Value) = Empty
      Next Cl
      For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
         If .Exists(Cl.Value) Then .Remove Cl.Value
      Next Cl
      Range("C2").Resize(.Count).Value = Application.Transpose(.Keys)
   End With
End Sub

 

[Mallesh23]

Hi Fluff,

Wow! What a beautiful piece of Code ,
It worked as expected, thanks a lot for your help !!! ?


Thanks
mg

 

[Fluff]

Glad to help & thanks for the feedback.

 

[Mallesh23]

Hi Fluff,

One more help needed in this Example.
Compare two Columns and extract adjacent Columns cells value as well.

Below Code Puts output in G Columns , how to extract B and D Columns data into F and H.

Sub CompareTwoColumns_Extract_Unique()
  
   Dim ary_base As Variant
   Dim ary_Compare As Variant
  
   'Store two Columns into Array
   ary_base = Sheet3.Range("C2", Range("C" & Rows.Count).End(xlUp)).Value2
   ary_Compare = Sheet3.Range("a2", Range("a" & Rows.Count).End(xlUp)).Value2
  
   'Store Unique value into Dictionary calling Function
   Dim dict_output As Dictionary
   Set dict_output = Compare2List(ary_base, ary_Compare)
  
   'Print Dict Unique Keys
    Range("G2").Resize(dict_output.Count).Value = Application.Transpose(dict_output.Keys)
     
        
End Sub

Public Function Compare2List(ByRef ary_base As Variant, ary_Compare As Variant) As Dictionary

    Dim i As Long
    Dim dict As New Scripting.Dictionary

     With dict
           
            For i = LBound(ary_base) To UBound(ary_base)
                .Item(ary_base(i, 1)) = Empty
            Next i
           
            For i = LBound(ary_Compare) To UBound(ary_Compare)
                If .exists(ary_Compare(i, 1)) Then .Remove (ary_Compare(i, 1))
            Next i
   
    End With
       

    Set Compare2List = dict

End Function

Below is Table and Expected output in Range("F:H")

Book3
	A	B	C	D	E	F	G	H
1	Old Transaction ID	Post Date	New Transaction ID	Transaction Date		Post Date	New Transaction ID	Transaction Date
2	11173	10/08/2020	11173	10/08/2020		16/08/2020	11246	11/08/2020
3	11175	14/08/2020	11175	14/08/2020		18/08/2020	11239	10/08/2020
4	11183	06/08/2020	11183	06/08/2020		09/08/2020	11247	09/08/2020
5	11185	09/08/2020	11185	09/08/2020		23/08/2020	11260	13/08/2020
6	11215	08/08/2020	11215	08/08/2020		11/08/2020	11257	11/08/2020
7	11217	11/08/2020	11217	11/08/2020		01/08/2020	11259	09/08/2020
8	11225	16/08/2020	11246	11/08/2020
9	11231	18/08/2020	11239	10/08/2020
10		09/08/2020	11247	09/08/2020
11		12/08/2020	11225	12/08/2020
12		23/08/2020	11260	13/08/2020
13		11/08/2020	11257	11/08/2020
14		11/08/2020	11183	11/08/2020
15		07/08/2020	11259	09/08/2020
16		09/08/2020	11231	09/08/2020
Sheet3

Thanks
mg

 

[Fluff]

How about

Sub Mallesh()
   Dim Cl As Range
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("c2", Range("c" & Rows.Count).End(xlUp))
         .Item(Cl.Value) = Cl.Offset(, -1).Resize(, 3)
      Next Cl
      For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
         If .Exists(Cl.Value) Then .Remove Cl.Value
      Next Cl
      Range("j2").Resize(.Count, 3).Value = Application.Index(.items, 0, 0)
   End With
End Sub

 

[Mallesh23]

Hi Fluff,

Thanks again for your help, giving correct output as per my question.

One more Last question if there are Non - Contiguous Columns and I want to add 5 Columns into Dictionary Items.

How to add 5 Non - Contiguous columns data into Dictionary Items. and Print keys and Items.

for example:=>
.Item(Cl.Value) = Array (Cl.Offset(, -1),Cl.Offset(, 2),Cl.Offset(, 3),Cl.Offset(, 5)))

and how to print same as output. Thanks.



Thanks
mg

 

[Fluff]

Just change the resize on the output from 3 to 4 columns

 

[Mallesh23]

Hi Fluff,

sorry for late on this, Output of expected Columns are not in sequence, I am not getting desired result.

Expected Result are in Column GHI,

Macro's output which is in KLM Column , which is not matching with Expeced Columns("G:I:").value


Need minor change, But I am unable to Identify it. Thanks.

Sub Mallesh()
   Dim Cl As Range
  
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("c2", Range("c" & Rows.Count).End(xlUp))
         .Item(Cl.Value) = Array(Cl.Offset(, -1), Cl.Offset(, 2))
         '.Item(Cl.Value) = Cl.Offset(, -1).Resize(, 3)
      Next Cl
      For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
         If .Exists(Cl.Value) Then .Remove Cl.Value
      Next Cl
      Range("K2").Resize(.Count, 3).Value = Application.Index(.items, 0, 0)
   End With
End Sub

Below is input and expected output

Book2
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Old Transaction ID	Post Date	New Transaction ID	Dummy data	Transaction Date		Post Date	New Transaction ID	Transaction Date		Post Date	New Transaction ID	Transaction Date
2	11173	10/08/2020	11173	xxx	10/08/2020		16/08/2020	11246	11/08/2020		16/08/2020	44143	#N/A
3	11175	14/08/2020	11175	xxx	14/08/2020		18/08/2020	11239	10/08/2020		18/08/2020	44112	#N/A
4	11183	06/08/2020	11183	xxx	06/08/2020		09/08/2020	11247	09/08/2020		08/09/2020	44082	#N/A
5	11185	09/08/2020	11185	xxx	09/08/2020		23/08/2020	11260	13/08/2020		23/08/2020	13/08/2020	#N/A
6	11215	08/08/2020	11215	xxx	08/08/2020		11/08/2020	11257	11/08/2020		08/11/2020	44143	#N/A
7	11217	11/08/2020	11217	xxx	11/08/2020		07/08/2020	11259	09/08/2020		08/07/2020	44082	#N/A
8	11225	16/08/2020	11246	xxx	11/08/2020
9	11231	18/08/2020	11239	xxx	10/08/2020
10		09/08/2020	11247	xxx	09/08/2020
11		12/08/2020	11225	xxx	12/08/2020
12		23/08/2020	11260	xxx	13/08/2020
13		11/08/2020	11257	xxx	11/08/2020
14		11/08/2020	11183	xxx	11/08/2020
15		07/08/2020	11259	xxx	09/08/2020
16		09/08/2020	11231	xxx	09/08/2020
Sheet1

Thanks
mg

 

[Fluff]

You've only got 2 values in the item, which is why you get #N/A.
If you want the date, you will have to add it to the item.