One of our tele-fundraising vendors returned a data file of phone numbers of donors who want to opt out of future phone calls.  The file contains no ID's and no phone formatting.  I need to be able to match the phone numbers up to constituent ID's in order to import a new solicit code.  (Pressuring the vendor to return a data file including ID's has already been tried).

 
How would you match the phone numbers up with ID's?

Here are the approaches I've taken: Turn off all phone formatting in RE and export a file containing ID's and phone numbers.  What I found is that many phone numbers had been manually formatted, so many phone numbers exported with (xxx)xxx-xxxx. 

The next thought I had was to write a formula in excel to extract numbers only.  However, some phone numbers are incomplete, etc.  But perhaps this is the best I can do.  Do you have recommendations for a formula?  Most phone numbers are formatted either (xxx)xxx-xxxx or xxx-xxxx.

Any other suggestions? 

thanks so much!

Faith

 

Faith,

This is not something that is going to be easy at all.  I would use Access to check for duplicates (matches) on phone number.  If your data is flawed (incomplete) they still should match to your data unless they have done a cleanup or phone lookup service to your original data (in which case you should get the updates they made first).  Any flaws will compromise your matching ability.

I know you said you tried to get the vendor to give you the information in the correct format and failed but I can not believe that your vendor is so inflexible.  I would not pay them for the service they provided unless you get what you want - you are the customer.  If they are truly a vendor who knows anything about what they are doing - they have to know that you use a database and you need an ID number to import the data they gave you.  If the do not understand this - tell us all their name so we all know never to use them in the future.

Melissa

One more question - did the vendor say that that person should be put on a do not call list or that number.  If they called a person's business do you want to only mark that business phone as DNC rather than coding the whole record as No Calls.  If so then you need to export the Cons ID, Address Imoport ID and Phone Import ID to get a file that you can import the DNC marker on.

 Thanks Melissa.  I am probably going to try one more round of discussions with the vendor.  After that, I will share their name with everyone if I'm not successful.  ;)

And yes, of course we did data clean up and phone appends, so even at that level I'm uncertain of my ability to make matches.  But I was hoping I could match, oh, say, 5,000 of the 8,700 and deal with the rest manually.  But I think I'm back to pressuring the vendor.  It's crazy to get a file back without ID's.

Thanks again.
 

ABSOULTELY.  8,700 is definitely a number you will want to import and import accurately.  I do not do anything manual for anything over 200.