PDA

View Full Version : Macro for comparing cells on 2 sheets in Excel



jasonr704
05-07-2010, 10:45 AM
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
05-07-2010, 10:47 AM
Can you post a sample file showing your data layout?

jasonr704
05-07-2010, 01:54 PM
here's a sample worksheet.
-need to compare the column "SKU"
-worksheet "Unique" will show the uniques

mdmackillop
05-07-2010, 03:18 PM
Nothing attached.
Use Manage Attachments in the Go Advanced reply section

zhoyojsnt
05-08-2010, 11:40 PM
i also need this macro

Bob Phillips
05-09-2010, 01:29 AM
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

jasonr704
05-10-2010, 12:26 PM
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
05-10-2010, 02:04 PM
This might be somewhat quicker



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

Bob Phillips
05-10-2010, 02:14 PM
This should be even quicker



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