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.

 

[SonJ]

Hi xlbo,

Thanks for the walk through of your solution. I understand the way that it works. I need to be able to format the results to be in the same tabular format that they are in initially.

At the moment, I have 10 columns and will have 60ish rows of data that will need to be compared. Ideally, when I run the macro, I would like to see the header row for the column and the values from sheet 2 where they do not match the value in sheet 1. If they do match sheet 1 then for a blank to be in that space to indicate that the values in the 2 sheets are the same.

Any insight on how I could do this would be much appreciated.

Thanks in advance,
SonD

 

[xlbo]

no need for code then

use
=if(Sheet1!A2=sheet2!A2,"",sheet2!A2)

copy your headers across then put the above formula in sheet3 A2 and copy across and down

If you really want to put it in a macro, then

lRow = sheets("Sheet1&quot.cells(65536,1).end(xlup).row
sheets("Sheet1&quot.Range("A1:J1&quot.copy destination:=sheets("Sheet3&quot.range("A1&quot

with sheets("Sheet3&quot
.range("A2:J" & lRow).formula = "=if(Sheet1!A2=sheet2!A2,"""",sheet2!A2)"
end with

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

 

[SonJ]

Thanks very much for your help xlbo. Your help has allowed me to acheive the desired results.

SonD

 

[Crystalguru]

Using Geoff's solution of comparing two spreadsheets:
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("MySheet1&quot
Set compSht = Sheets("MySheet2&quot
Application.ScreenUpdating = False

I wasn't sure if it would meet my needs...I am trying to compare two columns (A from first spreadsheet & AB from second spreadsheet) I did copy/paste it, but since I am a novice,I was not sure of the code.

The column would be red and a error message would print on the second spreadsheet (column AW) as "No match".

thanks

 

[xlbo]

Slight amendment - actually makes it easier:

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("Sheet1&quot
Set compSht = Sheets("Sheet2&quot
Application.ScreenUpdating = False

lRow = refSht.UsedRange.Rows.Count
refSht.Select
refArr = refSht.Range(Cells(1, 1), Cells(lRow, 1)) 'Loads Column A
compSht.Select
CompArr = compSht.Range(Cells(1, 28), Cells(lRow, 28)) 'Loads column AB on sheet2
For x = 1 To UBound(refArr)
If refArr(x, 1) <> CompArr(x, 1) Then
With compSht
.Cells(x, 49).Value = &quot;No Match&quot;
.Cells(x, 28).Interior.ColorIndex = 3
End With
Else
End If
Next
End Sub

As per before, you will need to change the sheet names to reflect your sheet names

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

 

[Crystalguru]

I put this code behind my Validate button. When I test, nothing happens. I feel I may have this incorrect.

Here is my code:
Private Sub CommandButton1_Click()
Dim i As Integer
Dim z As Integer
z = TextBox1.Value
Cells(1, 49).EntireColumn.Clear
For i = 2 To z
mat_name = Cells(i, 1).Value
mat_type = Cells(i, 13).Value
equip_type = Cells(i, 14).Value
Module = Cells(i, 29).Value

If mat_name = &quot;&quot; Then
Cells(i, 1).Interior.ColorIndex = 3
Cells(i, 49).Value = Cells(i, 49).Value + &quot;Material Name is required &quot;
ElseIf Len(mat_name) > 80 Then
Cells(i, 1).Interior.ColorIndex = 6
Cells(i, 49).Value = Cells(i, 49).Value + &quot;Material Name is longer than 80 characters &quot;
Else
Cells(i, 1).Interior.ColorIndex = xlNone

End If

If mat_type = &quot;&quot; Then
Cells(i, 13).Interior.ColorIndex = 3
Cells(i, 49).Value = Cells(i, 49).Value + &quot; Material Type is required, &quot;
ElseIf ((mat_type <> &quot;Case Cart&quot And (mat_type <> &quot;Drug&quot And (mat_type <> &quot;Equipment&quot And (mat_type <> &quot;Implant&quot And (mat_type <> &quot;Instrument&quot And (mat_type <> &quot;Supply&quot And (mat_type <> &quot;Tray&quot) Then
Cells(i, 13).Interior.ColorIndex = 6
Cells(i, 49).Value = Cells(i, 49).Value + &quot; Material Type can only be Case Cart,Drug, Equipment,Implant,Instrument,Supply, Tray &quot;
Else
Cells(i, 13).Interior.ColorIndex = xlNone

End If

If mat_type = &quot;Equipment&quot; And ((equip_type <> &quot;Basic&quot And (equip_type <> &quot;Cautery&quot And (equip_type <> &quot;Laser&quot And (equip_type <> &quot;Tourniquet&quot) Then
Cells(i, 14).Interior.ColorIndex = 3
Cells(i, 49).Value = Cells(i, 49).Value + &quot; Equipment is required, &quot;
Else
Cells(i, 14).Interior.ColorIndex = xlNone

End If

If Module = &quot;&quot; Then
Cells(i, 29).Interior.ColorIndex = 3
Cells(i, 49).Value = Cells(i, 49).Value + &quot;A module is required &quot;
Else
Cells(i, 29).Interior.ColorIndex = xlNone

End If
Next
End Sub

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;Manufacturer&quot
Set compSht = Sheets(&quot;Materials&quot
Application.ScreenUpdating = False

lRow = refSht.UsedRange.Rows.Count
refSht.Select
refArr = refSht.Range(Cells(1, 1), Cells(lRow, 1)) 'Loads Column A
compSht.Select
CompArr = compSht.Range(Cells(1, 28), Cells(lRow, 28)) 'Loads column AB on sheet2
For x = 1 To UBound(refArr)
If refArr(x, 1) <> CompArr(x, 1) Then
With compSht
.Cells(x, 49).Value = &quot;No Match&quot;
.Cells(x, 28).Interior.ColorIndex = 3
End With
Else
End If
Next
End Sub


I have tried stepping through the code. It errors with a Subscript out of range at the first step into...

thanks

 

[xlbo]

ok - you have 2 subs here - which are you running ??

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

 

[Crystalguru]

I need to run both. But am unsure how/where to put it. I just &quot;put&quot; it somewhere to see if it would work...

The first sub checks these columns
mat_name ,mat_type, equip_type, Module

I also need the second sub to check the 2nd spreadsheet column A against the 1st spreadsheet column AW.

Am I getting in over my head here?

thanks

 

[xlbo]

Nope - just add a line to the bottom of the 1st sub:

Else
Cells(i, 29).Interior.ColorIndex = xlNone

End If
Next
Call GetDiffs
End Sub


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

 

[Crystalguru]

Code worked up to this line:

refArr = refSht.Range(Cells(1, 1), Cells(lRow, 1)) 'Loads Column A

Error message:
Method 'Range' of object '_worksheet' failed.

Did a step through the code, it fails on the line above. I also did a local watch and it doesn't seem to evaluate.

thanks

 

[xlbo]

What does lRow evaluate to ??

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

 

[Crystalguru]

In my locals window, while stepping through:
lRow :
Value = 0
Type = long

Make sure I have this right - lRow is a LRow?

 

[Crystalguru]

Ah, I stepped over lRow and it gave me values:
lRow:
Value: 5 ( I put 5 rows of info on Manufacturer spreadsheet)
Type: Long

 

[xlbo]

So it fails on that line but the refSht.select works and there is data in rows 1 - 5 in column A ??

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

 

[Crystalguru]

Here's what I get when I step through my code:

lRow = refSht.UsedRange.Rows.Count '5rows on Column A spreadsheet &quot;Manufacturer&quot; Sheet3

refSht.Select 'No errors
refArr = refSht.Range(Cells(1, 20), Cells(lRow, 20)) 'Loads Column A - While stepping over this line I get the error 1004 - &quot;Application-defined or Object-defined error&quot;

compSht.Select 'steps over fine
CompArr = compSht.Range(Cells(1, 28), Cells(lRow, 28)) 'Loads column AB on sheet2- Works fine

Couldn't test the following due to the reference to refArr.
For x = 1 To UBound(refArr)
If refArr(x, 1) <> CompArr(x, 1) Then
With compSht
.Cells(x, 49).Value = &quot;No Match&quot;
.Cells(x, 28).Interior.ColorIndex = 3
End With
Else
End If
Next

 

[xlbo]

refArr = refSht.Range(Cells(1, 20), Cells(lRow, 20)
why are you using 20 ?? that would refer to column T. For column A use 1. This is probably why it is falling over - if there is no data to load it'll crash

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

 

[Crystalguru]

The 20 was for the number of columns in my spreadsheet. Just trying to figure this out. See where it gets me.

I re-copied the code you sent. So, it's not screwed up by me. Stepped through it and still got the Error 1004 on the refArr statement.



FYI:
I am using Excel 2000 on a XP professional machine.

 

[xlbo]

Whay are you using the number of columns in the spreadsheet ??

From your 1st post, you indicated that you wanted to compare values in Col A from worksheet1 with values from col AB in worksheet2 and ouput an error messge to column AW in the 2nd worksheet where the values didn't match. For your layout, refSht (Sheets(&quot;Manufacturer&quot should have data in col A, compSht (Materials) should have data in AB. Where it doesn't match, row by row, an error message is written to col AW on the Materials sheet

When I put test data in rows 1-30 of a sheet called &quot;Manufacturer&quot;, copy it to col AB of Materials and change some of the values, then run the sub, it works perfectly. You must've changed something - either in your specification or in your workbook.....using 2002 on XP but the code should work just fine with 2000. Loading compSht to an array works (as you have already posted as much)- QED the methodology is sound - we just need to get the setup right ;-)

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

 

[Crystalguru]

Like I said, I am new and when I ran across the error, I assumed it was the column number. The code is back to it's original form.

Yes, I do want to compare values from Col A from Worksheet1 with values from col AB in worksheet2 and output an error message to column AW. (Couldn't have said it better myself!)

Yes, sheet &quot;manufacturer&quot; does have 5 rows of data. Sheet &quot;Materials&quot; has 5 rows also. With one not matching.

At the top of my materials spreadsheet, there is a text box that I put the number of rows in my spreadsheet - for the first sub routine (I didn't know how to tell VBA to check for number of rows), then I click on the button. Would this be a problem?

I think I have the set up pretty messed up!

 

[xlbo]

Right - I'm pretty sure that I know what the problem is. My code picks up data from row 1. If your data starts in row 2 (which it sounds like it does, the code will need amending a little.

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'This gets the last row with data in
refSht.Select
refArr = refSht.Range(Cells(2, 1), Cells(lRow, 1)) 'Loads Column A
compSht.Select
CompArr = compSht.Range(Cells(2, 28), Cells(lRow, 28)) 'Loads column AB on sheet2
For x = 1 To UBound(refArr)
If refArr(x, 1) <> CompArr(x, 1) Then
With compSht
.Cells(x, 49).Value = &quot;No Match&quot;
.Cells(x, 28).Interior.ColorIndex = 3
End With
Else
End If
Next
End Sub

Where there is a 2 in bold, this means that it indicates the start row for the data to be picked up so if your data actually starts in row 3, change it to 3. Teh 1st sets the start row for the data in Col A, the 2nd sets it for the data in col AB. Hope that makes sense.

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