Comparing two sheets to highlight duplicate records

[lmoore]

I see there a lot of posts about duplicate entries but i cant seem to find one that suits what I want to do .
Basically I just want to compare one sheet with another and then highlight the duplicate entries I then want to copy the non duplicates to another sheet.

 

[Joe Was]

Will the data be in one column or is it anywhere on the sheet and in no-pattern?

 

[lmoore]

the data will run accross 6 columns A>F and would ahve about 50 entires but as this is part of a daily report the number of rows can vary

 

[big_mac]

formula answer
If A1 is the cell on both sheets that has the unique information (aka the KEY) then go to an empty column in row 1 and type this:
=if(iserror(match($a1,sheet1!A:A,0)),"",1)
This will place a 1 in the column of the row that doesn't match the sheet1 information.
You can filter by the 1 with autofilter or conditional format to highlight.
I use this almost daily, hope it helps.

 

[Joe Was]

Sub myDupData()
'Standard module code, like: Mouule1!
Set myRng = Sheets("Sheet2").Range("A1:F55")

For Each myCell In myRng
If myCell = "" Then GoTo myNext

'Copy rows from Sheet2 that have cells that are different from the same cells on Sheet1, to Sheet3!
If myCell.Value <> Worksheets("Sheet1").Range(myCell.Address).Value Then
Set myBottom = Worksheets("Sheet3").Range("A65536").End(xlUp).Offset(1, 0)
myCell.EntireRow.Copy Destination:=myBottom
Else

'Highlight each cell on Sheet2 that match that exact cell on Sheet1.
myCell.Interior.ColorIndex = 36
End If

myNext:
Next myCell
End Sub

 

[lmoore]

Yes i got that to work perfect thanks a million