Compare Excel docs
Experts,
does Excel have the option to compare docs much like Word does?
I am using 2007.
thank you.
does Excel have the option to compare docs much like Word does?
I am using 2007.
thank you.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
If it is data you can put all spreadsheets in a single file and then compare using another sheet.
For example if you have similarly designed spreadsheets (identical layout) for ie Sales 2010 and Sales 2011 you can have one sheet that checks data between the other two sheets.
Office 2010 has the same functionality as Word - see here
http://office.microsoft.com/en-us/excel-help/merge-copies-of-a-shared-workbook-HP010177169.aspx
2007 doesnt I'm afraid.
There are some third party compare sheets add ons listed here:
http://www.comparesuite.com/solutions/compare_utilities_review/compare-files-excel.htm
and
http://tech.niques.info/easily-compare-excel-spreadsheets/
and a macro that compares two spreadsheets
http://www.exceltip.com/st/Compare_two_worksheets_using_VBA_in_Microsoft_Excel/477.html
For example if you have similarly designed spreadsheets (identical layout) for ie Sales 2010 and Sales 2011 you can have one sheet that checks data between the other two sheets.
Office 2010 has the same functionality as Word - see here
http://office.microsoft.com/en-us/excel-help/merge-copies-of-a-shared-workbook-HP010177169.aspx
2007 doesnt I'm afraid.
There are some third party compare sheets add ons listed here:
http://www.comparesuite.com/solutions/compare_utilities_review/compare-files-excel.htm
and
http://tech.niques.info/easily-compare-excel-spreadsheets/
and a macro that compares two spreadsheets
http://www.exceltip.com/st/Compare_two_worksheets_using_VBA_in_Microsoft_Excel/477.html
OK I see 2007 does not have this compare functionality but 2010 does.
The macro that compares 2 spreadsheets is most interesting to me.
I looked at the link and there is the macro but then something below it and wondering if you know what this is:
Sub TestCompareWorksheets()
I dont know much about code.
How would it know which files you want to compare? Maybe you can read it easily and let me know.
thank you
The macro that compares 2 spreadsheets is most interesting to me.
I looked at the link and there is the macro but then something below it and wondering if you know what this is:
Sub TestCompareWorksheets()
I dont know much about code.
How would it know which files you want to compare? Maybe you can read it easily and let me know.
thank you
In Ole Erlandsen's TestCompareWorksheets, there are examples of comparing two worksheets in the same workbook, as well as two worksheets in different workbooks. Either way, the workbooks need to be open and the references hard-coded in the TestCompareWorksheets macro. You need to pick either the first statement (or the second) and comment out the other. Commenting out means putting a single quote in front of the statement so it doesn't execute:
'CompareWorksheets Worksheets("Sheet1"), Worksheets("Sheet2") 'This statement is commented out
It is possible to write a different version of TestCompareWorksheets that lets the user point to the two worksheets to choose them. The advantage is that the user never needs to touch the code. If you are going to use the macro many times, it is worth the effort to write the new front end. But for a one-time use, it is much simpler just to type in the names of the workbooks and worksheets.
'CompareWorksheets Worksheets("Sheet1"), Worksheets("Sheet2") 'This statement is commented out
Sub TestCompareWorksheets()
' compare two different worksheets in the active workbook
CompareWorksheets Worksheets("Sheet1"), Worksheets("Sheet2")
' compare two different worksheets in two different workbooks
CompareWorksheets ActiveWorkbook.Worksheets("Sheet1"), _
Workbooks("WorkBookName.xls").Worksheets("Sheet2")
End Sub
It is possible to write a different version of TestCompareWorksheets that lets the user point to the two worksheets to choose them. The advantage is that the user never needs to touch the code. If you are going to use the macro many times, it is worth the effort to write the new front end. But for a one-time use, it is much simpler just to type in the names of the workbooks and worksheets.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial