Delete values in sheet 2 that arre found in sheet 1

[np]

Is there an easy way to delete values in sheet 2 that match the value from
sheet 1. I have approximately 10000 rows in sheet 1 and some identical rows
on sheet 2 that need to be eliminated? I not sure if this has to be done
pragmatically or if there is some feature in excel 2007 that can handle this
type of request.

thanks
Nick

 

[JLatham]

If it is the values in a single column on each sheet that make up the
duplication, it's fairly easy. Lets say you want to compare the contents of
column A on each sheet to find duplicates on Sheet2 (that are also on
Sheet1). Your data starts on row 2 of both sheets, and on Sheet1 you have
the 10000 rows to examine (A2:A10001).

In an available column on row 2 on Sheet2 put a formula like this:
=IF(COUNTIF(Sheet1!A$2:A$10001,A2)>0,1,0)
fill the formula down for the entire length of entries on Sheet2 you want to
examine. It will put a 1 on each row that has a value in A that matches a
value on Sheet1. You can then filter the list to show only entries with 1 in
that column and delete them.