Finding Duplicate Opportunities in Salesforce (NPSP)

During our data import process, duplicate opportunities happened. This is largely the result of running two separate systems live (a story for another time). This shouldn’t happen in the future and isn’t something we’ll need to really automate anything else around. So I’m taking the path of least resistance. I’m sure there are apex methods and classes for this but I couldn’t find one that seemed to work for me.

Salesforce has a great merge tool for accounts and contacts. There are none for Opportunities. New Formula –> Text field on opportunity. Call it UniquenessTest or whatever you like.

Create a formula that will update this field with the data you’d like to test uniqueness against. In this case it’s Account ID, Amount, and CloseDate.

If the same amount of an opportunity on the same close date on the same account happened, it’s likely a duplicate for this use case.

Field Formula
AccountId &  TEXT(Amount) & TEXT(DATETIMEVALUE(CloseDate))

Group by this field in a report and you’ve got a way to find duplicates with very little fuss – group by the power of one (if you don’t know about it, read about it) – – the creator has my eternal gratitude.