In a previous article Identify duplicate invoice records in excel I created a list of the original invoices and their duplicates. This article shows you how to quickly highlight duplicate invoice records.
Here is a list of invoices and some duplicates. I use sale date, vendor invoice, amount and client to identify duplicate invoices. In column "Paid amount", duplicates have zero as value.
To highlight duplicates I use this conditional formatting formula:
=IF($F2=0, ISNUMBER(MATCH($B2&$C2&$D2&$E2, (Sale_date&Vendor_invoice&Amount&Client), 0)), FALSE)
What is named ranges?
Here is a picture of highlighted duplicate invoices.
How to apply the conditional formatting formula in excel 2007:
- Select the range (A2:F7)
- Click "Home" tab on the ribbon
- Click "Conditional formatting"
- Click "New rule..."
- Click "Use a formula to determine which cells to format"
- Click "Format values where this formual is true" window.
- Type =IF($F2=0, ISNUMBER(MATCH($B2&$C2&$D2&$E2, (Sale_date&Vendor_invoice&Amount&Client), 0)), FALSE)
- Click Format button
- Click "Fill" tab
- Select a color
- Click OK!
- Click OK!
Download excel example file
(Excel 97-2003 Workbook *.xls)
Functions in this article:
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE
Checks whether a value is a number and returns TRUE or FALSE
Returns the relative position of an item in an array that matches a specified value