I'm not sure if this is the right stack exchange site for this, or maybe this is more of a reddit question, but don't rake me over the coals for asking. Happy to go post on another corner of the internet if need be.
I'm wondering what would be the best approach to identifying duplicate transactions within an accounting system. If every transaction has a date, a vendor name, and an amount. I think any 2 of the 3 data points could indicate a possible duplicate, but probably relying on that approach would produce a high number of false positives.
My thinking currently would be the dollar amount and vendor would have to match, and the date would have to be within the same month? But also it seems like checking for a matching amount and date, but mismatched vendor might indicate a reasonable probability of being a duplicate.
Has anyone done this type of thing before and what criteria did you use that proved successful?
The purpose of identifying the duplicates would be to have an end user manually review them to determine if they should be deleted or if they're legitimate transactions.
My concern is the end user may get frustrated with reviewing a bunch of false positives, so I want to try and find the sweet spot where there aren't a ton of false positives OR negatives.
I could potentially use the invoice/reference number, so that's another possible point of comparison.
In other matching situations, I take 2 invoice numbers and "clean" them, by exchanging any Os for 0s and Is or ls for 1s, trimming white space, and then comparing the resulting value. I could potentially employ the same logic here, but that's proven problematic at times mainly because one invoice number could have a 3 digit prefix on one side and the other doesn't have the prefix. Same with a suffix. I'm sure people way smarter than me have figured out some pretty elegant and sophisticated ways to do this type of thing.
Again, trying to find that sweet spot is what I'm after.