Matching and deleting data

  • Thread starter Thread starter Nick
  • Start date Start date
N

Nick

I have recently downloaded the "National-Do-Not-Call
List"(yes I employ telemarketers, sorry), and I need to
compare each phone number on that list with my own
private list. They are both in an Excel format. What I
would like to do is, replace every phone number on my
private list with a "Do Not Call", for every one that
matches the National List.

I tried using the formula, =IF(B1=A$1:A$63000,"Do not
call",B1), but this only compares cells that are right
next to each other, not each cell in one list to every
cell in the other list.

(If you're not on the National Do-Not-Call List and would
like to be added, you can go to: www.donotcall.gov )

Any help would be greatly appreciated.
Thanks
Nick
 
=if(isnumber(match(b1,a:a,0)),"do not call","Call at dinner!")
<vbg>
or
=if(isnumber(match(b1,a:a,0)),"do not call",B1)

might work.

But you'll have to make sure both columns are the same (either text or numbers).

And isn't that list in the millions by now. Sounds like the number of rows
inside excel (64k) may be limiting.
 
Hey Dave,

That's the whole point. This way they have plausible deniability. <bg> "I
compared the lists, but didn't see that name anywhere." To what extent do
the telemarketers have to go to verify lists?

tim
 
Use Vlookup.
If you create a vlookup in the cell next to your first
number.
1. using the lookup value = cell ref for first number
2. lookup array = range of do not use data
3. column no. = 1
4. false.

Then you can drag this down, to give you matches for all
your data.

From there it should be simple to sort and replace the
matches with DO NOT CALL.
 
I just wanted to say Thanks for the suggestions to
Anonymous, Dave, Tim, and Cecil. I will be trying these
out today and tomorrow. I will reply as soon as it is
figured out.

Thanks, I appreciate it, you guys are the greatest
Nick
 
I have no idea. But watch out if you get/use coupons for anything. Sometimes
there's a little "if you use this, you'll accept telemarketing calls from xxx"
in the fine print.
 
Back
Top