Comparing and removing data in different workbooks

[Guest]

I have a master workbook with a list of companies and fax numbers. In another
workbook I have a list of companies with failed fax numbers. How can I
compare the two workbooks and remove the failed fax numbers from the master
list?

 

[Bernie Deitrick]

In another column, use the formula

=ISERROR(MATCH(cell with fax number, List of failed fax numbers, False))

then filter or sort the list for FALSE values returned by those formulas, and delete those values.

HTH,
Bernie
MS Excel MVP

 

[Guest]

Something has occurred to me, the list of bad fax numbers is formatted as
such "12225551212". The master list of fax numbers is formatted as such
"2225551212". I can't figure out how to add the one in front of the fax
number so I can match the columns in the two workbooks/worksheets. I am sure
it is something simple, but I am an Excel newbie and am just missing it.

 

[Bernie Deitrick]

This will remove a leading 1:
=ISERROR(MATCH(MID(cell with fax number,2), List of failed fax numbers, False))

And this will add a leading 1:
=ISERROR(MATCH("1" & cell with fax number, List of failed fax numbers, False))


HTH,
Bernie
MS Excel MVP