How to create a Report for finding Duplicate Contacts in my instance? - Answers - Salesforce Trailblazer Community
Ask Search:
Unais KomathUnais Komath 

How to create a Report for finding Duplicate Contacts in my instance?

 Hi

How can i find all the Duplicate Contacts in my instance?
My criteria will be based on First Name, Last Name, (EMail or Phone)

Can anyone help?

Thanks

Best Answer chosen by Moderator (salesforce.com) 
Steve MolisSteve Molis
 If you want to us a standard report for this, you could create a custom Formula(Text) field that contactenats the fields you want to use in your duplicate critera.

FirstName + LastName + Email, etc.  

Then create a custom Formula(Number,0) field and enter 1 as the default value.  

Then create a summary report grouped by your custom Text field and Summarize your custom number field, and use conditional highlighting to highligt any group with a count > 1

All Answers

Mark PassovoyMark Passovoy
You will probably want to create a report of all contacts in your Org and then export to Excel. You can use the built in Dupe finder in Excel and de-dupe that way. Then either manually delete/merge what you have found in Salesforce or use a tool like the Data Loader if available.
Steve MolisSteve Molis
 If you want to us a standard report for this, you could create a custom Formula(Text) field that contactenats the fields you want to use in your duplicate critera.

FirstName + LastName + Email, etc.  

Then create a custom Formula(Number,0) field and enter 1 as the default value.  

Then create a summary report grouped by your custom Text field and Summarize your custom number field, and use conditional highlighting to highligt any group with a count > 1
This was selected as the best answer
Mark PassovoyMark Passovoy
@SteveMo, that is a pretty good idea. You always have awesome tricks up your sleeve.
Unais KomathUnais Komath
 Thanks Guys,

Will create a report with formulas and let you know the progress of the same:-)


Unais KomathUnais Komath
 @SteveMo - You are AWESOME!!

Thanks a lot :-)
高長瑞高長瑞
The report will help to find out which record is duplicated, however it can't just show out duplicated records, right? People still needs to work the report in the excel to filter out record count >= 2

Steve MolisSteve Molis
@CRM2.tw   
Yes, you will still have to manually filter the report results in Excel using the Data Sort and Filter tools.  Without a 3rd party Data Hygiene App 
(which is the boat I'm in) you kinda have to make do with what you can build yourself.
Alan BlairAlan Blair
Hi Steve,

Can you advise a bit further on "Then create a custom Formula(Number,0) field and enter 1 as the default value."?

Thanks!

Alan
James RogersJames Rogers
Hi Steve 

I too have also atempted this and have got lost along the way, is there any chance you can explain a bit further please? it sounds really usful an i would love to try it out in my company as we are getting hot on duplicates, sounds just like what i want to impliment before i leave in a few weeks :)

Thanks

Jay
Steve MolisSteve Molis
Hi Guys,
That's a 3 year old question, I don't get alerts when new comments are posted.  Basically the Formula(1) field allows you to get the count of duplicate records.  That's all, it just makes finding them in the Report results a little easier.  
Ravi amerineniRavi amerineni
Hi Steve,

I have am able to create a text formula field i am not able to understand to create a formula(1) field can u suggest me a link or any thing where we can field a step by step procudure 
Steve MolisSteve Molis
Ravi amerineniRavi amerineni
Thanks steve 
Ravi amerineniRavi amerineni
HI steve,

Can u share the screen shot of the final report , if possible 

Steve MolisSteve Molis
I have several posted on the page in the link I sent you.  Is there a specific Report you're looking for?  Can you post a screenshot of the Report and settings you're currently using and a mock-up of what you want?  
Ravi amerineniRavi amerineni
i am looking to find the duplicate contacts in my org

Steve MolisSteve Molis
Hi Ravi,

I think you need to read all the way through the post, your duplicate reporting options within reports are going to be extremely limited (at best). 
Pierre ChungPierre Chung
I found this write-up and method for locating duplicates to be pretty helpful (and easy):

http://www.shellblack.com/data/merging-duplicate-records/
Alejandra MestaAlejandra Mesta
hi steve,
i think i'm close to where i want to get, but might be missing a step or two. this is what i did:

1) custom field formula/text/treat blanks as blanks (under opps) = Name  +  TEXT(CloseDate)  +  TEXT(Amount)
2) custom field formula/number/0/treat blanks as blanks = 1
3) created report, clicked "add", field 2 > 1

the report didn't bring any results, but i do have duplicates.
can you please help me identify what i missed?
thank you!
Steve MolisSteve Molis
Hi Alejandra,
Can you provide more information, and screenshots of your Formula and Report result?
Alejandra MestaAlejandra Mesta
absolutely

screenshot of field 1 called: "dupe field catcher"

Field 1: Dupe Field Catcher

screenshot of field 2 called: "dedup field guide"

Field 2: Dupe Field Guide

screenshot of report: summary, grouped by "dupe field guide", status "any", close date "all time"
Step 3: report


thank you for taking a look at this!
Steve MolisSteve Molis
Hi Alejandra,
You just need to change the Report Summary Grouping, you're grouping it by the Dupe Field Guide (Number) you need to group it by the "Dupe Field Catcher" (Text) field, then SUM the "Dupe Field Guide (Number) within the Report.
 
Alejandra MestaAlejandra Mesta
thank you steve!
John SchatzlerJohn Schatzler
Steve
can you  exaplain me whats does the formula do and what are the benefits from an app like dupe catcher or is the mechanism quite similar

thanks in advance john

 
John SchatzlerJohn Schatzler
Steve
can you  exaplain me whats does the formula do and what are the benefits from an app like dupe catcher or is the mechanism quite similar

thanks in advance john

 
Steve MolisSteve Molis
Hi JohnS, that post is over 4 years old, can you provide more detailed information about what you're trying to do?
John SchatzlerJohn Schatzler
Hello Steve

I do the same like Alejandra has done  with the two dupe Fields and the "power of one" Method which your described and it's marked as best answer.

But I'm not really understand how this works and if i need some other apps like "dupe catcher" or sth. like that.

We have also some Duplicates ( exept that we set duplicates rules when the records will be created)  and try to search a tiny small solution for it.

regards john 



 
Steve MolisSteve Molis
You're MUCH better off with a dedicated De-Duplication App (unless you have a VERY small org)
Ford BenFord Ben
Use this guide to stop duplicates and to mass-merge/convert+merge existing one:
http://www.mass-update.com/2016/08/stop-duplicate-contacts-accounts-and.html 
John SchatzlerJohn Schatzler
Hello again,

Our org is not so small ( 120 employees)   .. but the sales unti is relatively a minor part from it   .. so maybe the approach at that time  isn't so unimationportant as it seems ... so an explaination will be useful ...

regards john 
 
Ford BenFord Ben
Hi John,

To block new duplicates from entering your Salesforce just use the built-in "Duplicate Management" Salesforce-feature, in case you need to mass merge existing records you can use the dedup-manager app.

The steps:
1. Create Duplicate Matching Rules: Setup > Data.com Administration >  Duplicate Management  > Matching Rules
2. Create Duplicate Detection Rules:  Setup > Data.com Administration >  Duplicate Management > Duplicate Rules.
3. To clean all the existing duplicates use dedup-manager (http://www.zaapit.com/page/dedup-manager).

​Ben
 
John SchatzlerJohn Schatzler
Of course are there better solutions nowadays  which are works core in sf .. are welcome

regards john  
Effective Salesforce SolutionsEffective Salesforce Solutions
hi SteveMolis,
I tried this workaround to get a report of duplicate Leads by creating 2 custom formula fields (1 Lead data text field and another power of 1 field) and created a summary report by grouping based on SUM( Lead_text_formula_field). Up to this point it works fine.

However, when I add a filter before running the report  lead_power_of_1 > 1 , it doesn't work and shows me all records including those with rowcount = 1, 2 etc.

Could you give a hint ?
Steve MolisSteve Molis
Effective Salesforce Solutions 

The "Power of One" can't be used in a Report Filter like that (Reports can't be filtered by the result counts) 
Ford BenFord Ben
You can use dedup-manager (the detections is offered for free & support soql):
Dedup-manager is action: https://www.youtube.com/watch?v=bQ-ee2jO0w4
The app: https://appexchange.salesforce.com/listingDetail?listingId=a0N30000000qFzXEAU (https://appexchange.salesforce.com/listingDetail?listingId=a0N3000000B4akOEAR)
The provider: http://www.zaapit.com/page/dedup-manager 

User-added image

 
srikanth kothapallisrikanth kothapalli
Any one help me to create a report in SFDC which shows all the Account records which has Duplicate NPI numbers.
Ford BenFord Ben
Try dedup-manager (custom dedupe). http://www.zaapit.com/page/dedup-manager Custom fuzzy /by formula dedupe: https://www.youtube.com/watch?v=nR0vKnlca7k Ben
srikanth kothapallisrikanth kothapalli
Hi Unais Komath,

How to summrise the fields in reports and please provide the screen shoot for me
 
Ludovic ColletLudovic Collet
Hi all,

I am trying to build a report to show all potential duplicated contacts which have been created last week.

I understand the described method by Steve but the problem is that I am french, and names have accent in my org.
So I am looking for a way to eliminate accents in the custom Formula(Text) field i have to create. 
If anyone have an idea?

Thanks,
Steve MolisSteve Molis
Ludovic C. 
You'd probably have to create a custom Formula(Text) field and use either a TRIM, or a FIND and SUBSTITUE function to "clean" the Names
Ludovic ColletLudovic Collet
Thanks for your help Steve.
 
April ElkinsApril Elkins
Hi Steve,

I know this is an old thread, and you've answered many questions about this. But I know Salesforce has changed its interface and technology a lot in the last few years. Can you please walk through step by step how to build a report that catches duplicates leads sharing the same email/name. We've had issues where an account has different names with the same email address, and we need to flag these instances in an official report. Can you please help me?
Ford BenFord Ben
Hi Steve, 
You may be able to create a dedupe report but I won't be able to mass merge your duplicates.
With the following app you can create a custom dedupe report for free and even run a mass merge on the detected duplicates:
https://www.zaapit.com/page/dedup-manager 
https://appexchange.salesforce.com/listingDetail?listingId=a0N30000009uXwyEAE 
Wesley PritchardWesley Pritchard
@Steve

"If you want to us a standard report for this, you could create a custom Formula(Text) field that contactenats the fields you want to use in your duplicate critera.

FirstName + LastName + Email, etc. "

Struggling with this custom field. What does the formula need to be in the custom field for exactly what you described here? FirstName + LastName + Email 
 
Steve MolisSteve Molis
Hi Wesley Pritchard 

Yeah, something like that.  Keep in mind that post started on May 5, 2011 a LOT has changed since then  
Bradley RiceBradley Rice
Just a quick update in Performance and Unlimited edition orgs you can use Duplicate Jobs to mass identify duplicates based on whatever criteria you choose. https://www.youtube.com/watch?v=ZBgq4kD9NIA
Abhishek KumarAbhishek Kumar
@Steve Molis, Just a quick update, we can add Custom Summary Formula in Report instead of adding Custom Number field in Object as per below snapshot. Custom Summary Formula can be used to Conditional Highlighting.
User-added image
Steve MolisSteve Molis
@Abhishek Kumar  

I don't think that a Report Summary Formula using just RowCount will do the same thing.  Also, as I have said before "that post started on May 5, 2011 a LOT has changed since then" 

For example, you can now Report on Duplicate Datasets in Salesforce  

Documentation Create Reports on Duplicate Records
https://help.salesforce.com/articleView?id=duplicate_management_custom_report_types.htm&type=5
Ford BenFord Ben
You can use the following apps to mass merge your duplciates:

https://appexchange.salesforce.com/listingDetail?listingId=a0N30000000qFzXEAU
https://appexchange.salesforce.com/listingDetail?listingId=a0N30000009uXwyEAE

dedupe manager merge jobs
dedupe manager
Kevin BostonKevin Boston
@Steve Molis
We have created a duplicate records report for the contact records following the link below.  It works great.  However, we have run into an issue that once the records are merged and we run the report again later in the week or month, there is still a single name listed of the merged record. How can we prevent this?

Documentation Create Reports on Duplicate Records
https://help.salesforce.com/articleView?id=duplicate_management_custom_report_types.htm&type=5
Steve MolisSteve Molis
I dunno man, that original post was from 2011 (a lot has changed since then). 

I have not done much with Dup reporting on my own org