?
Solved

Compare two excel sheets 2007

Posted on 2010-01-12
9
Medium Priority
?
697 Views
Last Modified: 2012-05-08
Related to this one:  http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_25045946.html#a26288504

Is there a compare tool for Excel 2007 that will compare two different sheets and highlights the cells that are different.  Anyone have one that does that?  Free would be great, but even if it cost a little it would be worth it.
Thanks, Pat
0
Comment
Question by:PatKung
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 21

Expert Comment

by:netcmh
ID: 26297283
0
 
LVL 21

Expert Comment

by:netcmh
ID: 26297320
0
 
LVL 6

Expert Comment

by:Mushq
ID: 26297321
Please check this.
http://www.formulasoft.com/xlsc.html
It has reasonable price for personal license.
http://www.formulasoft.com/xlsc_order.html

There is a trial version of that.

Regards,
Mushi
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 50

Expert Comment

by:Dave Brett
ID: 26298434
Are you cell layouts indentical, ie A10 in sheet 1 should be the same as A10 in sheet2

And are you wanting to compare exact contents, ie formulas, or cell output values?

Cheers

Dave
0
 

Author Comment

by:PatKung
ID: 26298483
Yes, cell layouts are identical.  I used Access to give me which ones were in one table but not the other, and visea versa.  Then I did a one for one match on the unique key which is a one up number.  But now I need to see if there were any changes made in the data, so she can look at it and decide which is correct and which is not.  
Thanks, Pat
0
 
LVL 50

Accepted Solution

by:
Dave Brett earned 2000 total points
ID: 26298720

This code below will compare the used range of two sheets. It will

  • Produce an output sheet  (called "output") which will list the differences (in values, not formulas)
     
  • It will colour the different cells green in the first sheet
It uses variant arrays rather than cell ranges so it will be quick

To use simply run the code, and follow the 2 step instructions to click on a single cell in each of the two sheets to be compared

Cheers

Dave

Sub CompareMe()
    Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
    Dim rng1 As Range, rng2 As Range, rng3 As Range
    Dim X1(), X2(), X3()
    Dim i As Long, j As Long
    Set ws1 = Application.InputBox("Click in any cell in the first sheet", "Select sheet1", , , , , , 8).Parent
    Set ws2 = Application.InputBox("Click in any cell in the second sheet", "Select sheet2", , , , , , 8).Parent
    If ws1.Name = ws2.Name Then
        MsgBox "You picked the same 2 sheets", vbCritical
        Exit Sub
    End If

    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With

    Set rng1 = ws1.UsedRange
    Set rng2 = ws2.Range(rng1.Address)
    On Error Resume Next
    Sheets("Output").Delete
    On Error GoTo 0
    Set ws3 = Worksheets.Add
    ws3.Name = "Output"
    Set rng3 = ws3.Range(rng1.Address)
    X1 = rng1
    X2 = rng2
    X3 = rng3
    For i = 1 To UBound(X1, 1)
        For j = 1 To UBound(X1, 2)
            If X1(i, j) <> X2(i, j) Then
            X3(i, j) = "'" & X1(i, j) & " v " & "'" & X2(i, j)
            rng1.Cells(i, j).Interior.ColorIndex = 4
            End If
        Next j
    Next i
    rng3 = X3

    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With

End Sub

Open in new window

compare.xls
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 26298737
And if you want the oiutput to shows different formulae rather than differnet values

ie
'=SUM(1,2,3,4) v '=SUM(1,2,3)
rather than
'10 v ' 6

then change

  X1 = rng1
  X2 = rng2
  X3 = rng3

to this

Cheers
Dave
0
 

Author Closing Comment

by:PatKung
ID: 31676342
This one worked best of all of them for what I needed.  Thanks, Pat
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 26300167
thx for the grade :)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question