Excel ? Do Not Call Table

[Vindictiiv]

I was wondering if anyone knew how to create a simple formula to show matches
in phone numbers for a do not call type list. I have column A (my database of
numbers), and I want to compare it to column B (the do not call list) and
report any matches in column C.

[Biff]

Hi!

Here's one option:

Use conditional formatting to highlight the numbers in column A that match
the DNC numbers in column B.

Assume the numbers in column A are in the range A1:A100. The numbers in
column B are in the range B1:B10.

Select the range A1:A100
Goto Format>Conditional Formatting
Formula is: =ISNUMBER(MATCH(A1,B$1:B$10,0))
Click the Format button
Select the style(s) desired (maybe a different background color)
OK out

Biff

"Vindictiiv" <[email protected]> wrote in message
news:[email protected]...
>I was wondering if anyone knew how to create a simple formula to show
>matches
> in phone numbers for a do not call type list. I have column A (my database
> of
> numbers), and I want to compare it to column B (the do not call list) and
> report any matches in column C.

[Vindictiiv]

I tried using a formula similar to this previously without any luck, I'm not
sure but I believe the formula is having issues distinguishing between cells
and the column length. The database in column a consists of 55k phone numbers
while the do not call list in column b is only 1-2k. Even then with
auto-formatting id have to manually go thru and pick out the numbers I didn't
want to call. Thats why I'm really just looking for a formula to report
matching numbers into a third column (column c)

But thank you for the advice anyway.

"Biff" wrote:

> Hi!
>
> Here's one option:
>
> Use conditional formatting to highlight the numbers in column A that match
> the DNC numbers in column B.
>
> Assume the numbers in column A are in the range A1:A100. The numbers in
> column B are in the range B1:B10.
>
> Select the range A1:A100
> Goto Format>Conditional Formatting
> Formula is: =ISNUMBER(MATCH(A1,B$1:B$10,0))
> Click the Format button
> Select the style(s) desired (maybe a different background color)
> OK out
>
> Biff
>
> "Vindictiiv" <[email protected]> wrote in message
> news:[email protected]...
> >I was wondering if anyone knew how to create a simple formula to show
> >matches
> > in phone numbers for a do not call type list. I have column A (my database
> > of
> > numbers), and I want to compare it to column B (the do not call list) and
> > report any matches in column C.
>
>
>

[Biff]

> just looking for a formula to report
> matching numbers into a third column (column c)

All that'll do is give you a duplicate of column B, wouldn't it?

There's a formula that will do that but I don't think it could handle the
range size.

Here's another option:

Insert a new column B. The DNC numbers are now in column C.

Enter this formula in B1:

=COUNTIF(C$1:C$1000,A1)

If a number IS NOT on the DNC list the formula will return 0.

Double click the fill handle to copy the formula down to B55000. May take a
few seconds to complete!

Now, select both columns A and B.

Do a sort on column B ascending.

Now, all the "good" numbers will be at the top of the list. All the DNC
numbers will be at the bottom.

Biff

"Vindictiiv" <[email protected]> wrote in message
news:[email protected]...
>I tried using a formula similar to this previously without any luck, I'm
>not
> sure but I believe the formula is having issues distinguishing between
> cells
> and the column length. The database in column a consists of 55k phone
> numbers
> while the do not call list in column b is only 1-2k. Even then with
> auto-formatting id have to manually go thru and pick out the numbers I
> didn't
> want to call. Thats why I'm really just looking for a formula to report
> matching numbers into a third column (column c)
>
> But thank you for the advice anyway.
>
> "Biff" wrote:
>
>> Hi!
>>
>> Here's one option:
>>
>> Use conditional formatting to highlight the numbers in column A that
>> match
>> the DNC numbers in column B.
>>
>> Assume the numbers in column A are in the range A1:A100. The numbers in
>> column B are in the range B1:B10.
>>
>> Select the range A1:A100
>> Goto Format>Conditional Formatting
>> Formula is: =ISNUMBER(MATCH(A1,B$1:B$10,0))
>> Click the Format button
>> Select the style(s) desired (maybe a different background color)
>> OK out
>>
>> Biff
>>
>> "Vindictiiv" <[email protected]> wrote in message
>> news:[email protected]...
>> >I was wondering if anyone knew how to create a simple formula to show
>> >matches
>> > in phone numbers for a do not call type list. I have column A (my
>> > database
>> > of
>> > numbers), and I want to compare it to column B (the do not call list)
>> > and
>> > report any matches in column C.
>>
>>
>>