Compare Sheets with diff number of rows 1

[OMG_VBA_IS_GREAT]

Hi All, I've been using the below code (took from this great website) and it works great when I have the same number of rows in sheet 1 and sheet 2. The problem I am having and I am struggling to resolve if one of the two sheets has different rows the macro displays diffs because the number of row are off. Not sure how to fix. Any help would be greatly appreciated.

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
Dim MaxRow1 As Long
Dim MaxCol1 As Integer
Set refSht = Sheets("Sheet1")
Set compSht = Sheets("Sheet2")
Application.ScreenUpdating = False

Sheets("Diff").Select
Columns("A:L").Select
Selection.Delete shift:=xlToLeft
Range("A1").Select

'Call CreateCommonHeaders
'Call Sort_Column_Headers_AtoZ
'Call Sort_Unique_ID 'Need to write code to sort Unique ID for each tab

lRow = refSht.UsedRange.Rows.Count
cols = refSht.UsedRange.Columns.Count
incr = 2
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("Diff")
.Cells(1, 1).Value = "Header Col Diff"
.Cells(1, 2).Value = "Diff Cell Location"
.Cells(1, 3).Value = "Umy Data"
.Cells(1, 4).Value = " MyData"
'.Cells(incr, 1).Value = "R" & x + 1 & "C" & i
.Cells(incr, 1).Value = refSht.Range(Cells(i).Address(RowAbsolute:=False, ColumnAbsolute:=False)).Value
.Cells(incr, 2).Value = Cells(x + 1, i).Address(RowAbsolute:=False, ColumnAbsolute:=False)
.Cells(incr, 3).Value = refArr(x, 1)
.Cells(incr, 4).Value = CompArr(x, 1)
incr = incr + 1
End With
Else
End If
Next

Next i

Sheets("Diff").Select

With Sheets("Diff").UsedRange '
LastRow1 = .Rows.Count
LastCol1 = .Columns.Count
End With

MaxRow1 = LastRow1
MaxCol1 = LastCol1

Application.StatusBar = "Formatting the report..."
With Range(Cells(1, 1), Cells(MaxRow1, MaxCol1))
.Interior.ColorIndex = 19
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlHairline
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlHairline
End With
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlHairline
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlHairline
End With
On Error Resume Next
With .Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlHairline
End With
With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlHairline
End With

With Range(Cells(1, 1), Cells(1, MaxCol1)).Select
With Selection
.Interior.ColorIndex = 15
End With
End With

Columns("C").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

Range("A1").Select
End With

Columns("A:L").AutoFit

Application.StatusBar = False
Application.ScreenUpdating = True

MsgBox ("Recon Complete!!")

End Sub

 

[SkipVought]

Hi,

Welcome to Tek-Tips.

Without having to wade through your code to try to ascertain what you are trying to accomplish, please explain in plain words what you are attempting to do. Maybe a small representative example of what’s on each sheet may be helpful.

Please be more detailed and specific rather than “I’m comparing sheets.” That could be done in any number of specific ways.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[OMG_VBA_IS_GREAT]

Hi Skip,
Thanks for the welcome. Sorry for not being clear. Sheet 1 and Sheet 2 share unique ids. The macro above compares rows and columns in each sheet and prints out differences in Diff sheet. I sort each sheet by unique id in (column 1) and the macro compares data in rows and columns in sheet 1 and sheet 2. The issue i am having is if one of the sheets does not have the same number of rows i.e. missing a unique id. My diff sheet will shows false breaks because the rows differ.
See attached file. Many thanks for your help.

 

[SkipVought]

What does it matter what row a particular ID is on?

Aren’t you simply interested that you have the same TransactionID in both tables and that the dates for any ID are identical in both tables?

Or am I missing something?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[OMG_VBA_IS_GREAT]

In the code I provided the macro assumes a one for one relationship for tansaction IDs. If a transaction ID is missing in one of the sheets it throws of the comparison giving false breaks in diff sheet. Note the other columns will not always have the same dates and column numbers might expand or contract. My goal Is to showissing transaction ID and mismatched cells for transaction IDs that are the same between the two sheets.

 

[SkipVought]

My goal Is to show missing transaction ID and mismatched cells for transaction IDs that are the same between the two sheets.

See uploaded workbook.

I show a spreadsheet method, using formulas and Conditional Formatting.

I also wrote a procedure in sheet DIFF2, where I show the results

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[OMG_VBA_IS_GREAT]

Thanks as most go rt the file and solution. I appreciate your time and effort. My only concern , Sorry for not mentioning it before, the sheet will have 20,000 rows and 55 to 60 columns will the spreadsheet method work? Is possible to do it all in VBA using arrays? Unfortunately my VBA skill set is limited in working with arrays. Thanks again for your help.

 

[SkipVought]

1) Why is the row missmatch at all relevant?

2) If you have a database or system from which these transactions result, then why would any of the data in a TransactionID row be any different for the same ID?

If there is some kind of system deficiency, then every single data element associated with any TransactionID must be compared to the corresponding data element in a matching ID: in your example, not only the dates but also Version, Action and Status.

Need the answer to these two questions.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[OMG_VBA_IS_GREAT]

Hi Skip,

1) Comparing two data sets. Sheet 1 is an extract from an internal system/ data set and sheet 2 is from a third party system. I am performing a reconcilation between the two to ensure the third party system has a record of all internal transaction IDs. There are mismatches caused by timing or processing errors.

2) data sets are coming from two different systems. I am pulling data in Excel to compare and identify diffs. Unfortunately the data is not the same and that is the reason we run a reconcilation. If we have the same number of records then the code I provided works great processing 20k rows and 57 coulumns in a matter of seconds. If the number records are Not the same the macro produces false positives.

Yes I want to match each data set for each transaction IDs As mentioned the code provided works if both sheets have the same transaction IDs. If one or more transaction IDs are missing in sheet 1 or sheet 2 then the comparison results will be off.
Hope that answers your questions. Thank you again for your help.

 

[SkipVought]

So Sheet1 is the standard.

I’ll modify my code to read as many columns as exist on Sheet1 and assuming that the column headings are identical and in the same order in both sheets.

I assume that the order of the data is irrelevant and that the key factor is a matching TransacionID and matching data in each corrrsponding column for each ID.

If TIDs mismatch, there is no need to look at column data: the missmatch result will either indicate the TID, Sheet1 Name or Sheet2 Name.

If TIDs match, then I’ll loop through the data in the row looking for missmatch data. If a missmatch then list the TID, FieldName, the Sheet1 Value, the Sheet2 value for each data mismatch.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[OMG_VBA_IS_GREAT]

That would be awesome Skip. Thank you again for all your help.

 

[SkipVought]

Check it out.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[OMG_VBA_IS_GREAT]

Thank you so much Skip. It looks great. I will test it on my complete data set Monday morning and will let you know how it goes.

 

[OMG_VBA_IS_GREAT]

Hi Skip,

I ran the macro you provided this morning using my standard data set 20,000 rows and 57 columns. Unfortunately, the macro crashes excel with Not Responding and I have to restart my excel. Not sure what to do?

 

[Andrzejek]

I am sure Skip's sample will work for you sooner or later (it always does), but is this the right tool for this job? If you have your own internal data and you get en external data (in some kind of data base, I assume) - why not use this DB to do your comparison?
If you dump your external data into a table that mirrors your internal structure, then you need a few simple SQL's to compare the two, like:
[tt]
Select transaction_ID from InternalData
Where transaction_ID NOT IN
(Select transaction_ID From ExternalData)[/tt]

And if you want to check it backwards, do the opposite SQL check.

If you want to check all data in rows that match transaction ID, you can do that, too.

Why import all of it to Excel?


---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

OMG, can you upload your workbook? Pare it down or launder if you have sensative data.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[OMG_VBA_IS_GREAT]

Hi Skip - see attached file. Also note that the file is producing breaks that are not breaks (reduced the number of rows). I've attached real number of rows/columns. Thanks in advance for your help.

 

[SkipVought]

I ran the code in your workbook several times. I added some mismatches in the data as well, in order to get DIFF2 results.

The code ran about 3 minutes on my laptop.

I added two statements, one near the beginning and one near the end:

[b]Application.ScreenUpdating = False[/b]
'...
[b]Application.ScreenUpdating = True[/b]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

If you have excel 2016 or excel 2013+PoverQuery free add-in from microsoft, you can compare two sheets in seconds.
I assumed that:
- Transaction ID is unique,
- headers names, if to compare, are the same.
I enclose the workbook with queries. They:
- unpivot tables,
- link by ID and header,
- detect different entries.
and:
- pick ID from both tables,
- detect non-existing IDs in linled table (both directions).
Just refresh data from queries in Diff2 sheet.

combo

 

[OMG_VBA_IS_GREAT]

Hi Skip - it worked. Thank you for your help!