How do i delete duplicate records?

• Any email address that differs in one letter (typo) will not be captured, or written differently (
  john.smith@example.com/john_smith@example.com)
• Any duplicate records that have no email addresses will not be captured.

• Adjust the following settings
  • Show: All Leads

  • Date Field: Create Date

  • Range: All Time

• Add Filters:
  • Filter 1: State equals ______ (e.g. State equals NJ)- Use any State, will complete one by one.

  • Filter 2: Converted equals False
    • This must be set because leads reports capture records that are really contacts, but
      used to be leads and have since been converted into contacts. This setting makes the report only capture leads that are still leads. If this is not done your final product will show many duplicates when in fact there is only a contact and no longer a lead.
• Recommended Tabs
  • Company/Account; Lead Owner; Lead ID; First Name; Last Name; Email (this is a must)

  • I used Lead ID so I can later identify which record is a lead and which record is a contact later in the process when all leads and contacts are merged together into an excel file.

• Save the report, and then run it. Once you run the report you can click “Export Details”
  • Leave all the settings the same and click “Export” and an excel window will pop up. Click “Open”
    • Once opened, save the file. Make sure when you save it to change the “Save as type” from “Web Page” to an excel file and save it in your documents.

    • Recommended Tabs:

    • Save the report, and then run it. Once you run the report you can click “Export Details”

Accounts/Contacts Report

• Make sure to make a “Contacts & Accounts/Customers” report

• Adjust the following settings
  • Show: All accounts/contacts

  • Date Field: Create Date

  • Range: All Time

• Add Filter:
  • State Equals ____ (e.g. State equals NJ)

• Recommended Tabs:
  • Account/Customer Name; Contact Owner; Mailing Street; First Name; Last Name; Email

  • Report must have same amount of tabs as the leads report. I used “Mailing Street” in place of “Lead ID”. The excel file will then in the “Leads ID” column have numbers for leads, and an address for contacts. I did this only to differentiate between a lead or contact record in excel.

• Save the report, and then run it. Once you run the report you can click “Export Details”
  • Leave all the settings the same and click “Export”, again click open when the window prompts you to.

  • You do not need to save this file. Once the report opens click a cell, then hit Ctrl +A, then “copy” the information.

  • Go back to the lead report excel file and “paste” the info from the contacts excel file directly below the last lead record. Delete the row which was the heading from the contact excel file (Accounts; Contact Owner…etc.)

  • You may edit the excel file to include lines by: clicking one cell, hitting Ctrl +A, Format, Cells, Borders tab, pick a color, and the presets “outside” and “inside”.

  • Save the file which now includes the records of
    all leads and contacts from whichever state you chose.

Identifying duplicates from the excel file.

• • Open a new excel file that is blank

  • Go back to the excel file with the records and right click the column letter of the column that includes the email addresses. This will highlight the entire column with all the email addresses in it. “Copy” the whole column and paste it in the new (blank) excel file. Once the email addresses are copied into that excel file save it. You can now close the original excel file with
    all the records in it and leave open the file that only has the email addresses.
  • Go back to the file which includes
    only email addresses. Select the whole column of email address and click the “sort ascending” button on the toolbar up top. The button will have an “A” on top of a “Z”. This will arrange the email addresses by alphabetical order.
  • Now click only the first email address in the file and click: Format; then Conditional Formatting. A window will pop up. Where it says “cell value is” click the drop-down arrow and select “Formula is”.  There will be a large white bar to enter in codes. Enter in the code:             =COUNTIF(A:A,A2)>1

  • Then click the “Format” button in the window, then click the “Pattern” tab and select the color you would like to highlight the duplicates and click “OK”. Also click “OK” on the first window that popped up. It will look like nothing happened even if you did it right.

  • The first email address should still be selected. With the first email address selected, click the button “Format Painter” (It is a button that looks like a thick paint brush, or broom). Then click the email address below the first one and drag the mouse while it is still clicked down to the last email address on the lists (this should select all email addresses in the file except the first one). Release the mouse on the last email address and it will appear to be doing something for a few seconds.

  • What you should be left with is a list of email addresses in alphabetical order that highlights in a color you selected instances where an email address repeats itself. You can copy and paste the email address from excel into the Salesforce search bar and hopefully get a result that includes 2 records.

  • Identifying duplicates from the excel file.

• Make sure the
  entire list was put into alphabetical order.
• Make sure you selected a color to highlight the duplicate records in the “Conditional Formatting” window. If not, excel will identity duplicates, but won’t point them out through any type of formatting.

• Make sure that before you click "Format" and "Conditional Formatting" that you first selected the FIRST email address which should be in Column A and Row 2.

• If you get records that excel identified as duplicates, but when you search on SF only one record shows up, then make sure when you created the Leads Report that you added the Filter: “
  Converted equals False”