AnsweredAssumed Answered

Script to manage duplicates before uploading 1200 new contacts

Question asked by Cécile on May 8, 2017
Latest reply on May 11, 2017 by Cécile

Background

I "inherited" a  database of about 40k contacts (logged in inconstistently over 17y. by a succession of employees) where there might be already several duplicates. I haven't had time in the last 18 mo that I have started this position to actually figure out a process to clean up the old stuff but when I upload a new complaint, I normally check the DB to see if the person record already exists. Duplicates can be spotted by similar addresses, emails, etc. When we only have a name to go by, if the writing style is strikingly similar, we assume it is the same person otherwise we create a new record. If the name and email is the same but the address is different, we combine them, put in the file notes the old address and destroy the duplicate.

 

Relevant for scripting purposes

So I received a massive amount of complaints for a specific event which is impossible for me alone to log one by one so I have created a process to extract all the info from the emails. It is now in an Excel table which I prepped to upload the contacts and create the new complaints "en masse".

 

I want to find a way to:

 

  1. Compare the new contacts from the Excel list to those in the database
  2. Get the list of all the contacts for which there might already be a record(s) in the DB

 

Bonus points!

I don't think - but don't know - if this list of contacts for which there might already be a corresponding record in the DB will be big so I am planning to go through that list manually. However if the list was more than 30 contacts, it would be great to have the result showing like this:

 

Full Name
(xls)
Full Name
(FM)
First Name
(xls)
First Name
(FM)
Last Name
(xls)

Last Name

(FM)

Prov.
(xls)
Prov.
FM

Email

(xls)

Email

FM

Bill BrownWilliam BrownBillWilliamBrownBrownONONBWB@me.caBWB@me.ca
Bill BrownBill H. BrownBillBill H.BrownBrownONONbbrown@gmail.com
Bill BrownHenry Klout-BrownBillHenryBrownKlout-BrownONMBBWB@me.caBWB@me.ca
Claire KittClaire KittsClaireClaireKittKittsBCBCkittyc@live.com
Claire KittClara KClaireClaraKittKBCBC
Claire KittClaireKittkittyc@live.comkittyc@live.com
Claire KittMary C KittsClaireMary CKittKitts

Outcomes