In case you are wondering how to compare 2 columns of data having rows in millions & extract the unique values among both the columns then you should read this article. We will use VBA code to compare two columns of data & show the difference in next two columns.
Question: There are multiple values which exist in columns A & B. I want a macro to check more than 40 k rows of data in both of the columns & then extract the list of unique entries from each of the column into the next column i.e. columns C & D respectively. In this example, I am taking only 40 rows of sample data.
You can find original question here
Following is the snapshot of data:
To compare the list of two columns, we need to follow the below steps to launch VB editor:
Sub PullUniques() Dim rngCell As Range For Each rngCell In Range("A2:A40") If WorksheetFunction.CountIf(Range("B2:B40"), rngCell) = 0 Then Range("C" & Rows.Count).End(xlUp).Offset(1) = rngCell End If Next For Each rngCell In Range("B2:B40") If WorksheetFunction.CountIf(Range("A2:A40"), rngCell) = 0 Then Range("D" & Rows.Count).End(xlUp).Offset(1) = rngCell End If Next End Sub
In the above code, we will use IF function along with For loop to get the result.
Conclusion: We can get the unique list of text or values from each of the column; the header name of the column C (Results – Exists in List 1 but not in List 2) & column D (Results – Exists in List 2 but not in List 1). In case we have similar requirement but the number of columns are more than two then we need to tweak the code in order to get the result.
If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at firstname.lastname@example.org
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.