• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro to compare 2 worksheets and return differences in a new sheet

Aps

New Member
Hi,

I need to write a VBA macro to compare all except 1 cell in 2 worksheets and the rows which have differences in cell values should be pasted in a new worksheet with the different cells highlighted.

So far I have done a vlookup in sheet1 (Hdr-CV40) with column B in sheet2 (Hrd-Loaded) and then filtered the values in sheet1 that are not equal to #N/A. I have then sorted the data in sheet1 and sheet2 so that the rows in both sheet match.

However, I am not sure how to proceed with comparing the cell values in these 2 sheets and then pasting it to the new sheet (Hdr-Mismatch).

Any help would be highly appreciated.

Code:
Sub MismatchData()
Dim SourceLastRow As Long
Dim OutputLastRow As Long
Dim ResultLastRow As Long
Dim sourceBook As Workbook
Dim sourceSheet As Worksheet
Dim outputSheet As Worksheet
Dim resultSheet As Worksheet
Dim pasteRange As Range
Dim saveSource As Boolean

Application.ScreenUpdating = False

'what are the names of our worksheets?
Set sourceSheet = ThisWorkbook.Worksheets("Hdr-Loaded")
Set outputSheet = ThisWorkbook.Worksheets("Hdr-CV40")
Set resultSheet = ThisWorkbook.Worksheets("Hdr-Mismatch")

'Determine last row of source
With sourceSheet
  SourceLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
With outputSheet
  'Determine last row in col CP
  OutputLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
  'Apply our formula
  .Range("CP2:CP" & OutputLastRow).Formula = _
  "=VLOOKUP(B2,'" & sourceSheet.Name & "'!$B$2:$B$" & SourceLastRow & ",1,0)"
 
  'Filter the data
  .Range("$A$1:$CP$" & OutputLastRow).AutoFilter Field:=94, Criteria1:="<>#N/A"
 
  'Sort the data
  Range("A1:CP" & OutputLastRow).Sort _
  Key1:=Range("B1"), Order1:=xlAscending, Header:=xlYes, MatchCase:= _
  False, Orientation:=xlTopToBottom
 
saveSource = True
 
End With

Application.ScreenUpdating = True
End Sub
 
Last edited:
I wasn't sure that did You really mean to compare sheets 1 & 2 except 1st row.
Anyway ... here You are.
I can modify this after You'll give more details, Okay?
 

Attachments

  • Sample File.xlsm
    36.9 KB · Views: 60
Thanks Vletm.

In the 2 sheets I only want to compare the filtered and sorted vlookup data in Sheet1 (Hdr-CV40). Sheet2 is a subset of sheet1 so I only want to compare records where column B in both sheet matches. Also, column L should not be compared as it will always be blank in Sheet1 and populated in Sheet2.

Also, in the output sheet is it possible to paste the header from Sheet2 and only the Sheet2 rows with mismatch values along with formatting and column widths?

I have created a sample output file that may explain this better.
 

Attachments

  • Sample File.xlsm
    40.9 KB · Views: 29
Hi,

easier with a result worksheet !

No need to filter Sheet1 with this demonstration :​
Code:
Sub Demo()
Dim Rg As Range
Set Rg = Sheet1.Cells(1).CurrentRegion.Columns(2)
Application.ScreenUpdating = False
With Sheet4.UsedRange:  .RowHeight = Sheet4.StandardHeight:  .Clear:  End With
                                L& = 1
With Sheet2.Cells(1).CurrentRegion.Rows
    .Item(1).Copy Sheet4.Cells(1)
  
    For R& = 2 To .Count
                     V = Application.Match(.Cells(R, 2).Value, Rg, 0)
        If IsNumeric(V) Then
                B% = 1
            For C& = 1 To .Columns.Count
                If C <> 12 And .Cells(R, C).Value <> Sheet1.Cells(V, C).Value Then
                    If B Then B = 0: L = L + 1: .Item(R).Copy Sheet4.Cells(L, 1)
                    Sheet4.Cells(L, C).Interior.ColorIndex = 36
                End If
            Next
        End If
    Next
End With
  
Set Rg = Nothing
Application.Goto Sheet4.Cells(1), True
Application.ScreenUpdating = True
End Sub
Do you like it ? So thanks to click on bottom right Like !
 
  • Like
Reactions: Aps
Hi,

I have another requirement that has come up for comparing sheets. It is possible that some columns that are blank in sheet1 (Hdr-CV40) are populated in sheet2 (Hdr-Loaded). In such a scenario we do not want to compare the columns. Is it possible to adjust the macro such that it only compares columns where sheet1 cell is not null? This needs to be done only for records where column B in both sheet matches.

Attached is the sample sheet.

Also, I am trying to learn macro and it will be great if you can help me with some comments in the code so that I can extend the macro to a few other files without bothering you again.
 

Attachments

  • Sample File.xlsm
    42.7 KB · Views: 32
Just mod the line​
Code:
If C <> 12 And .Cells(R, C).Value <> Sheet1.Cells(V, C).Value Then
to​
Code:
If C <> 12 And .Cells(R, C).Value <> Sheet1.Cells(V, C).Value And Sheet1.Cells(V, C).Value > "" Then

Application.Match refers to MATCH Excel worksheet function
(so see Excel's help) …

IF B Then means IF B <> 0 Then

Apart from these two exceptions, all statements are within VBA inner help :
just place text cursor on a statement and hit F1 key then just read,
an easy way to learn !

And Macro recorder is the beginner best friend …
 
  • Like
Reactions: Aps
Back
Top