Identify duplicate invoice records using conditional formatting in excel
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)
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?
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
identify-duplicate-invoice-records-using-conditional-formatting.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
ISNUMBER(value)
Checks whether a value is a number and returns TRUE or FALSE
MATCH(lookup_value;lookup_array; [match_type])
Returns the relative position of an item in an array that matches a specified value
Related posts:
- Identify duplicate invoice records in excel
- Highlight smallest duplicate value in a column using conditional formatting in excel
- Highlight duplicate values in a range using conditional formatting in excel
- Highlight duplicates using conditional formatting in excel
- Highlight the second or more duplicates in two lists using conditional formatting in excel
- Highlight dates within a date range using conditional formatting
- Highlight duplicates where adjacent cell value meets criteria using conditional formatting in excel
- Prevent duplicates using dynamic conditional formatting in excel
- Highlight unique values and unique distinct values in a range using conditional formatting in excel
- Highlight duplicates where an adjacent column is in a date interval using conditional formatting in excel
August 11th, 2009 at 11:40 pm
So close. Go into your table of data and change all the dates, vendors, amounts, and clients around and you'll notice that the conditional format will not notice that the records are different. Every isnumber of your matches will come back true, so the condition is met. The match will return a reference to the first row that matches it, so even if it is only in the data once and it's the 13th record, 13 will be returned to the function which will make the isnumber function return true. The only thing that is making your cells not be gray is that they don't meet the first condition of your if funtion... their paid amount doesn't equal 0. In your scenario though, those fields most likely wouldn't ever change. In the spreadsheet I'm working with, my data could be different.
I am thoroughly bummed about this though because I really would love to find the functionality that this tutorial proclaims to have but doesn't. By the way, as I've been searching for this solution for the past couple hours, yours has been the easiest tutorial to follow.
If you do find a way to accomplish this task without macros, please let me know.
August 12th, 2009 at 12:19 pm
How do you identify duplicate records? Are they identical in column A, B, C, D, E and F? (See picture above)
August 12th, 2009 at 9:19 pm
My scenario is tracking employees who complete online training. I capture their name, id, class taken, and date. Any employee is free to take a plethora of different courses and they could take them all on the same day if they want. What I am trying to do is see if any employee has taken any course more than once in any given day. I will count a completion of a course multiple times for any employee as long as it isn't completed in the same day.
I actually came up with a solution using what I learned from this tutorial and another one of yours. I am creating a cell that has all the fields concatenated and then searching for multiple copies of that value in the field. Then I'll do the conditional format based off of that. What I was wanting to do was requiring array formulas which didn't work in the conditional format. By splitting it out this way, I didn't have to use array formulas and it still works.