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:
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
Highlight duplicate records
Identify duplicate invoice records
Highlight duplicates where an adjacent column is in a date interval using conditional formatting in excel
Prevent duplicates using dynamic conditional formatting in excel
How to highlight duplicate values
Identify duplicate files in excel
Save invoice data [VBA]
Invoice template with dependent drop down lists
Populate listbox with unique invoice numbers [VBA]
Select and view invoice [VBA]
Edit invoice data [VBA]
Basic invoice template
Sum unique distinct invoices
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.
How do you identify duplicate records? Are they identical in column A, B, C, D, E and F? (See picture above)
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.
How to add a formula to your comment:
Remember to convert less than and larger than signs to html character entities before you post your comment.
How to add VBA code to your comment:
[vb 1="vbnet" language=","]
How to add a picture to your comment:
Upload picture to postimage.org
Add picture link to comment.
You can contact me through this webpage
Mail (will not be published) (required)
Notify me of followup comments via e-mail
VBA Knowledge Base
User Defined Functions
Advanced Excel Course
Posts in category