Question: I would like to know how to find duplicate invoice records on a sheet? Duplicate invoices based on sale date, vendor invoice, amount and client. I would like to list the original invoice and the duplicates.

identify-duplicate-invoice-records

Answer: In the picture above, sale date, vendor, amount and client are the same for invoice 234 and the duplicates 234-A and 234-B. Invoice 235 is an original and 235-A is the duplicate. Invoice 236 has no duplicates.

Formula in A2:A4, see picture below:

=INDEX(Invoice, SMALL(IF(Paid_amount=0, MATCH(IF(Paid_amount=0, Sale_date&Vendor_invoice&Amount&Client, ""), IF(Paid_amount<>0, Sale_date&Vendor_invoice&Amount&Client, ""), 0), ""), ROW()-1)) + CTRL + SHIFT + ENTER

Formula in B2:

=INDEX(Invoice, SMALL(IF(INDIRECT("'Invoices received'!B"&(MATCH(A2, Invoice, 0)+1))&INDIRECT("'Invoices received'!C"&(MATCH(A2, Invoice, 0)+1))&INDIRECT("'Invoices received'!D"&(MATCH(A2, Invoice, 0)+1))&INDIRECT("'Invoices received'!E"&(MATCH(A2, Invoice, 0)+1))=IF(Paid_amount=0, Sale_date&Vendor_invoice&Amount&Client, ""), ROW(Invoice)-1, ""), COUNTIF(A2:$A$2, A2))) + CTRL + SHIFT + ENTER. Copy the formula down to B4.

Named ranges
Invoice A2:A7
Sale_date B2:B7
Vendor_invoice C2:C7
Amount D2:D7
Client E2:E7
Paid_amount F2:F7
What is named ranges?

identify-duplicate-invoice-records_2

Download excel example file
identify-duplicate-invoice-records.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

ROW(reference) returns the rownumber of a reference

INDIRECT(ref_text,[a1])
Returns the reference specified by a text string

MATCH(lookup_value;lookup_array; [match_type])
Returns the relative position of an item in an array that matches a specified value

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

SMALL(array,k) Returns the k-th smallest row number in this data set.

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

Related posts:

Identify duplicate invoice records using conditional formatting in excel

Excel vba:Select and view invoice

Excel vba: Edit invoice data

Excel vba: Save invoice data

Identify duplicate files in excel