Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.
Hi, everyone.
I'm looking for a very quick (in terms of computations) way of comparing two columns. In a nutshell, I want to compare alpha-numeric data in column A to column B. I need to know if they are exactly the same (same number of rows, same values in each cell, sorted the same, etc). The catch is that since there are around 50,000 rows, I can't use loops in VBA. It takes too long to iterate through all the rows.
Any suggestions?
Cheers,
jj
Re: Quickly compare two columns of data
You certainly can use loops in VBA if you use an array based code rather than a object based code, then even 1,000,000 rows is no problem.
Assuming your data to compare is in columns A and B of each sheet you could try this:
Sub CompareColumnss()
Dim x, i As Long
With Sheets("Sheet1") '// Change sheet name to suit
If .Cells(.Rows.Count, 1).End(xlUp).Row <> .Cells(.Rows.Count, 2).End(xlUp).Row Then
MsgBox "The columns do\ not have the same number of data rows.", , "Columns not identical"
Exit Sub
End If
x = .Cells(1).CurrentRegion
End With
For i = 1 To UBound(x, 1)
If x(i, 1) <> x(i, 2) Then
MsgBox "The columns differs at Row " & i, , "Columns not identical"
Exit Sub
End If
Next
MsgBox "The columns are identical", , "Columns identical"
End SubRe: Quickly compare two columns of data
Perhaps?
Sub IsTheSame()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim x As Integer
x = 0
For Each C In ActiveSheet.Range("A1:A100").Cells
C.Interior.Color = xlNone
If Not C.Value = C.Offset(0, 1).Value Then
x = x + 1
C.Interior.Color = vbYellow
End If
Next C
MsgBox x & " Rows Are Not The Same, I Have Highlighted Them For You."
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End SubRe: Quickly compare two columns of data
KjBox, thank you. I will give that a shot in the next couple days. I think it will do the trick.
yegarboy, thanks, but I can't loop through 50,000+ rows as mentioned in my original post. In terms of usability, the amount of time it takes to loop through that many cells makes it unusable.
Re: Quickly compare two columns of data
Try this alternative to my previous code when you test.
This code will give a message box listing all the rows where the 2 columns differ.
I have tested on 60,000 rows of data that had 1,000 rows differing, the code completed in a fraction of a second and message box contained all incorrect rows.
You could further develop the code to either list the differing rows somewhere or to highlight them.
Sub CompareColumns()
Dim x, y, i As Long, sMsg As String
With Sheets("Sheet1") '// Change sheet name to suit
If .Cells(.Rows.Count, 1).End(xlUp).Row <> .Cells(.Rows.Count, 2).End(xlUp).Row Then
MsgBox "The columns do not have the same number of data rows.", , "Columns not identical"
Exit Sub
End If
x = .Cells(1).CurrentRegion
End With
For i = 1 To UBound(x, 1)
If x(i, 1) <> x(i, 2) Then
If sMsg = "" Then sMsg = i Else sMsg = sMsg & ", " & i
End If
Next
If sMsg <> "" Then
MsgBox "The columns differ at the following Row(s):" & vbLf & vbLf & sMsg, , "Columns not identical"
Else
MsgBox "The columns are identical", , "Columns identical"
End If
End SubRe: Quickly compare two columns of data
Good stuff again. Thanks KjBox. Fortunately, I only need to know if they differ. If they do, the process will be stopped. I had a wrench thrown in the works with my data set, so I won't be able to test this until I get that resolved. I appreciate your time.
Re: Quickly compare two columns of data
OK go with my first code then.
If this is to be used as a check during the run of another procedure then you could replace the message boxes with a Boolean variable which would return True if the columns differ and False if they are identical.
Something like this
Public bColsDiffer As Boolean
Sub CompareColumns()
Dim x, i As Long
bColsDiffer = 0
With Sheets("Sheet1") '// Change sheet name to suit
If .Cells(.Rows.Count, 1).End(xlUp).Row <> .Cells(.Rows.Count, 2).End(xlUp).Row Then
bColsDiffer = 1
Exit Sub
End If
x = .Cells(1).CurrentRegion
End With
For i = 1 To UBound(x, 1)
If x(i, 1) <> x(i, 2) Then
bColsDiffer = 1
Exit Sub
End If
Next
End Sub
Sub OtherMacro()
CompareColumns
If bColsDiffer = 1 Then
MsgBox "Columns not identical, macro will exit.", , "Columns not Identical"
Exit Sub
Else
'your existing code here
End If
End Sub
Note that because bColsDiffer is declared as Public the second code can be in a different module.
Re: Quickly compare two columns of data
Exactly what I was in the process of doing. Thank you. One other question though, what if the two columns are in different worksheets? I'm getting type mismatch errors in the code I've tried.
Edit: Nevermind. I figured it out. I gave a variable an incorrect data type. Thanks for your help. I just ran it on 400,000 rows and it took about half a second. Awesome!
Re: Quickly compare two columns of data
You're welcome.
Re: Quickly compare two columns of data
Hey, I use a tool, Synkronizer, does the job very well. Hope it helps! Cheers!
Don’t have an account yet? Register yourself now and be a part of our community!