Comparing two sheets to highlight duplicate records

lmoore

New Member
Joined
May 10, 2006
Messages
25
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.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
Will the data be in one column or is it anywhere on the sheet and in no-pattern?
 

lmoore

New Member
Joined
May 10, 2006
Messages
25
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

Board Regular
Joined
Jul 21, 2006
Messages
167
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

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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
 

Forum statistics

Threads
1,137,060
Messages
5,679,385
Members
419,824
Latest member
Mercy kiara

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top