compare column a to b and b to a

[ayazgreat]

I am unable to load workbook due to network problem, but i can give you here a view

A B D F
Data1 Data2 Result B compare with A Result A compare with B
81400501 81400501 81400515 81400503
81400502 81400502 81400516 81400504
81400503 81400506 81400517 81400505
81400504 81400507 81400518 81400512
81400505 81400508 84700124 84700133
81400506 81400509 84700125 84700134
81400507 81400510 84700126 84700135
81400508 81400511 84700131 84700136
81400509 81400515 9124800
81400510 81400516 9140012
81400511 81400517 9140013
81400512 81400518 9140014
84700123 84700123
84700127 84700124
84700128 84700125
84700129 84700126
84700130 84700127
84700133 84700128
84700134 84700129
84700135 84700130
84700136 84700131
9124800
9140012
9140013
9140014

[Admin]

Hi

try

Sub kTest()
    
    Dim k, ColA As Object, ColB As Object, i As Long
    
    k = Intersect(ActiveSheet.UsedRange, Range("a:b"))
    
    Set ColA = CreateObject("scripting.dictionary")
    Set ColB = CreateObject("scripting.dictionary")
    
    For i = 2 To UBound(k, 1)
        If Len(k(i, 1)) Then ColA.Item(k(i, 1)) = Empty
        If Len(k(i, 2)) Then ColB.Item(k(i, 2)) = Empty
    Next
    For i = 2 To UBound(k, 1)
        If Len(k(i, 1)) Then
            If ColB.exists(k(i, 1)) Then ColB.Remove k(i, 1)
        End If
        If Len(k(i, 2)) Then
            If ColA.exists(k(i, 2)) Then ColA.Remove k(i, 2)
        End If
    Next
    
    With Range("d2")
        .Offset(-1).Resize(, 2) = [{"Result B compare with A","Result A compare with B"}]
        .Resize(ColB.Count) = Application.Transpose(ColB.keys)
        .Offset(, 1).Resize(ColA.Count) = Application.Transpose(ColA.keys)
    End With
    
End Sub

HTH

[ayazgreat]

thank you very much kris

[Admin]

You are welcome Ayaz. Thanks for the feedback

@Ayaz Please share this forum among your friends

[ayazgreat]

You are welcome Ayaz. Thanks for the feedback

@Ayaz Please share this forum among your friends

Yes kris I must share it among my friends,
Now problem is that When I ran your code to compare 800,000 rows in each column , excel hangs , ok then I give 200,000 rows data then excel hangs again ? should there be limit in your code to hold data ?

[Admin]

Hi

If you have 800,000 rows of data, it's better to switch Access rather than sticking with Excel.

[ayazgreat]

Hi

If you have 800,000 rows of data, it's better to switch Access rather than sticking with Excel.

How should I do it with access ? I need code for it ?

[Admin]

I have asked one of my friends to do this for you. Probably he might give you a solution later in the evening.

[ayazgreat]

I have asked one of my friends to do this for you. Probably he might give you a solution later in the evening.

Thank you very much kris