Compare and Print/Highlight diffs between two worksheets

[NewUserMo]

Hello All,

Hoping to get your input on a compare excel worksheet problem. I have two tabs (OLD and NEW), the OLD tab contains data from the previous month and NEW tab contains data from the current month. Each tab has 2000 plus rows and 25 columns. Data in OLD tab can be in NEW tab. Also, the data is not in the same order and the NEW tab can have more or less rows. I am trying to do the following:

1. Highlight/Print in DIFF tab the first 10 columns that are in NEW but not OLD - do not match
2. Highlight OLD items that are not in NEW tab - cells that do not match

My problem is that the data is not rows in both tabs are not equal. SkipV was kind enough to provide the below, but it is not working the way I describe above



Sub compareSheets(shtSheet1 As String, shtSheet2 As String)

Dim mycell As Range, xl As Application
Dim mydiffs As Integer

Set xl = Application
'For each cell in sheet2 that is not the same in Sheet1, color it yellow

With ActiveWorkbook
For Each mycell In .Worksheets(shtSheet2).UsedRange
If IsError(xl.Match(mycell.Value, .Worksheets(shtSheet1).Columns(mycell.Column), 0)) Then

mycell.Interior.Color = vbYellow
mydiffs = mydiffs + 1

End If
Next

'Display a message box to demonstrate the differences
MsgBox mydiffs & " differences found", vbInformation

.Sheets(shtSheet2).Select
End With
Set xl = Nothing
End Sub

 

[SkipVought]

Hi,

What's the ultimate objective here? Certainly not to merely highlight cells, is it?

 

[NewUserMo]

Hi,

I am trying identify what cells changed month over month, what rows are deleted month over month and what new rows have been added. I am thinking it maybe best to show diffs in another tab

Thanks

 

[SkipVought]

I'd use Microsoft Query to return the deleted rows and then the added rows. This assumes that there is ONE field named Key that uniquely joins the two tables/tabs. If this is not the case, we need to see the table headings (assumed to be in ROW 1 starting in COLUMN A)

This returns the rows no longer in NEW tab (REMOVED)

SELECT a.* 
FROM {oj `OLD$` a LEFT OUTER JOIN `NEW$` b ON a.Key = b.Key}
WHERE b.Key IS NULL

This returns the rows ADDED to the NEW tab

SELECT b.*
FROM {oj `OLD$` a RIGHT OUTER JOIN `NEW$` b ON a.Key = b.Key}
WHERE a.Key IS NULL

So here are my test tables:

[pre]OLD:
Key field
a 1
s 2
d 3
f 4
[/pre]

[pre]NEW:
Key field
s 2
d 3
f 4
g 5
[/pre]

[pre]REMOVED:
Key field
a 1
[/pre]

[pre]ADDED:
Key field
g 5
[/pre]

 

[SkipVought]

Okay, let's use this thread to reach a conclusion for this issue.

 

[SkipVought]

The reason I posted my MATCH() solution in your original thread (SEE THE CONFUSION ALREADY?) is that your original code did a CELL BY CELL compare. (for each cell in usedrange) You did not give us ANY information about the data structure. We have no idea if this is a valid table of some kluge of columns of stuff. Is there a unique key for any row of data if this is a table?

You have assumed an approach. Turns out there were problems in your assumed approach. Now you're trying to modify your approach rather than determining if there might not be a better one.

And we still do not know what the ultimate objective is. First it was to highlight cells in the NEW data sheet, Now it is to show DIFFS in another tab. So what do you intend to do with the DIFFS data, just look at it? Count the rows?

Above I posted a solution that generates the NEW rows (DIFFS) and the deleted rows (DIFFS). NO COMMENT?

 

[NewUserMo]

Hi SkipV,

Sorry about not responding back sooner. I am not familiar with using SQL in VBA code. I was not sure how to ask the question so I found some code on the site that thought would help resolve my issue. I think the SQL will help identify my deletes and new items but it will not resolve the issue of the compare order still being showing false breaks.

Thanks for you input.

 

[SkipVought]

but it will not resolve the issue of the compare order still being showing false breaks."

This is an allegation without supporting evidence. We have no evidence of your specific instance as referenced! You have provided no example at all to support your statements!

 

[NewUserMo]

Fair point.

OLD
Person System Delivery
Joe PC 1/10/2015
Mo Mac 2/15/2020
Allan Mobile 5/18/2015
Sam PC 12/31/2015


New
Person System Delivery
Joe PC 1/10/2015
Allan Mobile 11/1/2015
Sam PC 12/31/2015

Results of VBA code:
Diff
Ref. OLD NEW
A3 Mo Allan
B3 Mac Mobile
C3 2/15/2020 11/1/2015
A4 Allan Sam
B4 Mobile PC
C4 5/18/2015 12/31/2015
A5 Sam
B5 PC
C5 12/31/2015

 

[Andrzejek]

Please, start using TGML tags to show your code and your examples.

Is that your data:

[pre]
OLD
Person System Delivery
Joe PC 1/10/2015
Mo Mac 2/15/2020
Allan Mobile 5/18/2015
Sam PC 12/31/2015

New
Person System Delivery
Joe PC 1/10/2015
Allan Mobile 11/1/2015
Sam PC 12/31/2015

Results of VBA code:
Diff
Ref. OLD NEW
A3 Mo Allan
B3 Mac Mobile
C3 2/15/2020 11/1/2015
A4 Allan Sam
B4 Mobile PC
C4 5/18/2015 12/31/2015
A5 Sam
B5 PC
C5 12/31/2015 [/pre]



Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[NewUserMo]

Thanks Andy for updating. Can anyone assist in modifying VBA code?

 

[zelgar]

So....

In the examples listed above, you'd want the following highlighted:
1. Mo since it's in the OLD sheet but not the NEW sheet
2. The Delivery Date for Allan because it changed from the OLD to NEW sheet. (Which sheet should this be highlighted?)

 

[SkipVought]

Your RESULTS are confusing and meaningless! Is this REALLY what you want? You see that I'm not the only one who is confused!

what you still have not made clear is what fields define the key elements.

Is it ONLY Person, meaning that every other field can change, but if the same Person exists in OLD & NEW, then simply a change(s) will be noted?

I am going on that assumption and it is only an assumption!

 

[SkipVought]

Okay here's my solution, with the assumption that Person the the unique key. Since you did not adequately define the DIFF table, I did.

Here's my output, based on your table example data, plus I added a new person in the NEW table, ME. There are 3 statuses: added, removed and changed...
[pre]
DIFF:
Table Person Status Field Value
OLD Mo removed Person
NEW skip Added Person
NEW Joe changed System PC
NEW Allan changed Delivery 11/1/2015
[/pre]

Also made each of the tables a Structured table: tOLD, tNEW, tDIFF...

Sub CompareOLDvsNEW()
    Dim r As Range, c As Range, xl As Application, lRow As Long, lOROW As Long
    
    Set xl = Application
'Table   Person  Status  Field
    lRow = 2
'removed in NEW table
    With Sheets("DIFF")
        For Each r In [tOLD[Person]]
            If IsError(xl.Match(r.Value, [tNew[Person]], 0)) Then
                .Cells(lRow, [tDIFF[Table]].Column).Value = "OLD"
                .Cells(lRow, [tDIFF[Person]].Column).Value = r.Value
                .Cells(lRow, [tDIFF[Status]].Column).Value = "removed"
                .Cells(lRow, [tDIFF[Field]].Column).Value = "Person"
                lRow = lRow + 1
            End If
        Next
'added in new table
        For Each r In [tNew[Person]]
            If IsError(xl.Match(r.Value, [tOLD[Person]], 0)) Then
                .Cells(lRow, [tDIFF[Table]].Column).Value = "NEW"
                .Cells(lRow, [tDIFF[Person]].Column).Value = r.Value
                .Cells(lRow, [tDIFF[Status]].Column).Value = "Added"
                .Cells(lRow, [tDIFF[Field]].Column).Value = "Person"
                lRow = lRow + 1
            End If
        Next
'changes in new table
        For Each r In [tNew[Person]]
            If Not IsError(xl.Match(r.Value, [tOLD[Person]], 0)) Then
                lOROW = xl.Match(r.Value, [tOLD[Person]], 0)
                For Each c In Intersect(r.EntireRow, [tNEW[#DATA]])
                    If c.Value <> xl.Index([tOLD[#DATA]], lOROW, c.Column) Then
                        .Cells(lRow, [tDIFF[Table]].Column).Value = "NEW"
                        .Cells(lRow, [tDIFF[Person]].Column).Value = r.Value
                        .Cells(lRow, [tDIFF[Status]].Column).Value = "changed"
                        .Cells(lRow, [tDIFF[Field]].Column).Value = xl.Index([tNEW[#HEADERS]], 1, c.Column)
                        .Cells(lRow, [tDIFF[Value]].Column).Value = c.Value
                    End If
                Next
                lRow = lRow + 1
            End If
        Next
    End With
    Set xl = Nothing
End Sub

 

[NewUserMo]

Hi SkipV,

It works great. Thank you for your help.

 

[Andrzejek]

To show appreciation for help received, please use [blue]“Great Post? Star it”[/blue] link on the helpful post.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.