Results 1 to 13 of 13

macro to compare 2 sheets row wise and copy unmatched data to 3rd sheet

Threaded View

  1. #1
    Registered User
    Join Date
    11-02-2012
    Location
    banglore
    MS-Off Ver
    Excel 2010
    Posts
    11

    macro to compare 2 sheets row wise and copy unmatched data to 3rd sheet

    I looking for a fast macro dat will compare two excel sheets(containing large amount of data) row wise..here i want to take one row from first excel sheet and compare with all rows in another excel sheet.if any of the row from first sheet completely matches with any of the rows in second excel sheet then its ok ,but if doesnt match then copy dat row to 3rd sheet...

    for example
    Sheet 1

    column1 column2
    aa bb
    dd dd

    Sheet 2

    column1 column2
    e e
    aa bb

    Sheet 3

    column1 column2
    dd dd


    I tried wid the following code:

    Sub Compare()
    '
    ' Macro1 Macro
    '
    ' compare two different worksheets in the active workbook
      CompareWorksheets Worksheets("Sheet1"), Worksheets("Sheet2")
    End Sub
     
    Sub CompareWorksheets(ws1 As Worksheet, ws2 As Worksheet)
      Dim dupRow As Boolean
      Dim r As Long, c As Integer, m As Integer
      Dim lr1 As Long, lr2 As Long, lc1 As Integer, lc2 As Integer, lr3 As Long
      Dim maxR As Long, maxC As Integer, cf1 As String, cf2 As String
      Dim dupCount As Long
     
      Application.ScreenUpdating = False
      Application.StatusBar = "Creating the report..."
      Application.DisplayAlerts = True
      With ws1.UsedRange
        lr1 = .Rows.Count
        lc1 = .Columns.Count
      End With
      With ws2.UsedRange
        lr2 = .Rows.Count
        lc2 = .Columns.Count
      End With
      maxR = lr1
      maxC = lc1
      If maxR < lr2 Then maxR = lr2
      If maxC < lc2 Then maxC = lc2
      DiffCount = 0
      lr3 = 1
      For i = 1 To lr1
        dupRow = True
        Application.StatusBar = "Comparing worksheets " & Format(i / maxR, "0 %") & "..."
        For r = 1 To lr2
            For c = 1 To maxC
                ws1.Select
                cf1 = ""
                cf2 = ""
                On Error Resume Next
                cf1 = ws1.Cells(i, c).FormulaLocal
                cf2 = ws2.Cells(r, c).FormulaLocal
                On Error GoTo 0
                If cf1 <> cf2 Then
                    dupRow = False
                    Exit For
                Else
                    dupRow = True
                End If
            Next c
            If dupRow Then
             Exit For
            End If
         Next r
           If Not dupRow Then
            dupCount = dupCount + 1
            ws1.Range(ws1.Cells(i, 1), ws1.Cells(i, maxC)).Select
            Selection.Copy
            Worksheets("Sheet3").Select
            Worksheets("Sheet3").Range(Worksheets("Sheet3").Cells(lr3, 1), Worksheets("Sheet3").Cells(lr3, maxC)).Select
            Selection.PasteSpecial
            lr3 = lr3 + 1
            ws1.Select
            For t = 1 To maxC
                ws1.Cells(i, t).Interior.ColorIndex = 19
                ws1.Cells(i, t).Select
                Selection.Font.Bold = True
            Next t
         End If
        Next i
    Application.StatusBar = "Formatting the report..."
    'Columns("A:IV").ColumnWidth = 10
    m = dupCount
    Application.StatusBar = False
    Application.ScreenUpdating = True
    MsgBox m & " Rows contain different values!", vbInformation, _
    "Compare " & ws1.Name & " with " & ws2.Name
    End Sub



    its working as required but got stucked for long data.

    Then got an idea to use varient for faster comparison.

    Now clueless how to go ahead...can anyone help me in this ?

    Moderator's Edit: Use code tags when posting code. To do so, select your code and click on the # icon above. I have done it for you this time.
    Last edited by arlu1201; 11-05-2012 at 07:29 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1