How to Identify Duplicate Invoice using Spreadsheet ?

Identifying Duplicate Invoices

Duplicate invoices have been a pain to many organisations and they are most of the times not deliberate. It is important to understand the users and their access to understand if the duplication could have been a deliberate attempt to swindle cash from the company. It is difficult to identify duplicate invoices just by reading the data, in this article we discuss simple techniques to find duplicates.

The easiest way to detect duplicates in Excel is using the COUNTIF function. Depending on whether you want to find duplicate values with or without first occurrences, there’s going to be a slight variation in the formula as shown in the following examples.

COUNTIF Method

Here’s a formula to find duplicates in Excel including first occurences Countif(B:B, B13)>1

Conditional Formatting Method

• Select the entire your data from which you want to highlight duplicate values.
• Go to Home Tab -> Styles -> Conditional Formatting -> Highlight Cells Rules -> Duplicate Values.
• Here, you’ll get a pop-up option to select a color to highlight values.
• Select the color, you want to use. Click OK.

Output:

Using Concatenate & IF Function

Formula for Concatenate –> =concat(“A,B,C,D”)

Concatenate has to be done for 5 different scenarios –

Output:IF formula: =IF(COUNTIF($Range, $Cell)>1, “Duplicate”, “Unique”)

After identifying these duplicates, it is important to get the hard copies of these invoices to be sure that these are in fact duplicates.

What we saw was one of the simplest methods to identifying duplicates and this could come in handy when you’re dealing with limited amount of data (usually the case in small and medium sized companies). Large companies on the other hand deal with huge number of transactions on daily basis and they have inbuilt preventive logical controls (dedupe) for them to identify these duplicates at the time of entry, however they are usually not that accurate.

While auditing in large organisations, there is a need to cleanse the data first before processing it with formulas to understand if there could be any potential duplicates in the invoices submitted. It is key to understand in such scenarios the people handling & approving the transactions in the payable’s module. We will discuss this in depth in the next article. Thanks for reading!

Download the worksheet here if you wish to get your hands on the formula to understand better >> Duplicate Invoice WorkSheet