Macro for comparing cells on 2 sheets in Excel

[jasonr704]

I need some help writing a macro that basically compares 2 data reports.
-Each week I get an updated report, so the columns are all formatted identically.
-I just need to determine which sku's are new over the previous week.
-The column that I'd like to sort on is SKU. Id like to have a 3rd sheet created that only shows the new entries.

I'm sure this is a pretty easy one, but I'm a newbie when it comes to writing macros.

Greatly appreciate the help!

[mdmackillop]

Can you post a sample file showing your data layout?

[jasonr704]

here's a sample worksheet.
-need to compare the column "SKU"
-worksheet "Unique" will show the uniques

[mdmackillop]

Nothing attached.
Use Manage Attachments in the Go Advanced reply section

[zhoyojsnt]

i also need this macro

[Bob Phillips]

[vba]

Sub IDENTIFY_duplicates()
Dim i As Long
Dim LastRow As Long
Dim NextRow As Long

With Worksheets("week2")

LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
NextRow = 1
For i = 2 To LastRow

If IsError(Application.Match(.Cells(i, "B").Value2, Worksheets("week1").Columns("B"), 0)) Then

NextRow = NextRow + 1
.Rows(i).Copy Worksheets("uniques").Cells(NextRow, "A")
End If
Next i
End With
End Sub
[/vba]

[jasonr704]

Thanks for the code. Seems to work, but it's taking a long long time to complete. I have fewer than 100,000 rows of data with less than 12 columns of data. is there a way to speed this macro up any?

[Bob Phillips]

This might be somewhat quicker

[vba]

Sub IDENTIFY_duplicates()
Dim i As Long
Dim LastRow As Long
Dim NextRow As Long

With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

With Worksheets("week2")

LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
NextRow = 1
For i = 2 To LastRow

If IsError(Application.Match(.Cells(i, "B").Value2, Worksheets("week1").Columns("B"), 0)) Then

NextRow = NextRow + 1
.Rows(i).Copy Worksheets("uniques").Cells(NextRow, "A")
End If
Next i
End With

With Application

.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End Sub
[/vba]

[Bob Phillips]

This should be even quicker

[vba]

Sub IDENTIFY_duplicates()
Dim i As Long
Dim LastRow As Long
Dim rng As Range

With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

With Worksheets("week2")

LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
.Columns("C").Insert
.Range("C1") = "temp"
.Range("C2").Resize(LastRow - 1).Formula = "=ISNUMBER(MATCH(B2,week1!B:B,0))"
Set rng = .Range("C1").Resize(LastRow)
rng.AutoFilter field:=1, Criteria1:="FALSE"
On Error Resume Next
Set rng = rng.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then

rng.EntireRow.Copy Worksheets("uniques").Range("A1")
End If
.Columns("C").Delete
End With

With Application

.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
End Sub
[/vba]