Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Compare Two Workbooks & Highlight Differences

  1. #1
    Join Date
    22nd May 2008
    Posts
    1

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Six Strings Guest

    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."

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    20th March 2008
    Posts
    23

    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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,398

    Re: Compare Two Workbooks & Highlight Differences

    try
    Paste this code onto a standard module in Master.xls
    VB:
    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 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    13th June 2012
    Posts
    6

    Re: Compare Two Workbooks & Highlight Differences

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

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

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,398

    Re: Compare Two Workbooks & Highlight Differences

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

    3)
    VB:
    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)

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Compare Data In Two Workbooks And Highlight Matches
    By AbsolutNut2007 in forum EXCEL HELP
    Replies: 4
    Last Post: October 8th, 2012, 19:38
  2. Replies: 1
    Last Post: April 17th, 2008, 10:52
  3. Highlight Differences Between Two Worksheets
    By akeenlearner in forum EXCEL HELP
    Replies: 8
    Last Post: March 31st, 2008, 18:19
  4. Replies: 2
    Last Post: December 6th, 2007, 14:25
  5. Conditional Format to highlight differences??
    By Pylorus in forum EXCEL HELP
    Replies: 9
    Last Post: August 24th, 2005, 21:43

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
  •  
porno