How to create a Macro that looks for matching rows of data on 2 different worksheets

I am new to programming and not sure how to write a Macro, but could use it for work. I have 2 worksheets with several hundred rows of data. I want to write a program that can check to see if both worksheets contain the same data and if possible, have anything that is different appear in a seperate tab. This would avoid me searching through thousands of lines one by one. Is this feasable? Thank you for your help.

ok. wow! I'll try it now. What about those columns I deleted. If the original data has more columns then the sample, will it make a difference since I just want to specifically compare those 3 criteria I gave you?

Hi Arlette,

The number of columns will be static at 37 for each data tab. They come from 2 sources thus the need to compare them.

1)Last column will be AK
2)The corresponding columns for M, R & V are O, T, & AD in my original.

Also, how can I tell if the items that are created are rows missing from Data 1, or Data 2? Is there a way to distinguish?

Thank You !!!

Hi Arlette,

Thank you so much!! But I'm having a problem still. I pasted the code, then created a filename "datacompare" and saved it. When I go into the sheet with my 2 tabs of data, I run the macro but it doesnt move and doesnt give an error message either. Any ideas?

Option Explicit
Dim lrow As Long
Dim tlrow As Long
Dim lcol As Long
Dim i As Long

Sub compare_rows()

Worksheets.Add(After:=Worksheets(2)).Name = "Temp"

lrow = Worksheets(1).Range("A" & Rows.Count).End(xlUp).Row
Worksheets(1).Rows("1:" & lrow).Copy Worksheets("Temp").Range("A" & Rows.Count).End(xlUp)

With Worksheets("Temp")
.Columns("A:A").Insert shift:=xlToRight
.Range("A1").Value = "Sheet"
.Range("A1").Font.Bold = True
.Range("A2:A" & lrow).Value = "1"
End With

lrow = Worksheets(2).Range("A" & Rows.Count).End(xlUp).Row
Worksheets(2).Range("A2:AK" & lrow).Copy Worksheets("Temp").Range("B" & Rows.Count).End(xlUp).Offset(1, 0)

With Worksheets("Temp")
tlrow = .Range("A" & Rows.Count).End(xlUp).Row
lrow = .Range("B" & Rows.Count).End(xlUp).Row
.Range("A" & tlrow + 1 & ":A" & lrow).Value = "2"
End With

Cells.EntireColumn.AutoFit

lrow = Worksheets("Temp").Range("A" & Rows.Count).End(xlUp).Row

ActiveWorkbook.Worksheets("Temp").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Temp").Sort.SortFields.Add Key:=Range("O:O"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Temp").Sort.SortFields.Add Key:=Range("T:T"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Temp").Sort.SortFields.Add Key:=Range("AD:AD"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Temp").Sort
.SetRange Range("A:AK")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

With Worksheets("Temp")
For i = 2 To lrow
If .Range("A" & i).Value = "1" And .Range("A" & i + 1).Value = "2" Then
If .Range("O" & i).Value = .Range("O" & i + 1).Value Then
If .Range("T" & i).Value = .Range("T" & i + 1).Value Then
If .Range("AD" & i).Value = .Range("AD" & i + 1).Value Then
.Rows(i & ":" & i + 1).Delete
lrow = lrow - 2
i = i - 2
End If
End If
End If
End If
Next i
End With

End Sub

Sub datacompare()

End Sub

Ok sorry, didnt know about the code tags.

Great! I got it working. Thanks so much! I have one more question for you too :-) I want to compare another set of data that has the same criteria as above, Columns (O,T,AD), but this new data is formatted differently and only has 20 columns in total. The corresponding columns are H, G, E respectively. The First column is A and the last column is N. Could you possibly create a new file with this adjustment? I thank you kindly for your time.

What do you mean by after inserting the column for sheet? I'm confused lol The criteria that is in O, T, & AD comes up in H, G, & E in the new format data. So I need to compare these columns with each other.

Hi Arlette,

I apologize but am a bit confused as to how to save this as a new macro to run. Please forgive my novice experience. How do I get a new blank sheet in the VBA screen? Thanks

Hi Arlette, Sorry for late reply. I pasted the alternate code below the original code as mentioned, however, the macro does not run properly. It gives me both sets of data on the temp tab. I'm sure I'm not pasting it or running it properly. Any ideas? Maybe can you create a new file name for the alternate code and I can save it as a new macro? I apologies for taking your time like this.

Best
Thomas

Hi Arlette, I'm trying to run the code from step #14 but get an error message of can not execute in break mode, and the line "Sub compare_rows()" is highlighted. What am I doing wrong? Thank you so much.