Duplicate Payments

Duplicate records can crop up in vendor tables, customer master lists, parts lists, etc. due to very minor differences in spelling or extraneous characters. One technique used to identify such duplicates is the use of “fuzzy” matching. This process uses various procedures to transform the values existing in the table into a more basic format and then make the comparison between all possible pairs using this basic format.

As a simple example, suppose that there are two vendor names set up as “ABC, Inc.” and “ABC Incorporated”. These names refer to the same vendor, but the spellings are not identical. A simple fuzzy match routine might remove “noise” words such as “Inc.”, “Incorporated”, “Corporation”, etc., then remove any extraneous characters such as punctuation, change all lower case letters to upper case letters and finally compare the results. In this example, the process would transform the first vendor name to “ABC” and also transform the second vendor name to the same value, “ABC”. Obviously when these values (“ABC”) are compared, there is a perfect match.

The script here is in pdf format for viewing.  The text can also be downloaded as TestDupCheck.txt and then renamed to TestDupCheck.php in order to be executed.  The purpose of the script is to test prospective payments against payments which have already been made in order to determine if any of the prospective payments may have already been paid, in part or in whole.  The script requires two data sources (input tables) - one which contains the historical payment information and the other which contains  the prospective payments.   The script is written in the PHP language and can be used to analyze data contained in a MySQL database.  Maintenance and use of the script is done primarily by "fill in the blank" for variable names which are all located at the start of the script.

Questions, comments and suggestions may be directed to Mike.Blakley@ezrstats.com.

Document addresses the use of procedures and scripts to test prospective payments on an automated basis.  The script can be viewed here and also downloaded. The name of the text file is TestDupCheck.txt.  In order to use the script, the suffix would need to be changed to "php", i.e. the filename should be TestDupCheck.php.

This is a systematic approach that applies both “fuzzy” and “exact” match logic to all reasonable combinations of the four key elements of paid invoices –

• Vendor number
• Invoice number
• Invoice amount
• Invoice Date

Often the key underlying reason for duplicate payments is that duplicate vendors have been established. Thus, the first step in the analysis is to identify any instances of duplicate vendors, in order that they will be considered as one, for purpose of searching for duplicate invoice payments.

Tests for duplicate records are commonly performed in many audits.  Examples include searches for duplicate invoice payments, duplicate receiving reports, duplicate employees, duplicate vendors, etc.  The process to identify potential duplicates can be performed very efficiently and effectively, even with very large data volumes.  This article explains the detail procedures.