VBA Tool to Identify Duplicate Invoices


Add To Cart

In the world of business, everyone is working hard to increase their profits and reduce revenue leakage. Many times, we have heard about duplicate payments made to the vendors. Sometime these amounts are in thousands and millions of dollars which is a huge risk to business.

While all the new age ERP systems comes with auto detection of duplicates which helps to avoid most of the duplicate payments; still duplicate payments get processed and paid to the vendors. If you try to find the trend of duplicates, you will notice that duplicates get processed because they did not meet the auto detection rules and policies of the ERPs.

See below an example of a duplicate:
Duplicate Invoice Identifier
In the above example you will notice that duplicate invoice was processed with few changes like:
  • Invoice number was appended with extra characters ‘4FEB’
  • Invoice Date is also changed from 4th Feb to 5th Feb
  • Invoice was processed against a similar vendor name ‘Tagfeed Ltd’
  • Also, amount is rounded off to $6522
To identify such duplicate invoices, there is a strong need of an external solution. ExcelSirJi is proud to announce the launch of ‘Duplicate Invoice Identifier’ tool which is a full proof solution to identify duplicates.
Duplicate Invoice Identifier
This tool is developed on MS Access and helps you to identify duplicates. It has following features:
  1. Support two datasets (Current and Historic) where each record of current dataset is compared with all records in historic dataset
    Duplicate Invoice Identifier
  2. Allows two ways to import data in the tool
    Manual Copy and Paste
    Duplicate Invoice Identifier
    Import from Excel file
    Duplicate Invoice Identifier
  3. It has got user friendly options to define condition for duplicates Duplicate Invoice Identifier
  4. The tool supports 25 types of match conditions Duplicate Invoice Identifier
  5. You can also define formatting conditions to first format the data before checking for duplicates Duplicate Invoice Identifier
  • MS Access 2007 or above version
  • MS Excel 2007 or above version
  • Windows 7 or above operating system
Follow the below steps to use this tool:

  • Open the tool in MS Access 2007 or above version
  • You may see a warning message on top because the file contains VBA Codes, click on Enable Content
    Duplicate Invoice Identifier
  • Double click on ‘Home’ form to open the tool
    Duplicate Invoice Identifier
  • You will see a blank form opened like below
    Duplicate Invoice Identifier
  • To use this tool for analysis, you need two datasets in Excel files.

Data 1 (Current Data): This is the data in which you want to identify duplicates
Data 2 (Historic Data): This is the data from which you want to compare current data to identify duplicates
Points to Note:

  1. Both Current and Historic dataset should be in same format like sequence of columns
  2. You can import only 10 columns from the dataset in the tool
  3. Tool can read only 255 characters of each cell

See below a sample dataset: Duplicate Invoice Identifier

  • To import data in the tool, click on ‘Manage Data’ button
    Duplicate Invoice Identifier
  • There are two ways you can import data in the tool

Option 1 (Manual Copy Paste): You can simply copy your data (without headers) from any Excel file and paste in the tool
Step 1: Copy data from Excel file
Duplicate Invoice Identifier
Step 2: Select the appropriate tab (Current or Historic) to paste the data and click on the top left section of the datasheet
Duplicate Invoice Identifier
Step 3: Press Ctrl+V to paste the data and click on ‘Yes’ button to confirm the actionDuplicate Invoice Identifier

Option 2 (Import from Excel):You can use import functionality in the tool to browse an Excel file and import data
Step 1: Click on ‘Import from Excel File’ button
Duplicate Invoice Identifier
Step 2: Read the instructions on the form and select the dataset to be imported
Duplicate Invoice Identifier
Step 3: Browse the Excel file you want to import
Duplicate Invoice Identifier
Step 4: Click on ‘Import Data’ button
Duplicate Invoice Identifier

  • Once the data is imported, you need to define datatype of each column. By default, each column is considered as text, you need to explicitly change the datatype. It is an important step because you can define few conditions on specific datatype only. To define the datatype, select the right option for each column

Duplicate Invoice Identifier

  • Now it’s time to configure the tool to identify duplicates. Since there are different ways a duplicate invoice can be processed; hence this tool comes with fully configurable conditions to catch the duplicate.
    For an example, let’s say that you want to identify duplicates where Invoice numbers looks similar, invoice date is +- 5 days, Vendor name is similar, Amount is +-1 dollars and Customer Name is same. Have a look at the below screenshot of such duplicate:

Duplicate Invoice Identifier

  • This tool comes with 25 types of matches, have a look at below table which can help you to decide the right match type to be selected.

Duplicate Invoice Identifier

  • Let’s start with configuring the tool to identify duplicates, first we will define Invoice Number condition as ‘Character Match [>70%]’. You can also choose other character match options depends on how much variation you are expecting in the data. As you decrease the character match percentage, you are expected to get more duplicates

Duplicate Invoice Identifier

  • In some cases where you want to remove special characters such as !@#$%^&*() before comparing the data, you can use Formatting option

Duplicate Invoice Identifier

  • For Text datatype, you can use ‘Remove Special Characters’ formatting option. For Number datatype, you can use ‘Remove decimal values’ and ‘Convert number to absolute’ options

Duplicate Invoice Identifier
Duplicate Invoice Identifier

  • Now we will define condition for Invoice Date as below. You can also choose other options as appropriate

Duplicate Invoice Identifier

  • We will define condition for Vendor Name as ‘Left Match [>60%]’

Duplicate Invoice Identifier

  • Next is Amount condition, for this we will define the condition as ‘Amount [+-1]’

Duplicate Invoice Identifier

  • The last condition we will define for Customer Name as ‘Exact Match’

Duplicate Invoice Identifier

  • Done, let’s click on ‘Analyze Data’ button and see the result.

Duplicate Invoice Identifier
Note that if you want to stop the analysis in between then you can click on the same button again. Also, you can see the progress on the bottom progress bar and percentage label
Duplicate Invoice Identifier

  • Once Analysis are completed, you will see a confirmation message box along with number of duplicates found. Click on ‘OK’ button to proceed.

  • To view the results in Excel file, click on ‘Export Report to Excel’ button

  • Report will be divided in two sections:

Section 1 – Current Data: These are the records from Current Data which are found as duplicate when comparing with Historic Data. You can identify them from Column A (Record Type) as ‘Current Data’. Also, these records will be marked in Orange color for easy identification
Section 2 – Historic Data: These are the matching records from Historic Data based on which duplicates have been identified in Current Data. You can identify them from Column A (Record Type) as ‘Historic Data’.
Duplicate Invoice Identifier

  • Let’s have a look at a report with few more records

Duplicate Invoice Identifier

In the above screenshot, you can notice that there are 5 duplicates found in Current Data and there are 7 matching records from Historic Data. Each match has been given a Match Number which you can see in column B (MatchNumber). So, if you want to have a look at matching records of first duplicate then you can apply filter in Colum B as 1Duplicate Invoice Identifier

Similarly, to look at matching records of fifth duplicate then apply filter in Column B as 5
Duplicate Invoice Identifier

  • Great news, now you are ready to use the tool and save your business from duplicate payments.

Please drop an email at excelsirji@gmail.com for any support. We will revert you in 24 hours

VBA Tips you Must Know – Part 1
VBA TRICKS
Excel VBA Tips you Must Know – Part 1

Here we are coming with one more exciting post which can help you to solve very basic but very important problems while writing VBA codes.

How to use Excel Workday Formula in Excel
DATA ANALYSIS TRICKS
EXCEL FUNCTION – WORKDAY

Are you working today? or Do you have Work Off or holiday today?  I am asking this question because I am gonna tell you the most commonly used function in Excel “Workday”. “Workday” as name suggest that it brings the day when you will be working or you are expecting …

Subtotal Function Excel
DATA ANALYSIS TRICKS
Excel Function – SUBTOTAL

Excel Function “Subtotal” is most commonly used formula in excel and it can make your work easy while performing simple mathematical calculations because of variety of features like: – You can use this function for different purposes instead of multiple functions i.e. SUM, AVERAGE, COUNT, MAX, MIN, Standard Deviation etc. …

error

Like the Article? Please spread the word :)

Facebook
Facebook
YouTube
YouTube
Pinterest
Pinterest
LinkedIn