×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Compare Sheets with diff number of rows

Compare Sheets with diff number of rows

Compare Sheets with diff number of rows

(OP)
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:D").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

RE: Compare Sheets with diff number of rows

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Compare Sheets with diff number of rows

(OP)
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.

RE: Compare Sheets with diff number of rows

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Compare Sheets with diff number of rows

(OP)
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.

RE: Compare Sheets with diff number of rows

Quote:


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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Compare Sheets with diff number of rows

(OP)
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.

RE: Compare Sheets with diff number of rows

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Compare Sheets with diff number of rows

(OP)
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.

RE: Compare Sheets with diff number of rows

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Compare Sheets with diff number of rows

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

RE: Compare Sheets with diff number of rows

(OP)
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.

RE: Compare Sheets with diff number of rows

(OP)
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?

RE: Compare Sheets with diff number of rows

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:

Select transaction_ID from InternalData
Where transaction_ID NOT IN
(Select transaction_ID From ExternalData)


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.

RE: Compare Sheets with diff number of rows

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Compare Sheets with diff number of rows

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:

CODE

Application.ScreenUpdating = False
'...
Application.ScreenUpdating = True 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Compare Sheets with diff number of rows

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

RE: Compare Sheets with diff number of rows

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

RE: Compare Sheets with diff number of rows

combo,
I guess your and my suggestions were ignored.

Oh, well.... sad


---- Andy

There is a great need for a sarcasm font.

RE: Compare Sheets with diff number of rows

Andy,
yes, good old VBA (anyway, it's VBA forum). My suggestion requires BI implemented in excel. Intermediate tables after unpivoting contain 3 columns and around 750k of rows each, they are linked with two fields and the third is compared. Power query (or get & transform in 2016) it's incredibly fast, tables are compared within 10 seconds (three output queries).

combo

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close