Removing Duplicate Record From another Sheet

[pantsmonkey]

Hay Guys,

Ive now gotten to the stage where I have 2 worksheets coloumn a) in each sheet has a site address that can contain spaces.

I would like a way for to check for exact copies of the contents of cell a) in sheet 1 and sheet 2, If the there is an exact match in both sheets I would like that row to be deleted from sheet 1.

Is this possible ?

Sorry to be a pain someone just dumped this on me at lunchtime (not my job and ive got a out of state flight booked for 2 hours from now)

[RichardS]

Modify that countif formula in Sheet 1

=COUNTIF(Sheet2!$A:$A,Sheet1!A1)

Paste the results as values. Any with a value of 1 or above has an extact match in sheet 2. Sort by the formula column, then delete all those rows.

Or are you only looking for matches in the same row? A1 in Sheet 1 has an exact macth in A1 in Sheet 2?

=MATCH(A1,Sheet2!A1:A1,0)

Returns #N/A if no match, so keep those records.

Richard

[tactps]

With little time, here is the quick and nasty way:

In sheet 1, use lookup formulas like this:

=vlookup(A1,Sheet2!a:a,1,false)

Filter sheet1 on everything except "#N/A" and then delete each visible line.

[Yogi Anand]

Hi pantsmonkey:

And here is a macro based approach ...

For i = Sheets("Sheet1").Range("A65536").End(xlUp).Row To 1 Step -1
        yRow = 0
        On Error Resume Next
        yRow = WorksheetFunction.Match(Sheets("Sheet1").Cells(i, 1).Value, Sheets("Sheet2").Range("A:A"), 0)
        If yRow > 0 Then Sheets("sheet1").Cells(i, 1).EntireRow.Delete
    Next