Skip to main content

Excel VBA compare two columns



How to compare two columns in Excel?

Comparing two columns in Excel for the same or different values is quite easy or can even be done manually.

Manual compare is ideal if trying to compare, few lines of data but it will not be a good idea if there are hundreds or thousands of rows that needs to be compare against another column.

Below is an Excel VBA code on how to compare two columns. This VBA code will only compare values side by side.

There are some other factors that needs to be considered when comparing data if VBA will be used for comparison.

One issue would be data consistency, on how the data has been inputted or typed into Excel.

Leading and trailing spaces will also play a big factor when VBA or an Excel Macro is used to compare.

For example if the cell has the same value and if it is manually compared of course the user who is comparing manually will notice that its equal or the cells contains same value; If space/s is not a concern.

But for VBA comparison, a cell with a space and a cell without a space will be considered not the same value.

In Excel Macro or Visual Basic Applications (VBA) is used to compare, it will consider the leading and trailing space as a value.

So even though, the cell has the same contents but the leading and trailing space is not the same.

Excel VBA will not consider the two cells as equal.

But no need to worry about leading and trailing spaces, Microsoft engineers has recognized this issue already and has provided a TRIM command.

TRIM command in VBA will trim leading and trailing spaces.

For those who are new to concept of Trailing and Leading space, please check picture below.

LEADing space, as the first word implies “LEAD” is the space/s in Front of the cell. Like to “LEAD” is to let others show; a good leader should not just sit down and watch the whole team collapse. 

Trailing space/s is the space that are lagging behind or space that is found at the end of the cell value.

In Word it’s quite easy to check trailing and leading space, you just need to enable the option to show or hide symbols in paragraph. But Excel does not provide this option.

Please check images below to understand leading and trailing spaces.











This code below will only compare columns side by side, if the other column is equal to the other side. If the columns are equal it will write “Same” or if it is not the same it will write “XX”.

Image below is the sample output for the VBA code (SidebySide_matches()) which is provided below:


Below is the VBA code on how to compare two columns side by side:

The data compared is on Column B and Column C, and result is placed on Column A.

Change the columns as necessary depends on your worksheet range addresses. 

If the data compared is not on Column B or Column C, just change the code as per where your data is placed.   

To run and compare the data without changing the VBA code (copy and paste the code below), then placed the data on Column B and C. Run the code and the result is shown in Column A.

Sub SidebySide_matches()

Dim CompareRange As Variant, x As Variant, y As Variant
Dim varr As Variant

' Find the last row value on the sheet
'Adjust the range value or the letter address depends on where the data is placed
varr = Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row).Value

For Each xValue In varr
x = x + 1 'increment the cell row value for comparison

CompareRange = Cells(x, 3).Value 'Is equals to (Range(x,"C").value
'MsgBox xValue & " - " & CompareRange

'MsgBox UCase(Trim(xValue)) & " == " & UCase(Trim(CompareRange))
'Trim the spaces and temporarily set the values to upper case for comparison

If UCase(Trim(xValue)) = UCase(Trim(CompareRange)) Then

'Message box uncomment to show how VBA works on the background but it will be slow
'MsgBox xValue & " - " & CompareRange
'Set the  Column A to same if B (row) value is equals to C (row) value

 Cells(x, 1).Value = "Same"

 Else

 Cells(x, 1).Value = "XX"

End If

Next 

End Sub


=================

What if there is a need to find duplicates between two columns and not just compare?

Microsoft has provided a code on how to find duplicates and compare two columns, just tweak the code to show the cell address that has duplicate values. 

So it won’t be hard where to find the values that has duplicate values with the other cell.

This Knowledge Base link from Microsoft shows on how to use VBA to compare data in two columns to find duplicates in Excel.


Here’s the code that has been modified from above link:

The data compared is on Column B and Column C, and result is placed on Column A. Change the columns as necessary depends on your worksheet range addresses.

Sub Find_Matches()

    Dim CompareRange As Variant, x As Variant, y As Variant
    ' Set CompareRange equal to the range to which you will
    ' compare the selection.

    Set CompareRange = Range("B1:B10")
    Set xCRange = Range("C1:C10")
  
    For Each x In xCRange
        For Each y In CompareRange

           ' MsgBox x & " == " & y & "--" & xCRange.Address
            If UCase(Trim(x)) = UCase(Trim(y)) Then y.Offset(0, -1) = x & " Dups with: " & y.Address & " with " & x.Address  
     
        Next  y       
    Next  x

    Columns("A:A").EntireColumn.AutoFit

End Sub

This code: y.Offset(0, -1) = x  means place the value at Column A.

Why column A? 

y value is the point of reference since the offset command is taken from y. (y.offset)

 Set CompareRange = Range("B1:B10")
    Set xCRange = Range("C1:C10")
  
    For Each x In xCRange
        For Each y In CompareRange

x is equals to xCRange which is Column C.

y  is equals to CompareRange which is Column B.

.Offset in VBA is to placed the value parallel to the  address specified on the command.

So y.Offset(0, -1) means  Zero is don't care the row, and place the value before Column B, -1 (negative one) implies to placed the value past column B.

If the statement has been 
y.Offset(0, 1)  which  is positive 1. Then this will overwrite the values of Column C. Since y is the point of reference.

Try changing the VBA code value to y.Offset(0, 1) and C column will be overwritten with the result of the VBA code.

But there is no point to overwrite C column since it is the compared values. But if you are trying to wrap over your head, on how .Offset command in VBA works, then it is a good exercise.

But do this in raw or test data and not on the actual data, or else it will be a disaster.

Image below is a sample output of the code above:


Hope it will help and  simplify the task of manually comparing row by row.


Cheers!!!

Comments

Popular posts from this blog

Copy a single file using robocopy

Copy a single file using robocopy from a local folder to a shared folder on the network.
A simple rule of thumb before any disaster strike, don't interchange the source and the destination.

If source and destination is mistakenly reverse, files might get overwritten. To avoid any loss of data do a test with a dummy file to ensure things work perfectly.
Robocopy [source][destination]   [file to be copied]
robocopy c:\local_c_folder  \\PC_network\shared_folder   file_to_be_copied_xx.txt
The command will be completed successfully provided the network access right has no issues.

Robocopy works quite good on large files. A simple copy or xcopy command will also work but the speed might vary.

Robocopy is free it can be accessed from command line. No need to install the resource kit tool if the operating system is Windows 7 or newer version.

Copy files with selected file extension using PowerShell and Robocopy:

$extension = ('.pdf', '.jpg', '.txt')
gci d:\WorkFolde…

WMIC get computer name

WMIC get computer model, manufacturer, computer name and  username.
WMIC is a command-line tool and that can generate information about computer model, its manufacturer, its username and other informations depending on the parameters provided.
Why would you need a command line tool if there’s a GUI to check?
If you have 20 or 100 computers, or even more. It’s quite a big task just checking the GUI to check the computer model and username.
If you have remote computers, you need to delegate someone in the remote office or location to check.
Or you can just write a batch file or script to automate the task.
Here’s the code below on how get computer model, manufacturer and the username.
Open an elevated command prompt and type:
wmic computersystem get "Model","Manufacturer", "Name", "UserName"
Just copy and paste the code above, the word “computersystem” does not need to be change to a computer name.
A sample output below will be generated if the co…