Click here for important information about Computing.net.

Compare two columns in Excel

Tags:
Microsoft Excel 2003 (full product)
excel
default
DELETE
June 15, 2010 at 04:55:24
Specs: Windows XP
Hi.
I would like to compare two columns in Excel. Columns are as follows:

Column A = xyz,123456,abc Column B = 123456

I use the following macro to compare columns where the data in both columns is identical. Can anyone advise how to adapt code when the data is as above?

Sub Find_Matches()

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

Set CompareRange = Range("C72:C102")
' Loop through each cell in the selection and compare it to
' each cell in CompareRange.

For Each x In Selection
For Each y In CompareRange
If x = y Then x.Offset(0, -1) = "match"
Next y
Next x

End Sub

Thanks in advance for your help.


See More: Compare two columns in Excel

Report •

#1
June 24, 2010 at 11:29:29
Hi,

The following code uses the same ranges for data as your original post. The data to be tested is the selected range and it is tested against the data in the range C72 to C102

You will see that I have been a bit pedantic in some of the code - for example specifically using Value, although it may be the default for a range object.

Also I specifically DIM ranges as Range - this makes the IntelliSense work when creating or modifying code.
If VBA knows the variable's type, it can list available functions for it.

For example change y to Variant, then go to y.Text
Delete the period and then insert it - nothing happens.
Do the same with y as Range and you will get Text as an immediate option.

Sub Find_Matches()

Dim CompareRange As Range
Dim x As Range
Dim y As Range

Set CompareRange = Range("C72:C102")
' Loop through each cell in the selection and
' see if cells contain text in the cells in the CompareRange.

For Each x In Selection
    For Each y In CompareRange
        If InStr(1, x.Text, y.Text) <> 0 And y.Value <> "" Then
            x.Offset(0, -1).Value = "match"
        End If
    Next y
Next x
End Sub

Regards


Report •

#2
July 2, 2010 at 05:06:49
Hi Humar

The code work perfectly and is saving loads of time. Thank you so much.


Report •

#3
July 2, 2010 at 07:20:23
You're welcome,

Regards

Humar


Report •
Start a discussion
Related Solutions
See More ↓


Ask Question