Find Duplicates Across Two Sheets, Delete Everything else.

  • Thread starter Thread starter waggett
  • Start date Start date
W

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
 
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:D,D2),"","Delete")

If this post helps click Yes
 
Worked perfectly, thanks again.

Jacob Skaria said:
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:D,D2),"","Delete")

If this post helps click Yes
Click to expand...
 
Back
Top