compare column a to b and b to a

[ayazgreat]

Hi
I have 800,000 rows data in column a and near about same data in column b, actually I was using vlookup formula to comprare both columns data but it hangs workbook and does not work.
I want a macro to find colomn b value in col a ad copy those values are not found in column a i mean unique values from.col b which are not matched in col a.
And same procedure for column a values to find in col b and copy those values are not found in col b.




https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[Excel Fox]

Ayaz, are you sure all of these records are unique if we were to look at both the columns together, or will there be duplicates at that level too?

[ayazgreat]

There are duplicated values also , most of records are duplicated but I want to compare both columns to see that which records are unique if these are in col a or b

[ayazgreat]

An example a column has values 1,3,4,6,9,11,12 and b column has values 1,2,3,7,9,10,13
When compare col b from col a so result is 2,7,10,13
When compare col a to col b so result is 4,6,11,12

[Rick Rothstein]

An example a column has values 1,3,4,6,9,11,12 and b column has values 1,2,3,7,9,10,13
When compare col b from col a so result is 2,7,10,13
When compare col a to col b so result is 4,6,11,12
Are these examples of real values you have? If so, what is the maximum value that can appear in a column? Depending on your two answers to these questions, there may be a relatively simple code solution. However, if those are values that you actually have, could you post an example with the real values? Again, depending on what they are, there again, may be a relatively simple code solution.

[ayazgreat]

Yes these are values , real.example like
Col a
814090
814091
814092
818000
818001
818002
818003
910010
987012

Col b
814090
814092
814093
814095
818003
818004
818005
818009
818010
818011
910010
910011
910012
987761

The data of both columns are in 800,000 rows of each

[Rick Rothstein]

Yes these are values , real.example like
Col a
814090
814091
..........

Col b
814090
814092
..........

The data of both columns are in 800,000 rows of each
What is the minimum and maximum possible values in each column?

[ayazgreat]

Minimum 400,000 to 500,000 and maximum 10,00,000

[ayazgreat]

Could it be possible to be done through code

[Admin]

Hi

Could you please upload a sample workbook with expected result ?

[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 :cheers:

@Ayaz Please share this forum among your friends :)

[ayazgreat]

You are welcome Ayaz. Thanks for the feedback :cheers:

@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