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

 

[Mallesh23]

Hi Fluff,

I have corrected items, getting correct output. But getting date issue
In Dates place month has come and vice versa. expected output GHI, Macro output is KLM. How to sort this issue.

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(Format(Cl.Offset(, -1), "DD/MM/YYYY"), Cl.Value, Format(Cl.Offset(, 2), "dd/mm/yyyy"))
       
         '.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

Book2
	G	H	I	J	K	L	M
1	Post Date	New Transaction ID	Transaction Date		Post Date	New Transaction ID	Transaction Date
2	16/08/2020	11246	11/08/2020		16/08/2020	11246	08/11/2020
3	18/08/2020	11239	10/08/2020		18/08/2020	11239	08/10/2020
4	09/08/2020	11247	09/08/2020		08/09/2020	11247	08/09/2020
5	23/08/2020	11260	13/08/2020		23/08/2020	11260	13/08/2020
6	11/08/2020	11257	11/08/2020		08/11/2020	11257	08/11/2020
7	07/08/2020	11259	09/08/2020		08/07/2020	11259	08/09/2020
Sheet1

 

[Fluff]

Don't format the dates, just use

Cl.Offset(, -1).Value2

 

[Mallesh23]

Hi Fluff,


Tried Cl.Offset(, -1).Value2, Amazing it worked.


But if I use Just value.. it don't give correct output. whats the difference between .value and .value2
Cl.Offset(, -1).Value


Thanks
mg

 

[Fluff]

If a cell is formatted as a date value will return a VBA date, whilst value2 will return the underlying number.

 

[Mallesh23]

Hi, Fluff,

Thanks again for your help, I really likes your approach of array and dictionary . ?


Thanks
mg

 

[Fluff]

My pleasure.