Announcement

Collapse
No announcement yet.

Compare Two Workbooks & Highlight Differences

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Compare Two Workbooks & Highlight Differences



    Hey Guys,
    I would first like to thank all the contributors to this site, it is very useful. I have been looking around this site for a few days now and although I've found some similar problems, I cannot find a solution to my particular problem.

    Basically I have two workbooks. One "base" workbook which is updated by the team on a weekly/monthly basis. I then have another workbook (basically a master copy) which resides on my computer and is shown to the execs on a weekly/monthly basis.

    When the team updates, they can update the following. (They can change the values to anything in columns E-I [in the 2nd workbook, I've changed values in columns E and G], they can add/remove rows, [in the 2nd workbook, I've added row 42 "Denny's-Pizza Hut...], etc)

    I have to go and download the base workbook at the end of every week and then compare line by line and update the master with any changes. This can be very tedious as there are over 300 lines to the original file.

    What I need is a VBA macro that will compare the two workbooks and highlight the differences and (ideally) update the workbook which is on my computer (It can either update the master workbook, or it can create a new workbook).

    Like I said, I found a similar problem, but the solution was to use cell by cell comparisons. This solution would work, however since the team can add and remove rows in the base workbook, if one row is added [ex. In Workbook 2 I've added an empty row at row 45] once the row is added or removed, the cells no longer line up and it shows everything under the newly added/removed row as being different from the original workbook. I've also looked into consolidating code and that only seems to take different worksheets and merge them into one workbook.

    I have attached two worksheets (Master and Book2) with some sample data. Notice how Master has the different cells highlighted in red.

    Thanks in advance for the help, and let me know if you need further clarification.
    Attached Files

  • #2
    Re: Update Macro To Compare Two Workbooks Column By Column And Highlight Differences

    As far as the problem definition, if you are only getting one workbook back from one team, I don't get why you are copying the changes over to Master, which is basically just a copy anyway. What's the difference between the one the team changes and your Master? Or are you getting multiple workbooks back from multiple teams that have to be merged back into a single copy?

    Regardless, Microsoft has designed Excel to handle this very situation. Save your master workbook as "shared", then distribute it for people to mark up. Then when they send the changes back, you do a Tools | Compare and Merge workbooks. It will also keep track of what changed, similar to how Word does "Track Changes."

    Comment


    • #3
      Re: Compare Two Workbooks & Highlight Differences

      i would say what you are looking for is a vlookup with using this method

      =if(iserror(vlookup) "word" (vlookup)

      then do a case statement to highlight the word from the list

      Comment


      • #4
        Re: Compare Two Workbooks & Highlight Differences

        try
        Paste this code onto a standard module in Master.xls
        Code:
        Sub test()
        Dim a, i As Long, ii As Long, w(), b(), n As Long, y, z As String
        With Workbooks("book2.xls").Sheets(1)
            a = .Range("a1", .Range("a" & Rows.Count).End(xlUp)).Resize(,9).Value
        End With
        With CreateObject("Scripting.Dictionary")
            .CompareMOde = vbTextCompare
            For i = 2 To UBound(a,1)
                If Not IsEmpty(a(i,1)) Then
                    z = Join(Array(a(i,1), a(i,2), a(i,3), a(i,4)),";")
                    If Not .exists(z) Then
                        ReDim w(1 To UBound(a,2))
                        For ii = 1 To UBound(a,2) : w(ii) = a(i,ii) : Next
                        .add z, w
                    End If
                End If
            Next
            With ThisWorkbook.Sheets("sheet1")
                a = .Range("a1", .Range("a" & Rows.Count).End(xlUp)).Resize(,9).Value
            End With
            ReDim b(1 To UBound(a,1), 1 To UBound(a,2))
            For i = 2 To UBound(a,1)
                If Not IsEmpty(a(i,1)) Then
                    z = Join(Array(a(i,1), a(i,2), a(i,3), a(i,4)),";")
                    If .exists(z) Then
                        w = .item(z)
                        For ii = 5 To UBound(a,2)
                            If w(ii) <> a(i,ii) Then
                                ThisWorkbook.Sheets("sheet1").Cells(i,ii).Interior.Color = vbRed
                            End If
                        Next
                        .remove(z)
                    Else
                        n = n + 1
                        For ii = 1 To UBound(a,2)
                            b(n,ii) = a(i,ii)
                        Next
                    End If
                End If
            Next
            If .count > 0 Then y = Application.Transpose(Application.Transpose(.items))
        End With
        With ThisWorkbook.Sheets("sheet2").Range("a1")
            .CurrentRegion.ClearContents
            .Resize(,11).Value = [{"Only in Book2","","","","","","","","","","Only in Here"}]
            If IsArray(x) Then .Offset(1).Resize(UBound(y,1) + 1, 9).Value = y
            If n > 0 Then .Offset(,10).Resize(n,9).Value = b
        End With
        End Sub

        Comment


        • #5
          Re: Compare Two Workbooks &amp; Highlight Differences

          Hi Jindon,
          Can you explain me this part of the code ?

          Code:
          With ThisWorkbook.Sheets("sheet2").Range("a1") 
                      .CurrentRegion.ClearContents 
                      .Resize(,11).Value = [{"Only in Book2","","","","","","","","","","Only in Here"}] 
                      If IsArray(x) Then .Offset(1).Resize(UBound(y,1) + 1, 9).Value = y 
                      If n > 0 Then .Offset(,10).Resize(n,9).Value = b 
                  End With
          Thanks.

          Comment


          • #6
            Re: Compare Two Workbooks &amp; Highlight Differences

            1)
            Code:
            .Resize(,11).Value = [{"Only in Book2","","","","","","","","","","Only in Here"}]
            Outputting heading for the result using Evaluate method.
            Equivalent to
            Code:
            .Resize(,11).Value = Array("Only in Book2","","","","","","","","","","Only in Here")
            2)
            Hummm, my bad.. x should be y (should compile before I post)
            Code:
            If IsArray(y) Then .Offset(1).Resize(UBound(y,1) + 1, 9).Value = y
            it comes from
            Code:
            If .count > 0 Then y = Application.Transpose(Application.Transpose(.items))
            If count of dictionay > 0, y will be an array.

            3)
            Code:
            If n > 0 Then .Offset(,10).Resize(n,9).Value = b
            When n = 0, there's no new data in array b.

            Does this help?

            P.S. Ahhh posted on June 5 2008, I remember that I couldn't even compile my code those days. (I had no Excel installed)

            Comment


            • #7
              Re: Compare Two Workbooks &amp; Highlight Differences

              How can we add comment that the value has changed from this value to a particular value?
              Also how to process this method for comparing multiple sheets in same workbook with other workbook?

              Comment


              • #8


                Re: Compare Two Workbooks &amp; Highlight Differences

                DS99

                You need to open a new thread for your own question by the rules here.

                Comment

                Working...
                X