Find and Replace Help

A

Allanda

Hi:

I have a list of phone numbers in one column in a worksheet, and many
phone numbers in many columns in another worksheet. I would like find
any instances of the first set of numbers in the second worksheet, and
either delete them, or replace them with zero, or highlight them; the
end result being that I would like to be able to give my staff the
second worksheet of phone numbers without including the phone numbers
from the first worksheet.

Any help would be greatly appreciated!

Thanks!

Allanda
 
F

Frank Kabel

Hi
have a look at
http://www.cpearson.com/excel/duplicat.htm#ExtractingCommon
Adapting this enter the following in your second sheet (assumption:
numbers are stored in column A):
=IF(COUNTIF($A$1:$A$10,B1)>0,B1,"")
=IF(COUNTIF('Sheet1'!$A$1:$A$999,A1)>0,"exists in sheet 1","")
enter this as array formula (CTRL+SHIFT+ENTER) and copy down.
Afterwards you can sort by this column (or apply an autofilter) and
delete all common entries

HTH
Frank
 
Top