Find Duplicates Across Two Sheets, Delete Everything else.
 

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

Find Duplicates Across Two Sheets, Delete Everything else.
 

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
---------------
Jacob Skaria


"waggett" wrote:

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

Find Duplicates Across Two Sheets, Delete Everything else.
 

Worked perfectly, thanks again.

"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"waggett" wrote:

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