Comparison of two MS Excel Spreadsheets. 7

[ScottXJ]

Hi,

I am looking to compare the values of two MS Excel 2000 spreadsheets. I would like any exceptions to be exported to a separate spreadsheet. Therefore the basic logic breakdown would be as follows:

- Compare spreadsheet A and B
- if values are the same do nothing
- if values are different, export value to appropriate field in the Exceptions spreadsheet

How would I go about accomplishing this? Any assistance would be appreciated.

Thanks,

Scott.

 

[bryanbayfield]

I think the easiest way to do it would be to export both sheets to Access. use the query wizard in Access to get a query where the values of variables are different, then save the query as a spreadsheet.

If you really want, you could automate that ...

 

[BrainB]

If the sheets are identical it's easy peasy.

Just make a 3rd. sheet and put your version of this formula in cell A1 and copy down & across :-
=Sheet1!A1-Sheet2!A1


Regards
BrianB
** Let us know if you get something that works !
================================

 

[xlbo]

This is the programmatic version of what Zathras suggested in the MSOffice forum:

Sub GetDiff()
Dim oSht As Worksheet, nSht As Worksheet, oSht2 As Worksheet, nSht2 As Worksheet
Set oSht = Sheets("Sheet1&quot
Set nSht = Sheets("Sheet2&quot
oSht.Copy after:=Sheets(Sheets.Count)
Set oSht2 = ActiveSheet
nSht.Copy after:=Sheets(Sheets.Count)
Set nSht2 = ActiveSheet

oSht2.UsedRange.Copy
nSht2.Select
ActiveSheet.UsedRange.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlSubtract, SkipBlanks:= _
False, Transpose:=False
osht2.delete
nSht2.name = "Exceptions"
End Sub


Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[Zathras]

It would help if you could be a little more specific about what you want to happen.

For example if sheet A, cell GM4003 has the value 4 and sheet B, cell GM4003 has the value 3 what do you want to see in sheet E?

1. 3 in cell GM4003?

2. 4 in cell GM4003?

3. A line that has "GM4003" in column A, 3 in columm B and 4 in column C?

Give us a little guidance here.

 

[ScottXJ]

Zathras,

I apologize if my original question was not descriptive enough. I certainly didn't mean to cause any difficulty for you in answering my question.

That being said, if I was to pick from your list of desired results, I would have to go with option 3 which would list sheet A's cell values along with sheet B's cell values. However, if it is easier to code option 2 which would simply show the "exception" cell value, that would be fine.

Once again, I apologize for not providing enough info and thanks to all for your quick responses.

Scott.

 

[Zathras]

No need to apologize. But as a fellow programmer, I'm sure you can understand the desire for complete specs before beginning to code.

One more question, if you don't mind. Are you dealing primarily with values, or do you have a few formulas? And if formulas, are you more interested in comparing the values or the text of the formulas themselves? Or both? (Ok, that's 3 questions. )

 

[xlbo]

I accept that it is what you want but please bear with me when I ask you to think hard about this. The route you are currently going down WILL involve having to loop thru ALL cells on a worksheet to do a comparison. Depending on the number of cells with data, this has the potential to be sloooooooooow. Having said that, I do have an idea which may work - will post back

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[Zathras]

Here is one way to do it:

Option Explicit

Sub CompareSheets()
Dim shtA As Worksheet
Dim shtB As Worksheet
Dim shtE As Worksheet
Dim rng As Range
Dim a As Range
Dim b As Range
Dim nErrorOutRow As Long
Dim nFirstRow As Long
Dim nLastRow As Long
Dim nFirstCol As Integer
Dim nLastCol As Integer

  ' Update the following with the actual sheet names:

[/code]
Set shtA = Worksheets("Sheet1&quot
Set shtB = Worksheets("Sheet2&quot
Set shtE = Worksheets("Sheet3&quot
[/code]

  ' Determine rows and cols to test

[/code]
With shtA.UsedRange
nFirstRow = .Cells(1, 1).Row
nLastRow = .Rows.Count + .Cells(1, 1).Row - 1
nFirstCol = .Cells(1, 1).Column
nLastCol = .Columns.Count + .Cells(1, 1).Column - 1
End With
With shtB.UsedRange
nFirstRow = WorksheetFunction.Min(nFirstRow, .Cells(1, 1).Row)
nLastRow = WorksheetFunction.Max(nLastRow, .Rows.Count + .Cells(1, 1).Row - 1)
nFirstCol = WorksheetFunction.Min(nFirstCol, .Cells(1, 1).Column)
nLastCol = WorksheetFunction.Max(nLastCol, .Columns.Count + .Cells(1, 1).Column - 1)
End With
Set rng = Range(Cells(nFirstRow, nFirstCol), Cells(nLastRow, nLastCol))
[/code]

  ' Set up exceptions sheet

[/code]
shtE.Cells.Clear
shtE.Cells(1, 1) = "Ref."
shtE.Cells(1, 2) = shtA.Name
shtE.Cells(1, 3) = shtB.Name
nErrorOutRow = 2
[/code]

  ' Process all cells and compare

[/code]
Application.ScreenUpdating = False
For Each a In shtA.Range(rng.Address)
Set b = shtB.Range(a.Address)
If a.Value <> b.Value Then
shtE.Cells(nErrorOutRow, 1) = b.Address(0, 0)
shtE.Cells(nErrorOutRow, 2) = a.Value
shtE.Cells(nErrorOutRow, 3) = b.Value
nErrorOutRow = nErrorOutRow + 1
End If
Next a
Application.ScreenUpdating = True
shtE.Activate
[/code]

  ' Clean up and terminate

[/code]
Set shtA = Nothing
Set shtB = Nothing
Set shtE = Nothing
Set rng = Nothing
Set a = Nothing
Set b = Nothing
End Sub
[/code]

 

[ScottXJ]

Zathras,

Thanks for being so paitent with me. I should have sent the full specs as I know how many times I have complained to my clients for not providing me with enough information. As for your questions, I am dealing only with values, both string and numeric. There are no formulas to deal with. Does the combination of havig to compare both string and numeric values create any problem?

Thanks,

Scott

 

[Zathras]

Shouldn't be any problem. I think the code above handles either. However, formatting is ignored. If both sheets have the number 42 in cell B2 (for example) but only one is formatted for 2 decimal places, they will not be regarded as different and will not be listed on the exceptions sheet.

Of course, a comparison of formats could easily be added to the routine. (or colors or fonts or comments or whatever.)

 

[xlbo]

Ok - this is my stab at it - thought I'd go a slightly different way -
Sub GetDiffs()
Dim lRow As Long, cols As Integer, i As Integer, refArr As Variant, CompArr As Variant
Dim refSht As Worksheet, compSht As Worksheet, incr As Long
Set refSht = Sheets(&quot;Sheet1&quot
Set compSht = Sheets(&quot;Sheet2&quot
Application.ScreenUpdating = False

lRow = refSht.UsedRange.Rows.Count
cols = refSht.UsedRange.Columns.Count
incr = 1
For i = 1 To cols
refSht.Select
refArr = refSht.Range(Cells(1, i), Cells(lRow, i))
compSht.Select
CompArr = compSht.Range(Cells(1, i), Cells(lRow, i))
For x = 1 To UBound(refArr)
If refArr(x, 1) <> CompArr(x, 1) Then
With Sheets(&quot;Sheet3&quot
.Cells(incr, 1).Value = &quot;R&quot; & x + 1 & &quot;C&quot; & i
.Cells(incr, 2).Value = refArr(x, 1)
.Cells(incr, 3).Value = CompArr(x, 1)
incr = incr + 1
End With
Else
End If
Next

Next i

End Sub

Zathras - I'd be interested in knowing how quickly your code runs - comparing cells vs comparing elements of arrays....FTR mine did 52 cols by 15000 rows in apprx 4 seconds - but I only put about 100 errors in. The main time eater seems to be in writing the exceptions rather than finding them...

Rgds
Geoff
&quot;Some cause happiness wherever they go; others whenever they go.&quot;
-Oscar Wilde

 

[bryanbayfield]

The functionality is already in Access - I don't understand why you guys are intent on doing it in Excel. Why write code for something for which the functionality already exists in the Office Suite? Excel was written in C, I bet Access was too - why should VBA code run faster than the source code?

As I said, if it really must be done automatically you can bind the Access object to Excel's VBE and automate it from there.

The drawback in me saying this is, of course, that I don't know the Access object model! But there must be someone who does ...

 

[xlbo]

Bryan - very true but I don't think I could code that - importing excel sheets into Access tables on the fly and creating the query on the fly........that's why I gave excel solutions - You are right tho - Access could handle this in seconds (even for loads of discrepencies). However - as has been mentioned above, I don't think I could code that. Also - we are not sure whether Scott has (sic) access to Access - I know that I'm one of only a few people here at work that has Access on their 'puter

Rgds
Geoff
&quot;Some cause happiness wherever they go; others whenever they go.&quot;
-Oscar Wilde

 

[Zathras]

Geoff, very nice indeed. My way takes well over a minute.

The only thing you might change is the assumption that the two used ranges are the same. (Or at least document the assumption.)

Scott, if you like Geoff's solution, you can reward him by clicking in his post where it says &quot;Mark this post as a helpful/expert post!&quot; (That is the only compensation we ever receive. We don't work for Tek-Tips. We're just members like yourself.)

 

[bryanbayfield]

OK, xlbo, give me a couple of months and I'll post back here with a solution ;-)

I have bought a book on Office programming - it's taking a while to get through it ...

 

[ScottXJ]

Zathras, Bryan and Geoff:

You guys are awesome! I have incorporated all of the solutions offered and I am in the process of developing the procedure based on your suggestions.

Thanks to all for your help. I will be sure to mark all of your posts as helpful/expert.

Scott

 

[xlbo]

Zathras - absolutely right - I should've mentioned that assumption - also that the data needs to start in A1
Apologies for not mentioning it earlier

Rgds
Geoff
&quot;Some cause happiness wherever they go; others whenever they go.&quot;
-Oscar Wilde

 

[SonJ]

Hi there,

I need to do the same as above ie compare 2 spreadsheets and report the differences between the two. I have read the above and this seems to be exactly what I need to do. However, I seem to be more of a novice than any of you guys, so if one of you could talk me through how I could do this (and I mean really from the start) then it would be much appreciated.

Alternatively, if you have any reference for the above, they too would be much appreciated.

Thanks in advance,
SonD

 

[xlbo]

ok - being as it looks like I'm the 1st to see this, I'll use my example as the example.

You must've read the thread so I assume you are aware of the assumptions I have used in my code.....

That being the case, in excel, press Alt+F11
this will open the Visual Basic Editor

Go Insert>Module
Copy the code from my earlier thread and paste it into the module

There should only b3 3 lines you need to change if your data layout is as per my assumptions

Set refSht = Sheets(&quot;Sheet1&quot'change this to be the name of the sheet where the original data is
Set compSht = Sheets(&quot;Sheet2&quot'change this to be the name of the sheet where the comparison data is

the other line is
With Sheets(&quot;Sheet3&quot
change this to be the name of the sheet where you want your exceptions written to

Then, just go Tools>Macro>Play macro and choose the macro to run

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244