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.

http://support.microsoft.com/kb/213367

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!!!

=========================
Linux Android App cheat sheet:
https://play.google.com/store/apps/details?id=com.LinuxMobileKit

Free Android Apps:

Click on links below to find out more:

https://play.google.com/store/apps/details?id=soulrefresh.beautiful.prayer

Catholic Rosary Guide  for Android:

https://play.google.com/store/apps/details?id=com.myrosaryapp

http://quickbytesstuff.blogspot.sg/2014/09/how-to-recite-rosary.html

Divine Mercy Chaplet Guide (A Powerful prayer):

https://play.google.com/store/apps/details?id=com.dmercyapp



Educational Android App for Kids:

https://play.google.com/store/apps/details?id=com.xmultiplication

https://play.google.com/store/apps/details?id=com.letsmultiply

Labels:

Comments

Post a Comment

Popular posts from this blog

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...
Post a Comment
Read more

Notepad++ convert multiple lines to a single line and vice versa

Notepad++ is an awesome text editing tool, it can accept regex to process the text data. If the data is in a “.csv” format or comma separated values which is basically just a text file that can either be opened using a text editor, excel or even word. Notepad++ can process the contents of the file using regex. Example if the data has multiple rows or lines, and what is needed is to convert the whole lines of data into a single line. Notepad++ can easily do it using regex. However, if the data is on a single line and it needs to be converted into multiple lines or rows then regex can also be used for this case. Here’s an example on how to convert multiple rows or lines into a single line. Example data: Multiple rows, just a sample data. Press Ctrl+H, and  on "Find what" type: [\r\n]+ and on "Replace with" type with: , (white space) --white space is needed if need to have a space in between the data. See image below, "Regular Expression" must be se...
1 comment
Read more

Print error 016-799 - Fuji Film Xerox

016-799 Fuji Xerox or Fuji Film print error code. That shows a description error as “Print instruction Fail detected in decomposer.” The error code and error description are alien languages for users and even system administrators who are not familiar with Fuji Xerox error code. The error code is quite simple and easy to fix, if the job print goes to the printer but print out doesn’t come out. So, basically the print job was received by the printer, but the printer just doesn’t know what type of paper or what size to use or which tray to utilize for the print out. In some instances, this is just a paper mismatch but the error description; if using Windows 10 to print does not exactly points to what is the issue. First thing to check, is the paper size selected by the user to print. Example, if the printer configuration is A3 and A4 sizes only. But then the person printing the file accidentally chooses “A4 Cover” then this error 016-799 will occur. ...
6 comments
Read more