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

Share and Enjoy:
  • Share/Bookmark

Related posts:

  1. Identify duplicate invoice records using conditional formatting in excel
  2. Identify missing values in a column using excel formula
  3. Identify missing values in two columns using excel formula
  4. Filter duplicate text values in a range using “begins with” criterion in excel
  5. Return unique and duplicate numerical data entries from multiple sheets (3D range) in excel
  6. Filter duplicate rows and sort by date using array formula in excel
  7. Filter duplicate values in a range using “contain” condition in excel
  8. Extract duplicate text values from a range containing both numerical and text values in excel
  9. Highlight duplicate values in a range using conditional formatting in excel
  10. Identify largest text value in a column using array formula in excel