Find and Replace Help

  • Thread starter Thread starter Allanda
  • Start date Start date
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
 
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
 
Back
Top