How to Compare Two Data Sets using Excel

I think it would be very useful to know how we can compare two data sets easily using Ms Excel by using a simple Excel Macro. This can be modified as you wish based on your requirement.

I usually use this simple macro to compare large data sets with in few seconds.

This sample macro is extracted from Microsoft Support web site

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

Method

• Start Excel.
• Press ALT+F11 to start the Visual Basic editor.
• On the Insert menu, click Module.
• Enter the following code in a module sheet:

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("C1:C5")
    ' NOTE: If the compare range is located on another workbook
    ' or worksheet, use the following syntax.
    ' Set CompareRange = Workbooks("Book2"). _
    '   Worksheets("Sheet2").Range("C1:C5")
    '
    ' 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) = x
        Next y
    Next x
End Sub
     

• Press ALT+F11 to return to Excel.
• Enter the following data (leave column B empty):

A1: 1   B1:     C1: 3
A2: 2   B2:     C2: 5
A3: 3   B3:     C3: 8
A4: 4   B4:     C4: 2
A5: 5   B5:     C5: 0

• Select the range A1:A5.
• In Excel 2003 and in earlier versions of Excel, point to Macro on the Tools menu, and then click Macros.
• In Excel 2007, click the Developer tab, and then click Macro in the Code group.
• Click Find_Matches, and then click Run.
• The duplicate numbers are displayed in column B. The matching numbers will be put next to the first column, as illustrated here:

   A1: 1   B1:     C1: 3
   A2: 2   B2: 2   C2: 5
   A3: 3   B3: 3   C3: 8
   A4: 4   B4:     C4: 2
   A5: 5   B5: 5   C5: 0