Find Duplicates Across Two Sheets, Delete Everything else.

[waggett]

I've tried and failed at this, any help would be appreciated...

I have a workbook with two sheets. Sheet1 is a list of equipment specific to
my department, it has 176 rows and 11 columns. Sheet 2 is a maintenance
record for all equipment within the company, it has 2406 rows and 8 columns.
Column D of both sheets contains the piece of equipments unique ID number.

What I would like to do is compare column D of sheet2 with column D of
sheet1 and delete everything on sheet2 which does not have a match on sheet1,
leaving me with only the maintenance records for the items in my department.
Can this be done?

Sincere thanks in advance for any assistance.

Tagg

 

[Jacob Skaria]

Apply the below formula in Sheet2 last unused column. I assume data start
from Row2. So if the last unused column is J put this formula in J2. which
will check Sheet1 records to find a match for the unique number and return
either a blank or "Delete". Once done sort this column and delete the records
with 'Delete'

=IF(COUNTIF(sHEET1!D,D2),"","Delete")

If this post helps click Yes

 

[waggett]

Worked perfectly, thanks again.

Apply the below formula in Sheet2 last unused column. I assume data start
from Row2. So if the last unused column is J put this formula in J2. which
will check Sheet1 records to find a match for the unique number and return
either a blank or "Delete". Once done sort this column and delete the records
with 'Delete'

=IF(COUNTIF(sHEET1!D,D2),"","Delete")

If this post helps click Yes