Need to compare two colums for matching... and can not understand Excel help

[Jackson]

I have two sets of phone numbers. The first set of phone numbers have names and addresses associated with them. The second set of phone numbers are "phone numbers only" and comes from that original set of phone numbers but is a smaller set. (Basically the numbers have been run through a "DO NOT CALL LIST"). The numbers I have in the second and smaller list are GOOD phone numbers and numbers I "CAN" call. I would like to compare the two phone lists and keep only the GOOD phone numbers along with their names and addresses.

 

[fred]

can you show us a sample? it will be easier to "vision" your datatable and provide a better answer. thx.

 

[Jackson]

Here is first set of info:


John Doe 123 Main St Denver CO 23498 (746)874-3675

Mary Franks 456 Center St Columbus GA 97584 (546)423-8765

Harry Smith 948 Walnut Ln Boise ID 75764 (234)903-2356

Greg Jones 303 Happy St Seattle WA 28376 (498)209-3877


In the second set of info, all I have are phone numbers:


(546)423-8765

(498)209-3877


They are from the first set of numbers. They are good phone numbers, numbers I CAN call. I would like to mark these numbers in the first set so I know that I can call them OR eliminate the other numbers and names. I guess if the other names and numbers can be eliminated, that would be less printing and paper saved. Thanks

 

[fred]

simplest way:


1. you will need two helper column with first set

2. first helper column: Right(1st set data column, 13)

3. second helper column: Match(2nd set data column, first helper column, 0)


anything you see 1 on #3 above is a good number that match 2nd data set.

 

[Luke M]

To expand on Fred's #3:

=IF(ISNA(MATCH(2nd set data columns, first helper column,0)),"Don't call","Can call")

 

[Jackson]

Still a little lost. Do the helper columns need to be right next to the first set of data? or phone numbers? Do the formulas need to be in every cell?

 

[Hui]

Jackson


If you have


John Doe 123 Main St Denver CO 23498 (746)874-3675

Mary Franks 456 Center St Columbus GA 97584 (546)423-8765

Harry Smith 948 Walnut Ln Boise ID 75764 (234)903-2356

Greg Jones 303 Happy St Seattle WA 28376 (498)209-3877


In Column A2:Ax


and

(546)423-8765

(498)209-3877


In Column B2:Bx


Then in C2 put =SUMPRODUCT(--(RIGHT($A$2:$A$5,LEN(B2))=B2))

Change the $A$5 to suit the end of your data

Copy down


Where it is a 1 in Column C there is a matching record

Where there is a 0 in Column C there is no matching record


Adjust formula ranges to suit where your data is