Formula to detect possible duplicate invoice entries

[geh17]

Hello Guys! Hope you can provide some help. I want to detect possible duplicate invoice entries in my database by looking on 4 categories/columns in excel. I have no idea how to start working with the formula so I searched the net and I have found some but it takes forever for excel to finish the calculation. Most of the time, the application crashes and it notifies me that the excel is running out of resources. It seems that this is happening because I'm using the formula on more than 100,000 rows. Please check the attachment if you can modify below formula so the calculations will run much faster.

=IF(SUMPRODUCT((A$2:A$12=A2)*1,(D$2:D$12=D2)*1,(E$2:E$12=E2)*1,--ISNUMBER(SEARCH("*"&G2&"*",G$2:G$12)))>1,"Duplicate","")

Also, I want to improve the formula that can detect or distinguish a slight irregularity on the Invoice No. field.

For example, 11695 & I1695, 120A67 & 12OA67, IN123 & lN123, are not being detected as possible duplicates despite the other 3 categories have the same information.

Any help would be greatly appreciated. Thanks!

Note: I noticed that there is a 1MB limit for the attachment so i just only included few rows.

[JohnTopley]

Try this without test for Invoice number to see if it improves performance

=IF(COUNTIFS($A$2:$A$150000,A2,$D$2:$D$150000,D2,$E$2:$E$150000,E2)>1,"Duplicate","")

Testing "irregularities" in invoice number is difficult because you are likely to want a range of "rules" for matching.

[dflak]

This will get you part of the way there. As John said, you'll need some sort of a "rule book" to convert the invoice numbers to a common base such as all letter "O" will be converted to zero(0), All lower case "l" to the numeral 1, remove trailing alphabetical numbers and so forth.

When you get that working, then you can add invoice number to the composite.

[geh17]

Thanks John & dflak!

I have tried the formula and it is a lot faster now but the problem is, it has detected a lot of false positive duplicates. I have tested it on 80,000 rows and more than 10,000 were detected as dup.

I understand that the irregularities on invoice reference is difficult to implement and requires more complex formula but if we can integrate the previous formula =SUMPRODUCT(--ISNUMBER(SEARCH("*"&G2&"*",G$2:G$12)))) to check for the invoice nos. on the countifs function will greatly lessen the detected false positives. I have tried to insert this but it seems that it is not working on countifs.

[geh17]

Hi Guys,

I'm thinking of an alternative formula to detect the irregularity on the invoice nos. still using the sumproduct function.

I will temporarily use this for now since I'm working on a smaller database but if this can be improve to a faster formula, this will be greatly appreciated.

As an addition to detect the string that may be added on the start and end of the invoice, this will search for the first 3 digits starting on the 2nd character from the left or 3 digits starting on the 2nd to the last character starting from the right. of the invoice reference to capture those errors on spelling.

Please see the attached file as I'm clueless how to insert properly the added steps from the original formula and I'm getting a #VALUE! error.

[MarvinP]

Hey gehI7,

See if using two helper columns makes your answer faster. See the attached. I've substituted Oh with zero and Eye with One before creating the helper column.
Substitute for Typos and Countif Helper.xlsx

[geh17]

Hi Marvin,

Many thanks for the provided solution.

The spelling mistakes are not fixed to O and I. These can be used interchangeably depending on the characters on the invoice reference. The formula should be flexible that may capture the spelling error on the first, middle, and last character of the invoice reference. I understand that this may not detect the exact duplicate match but an approximate one will do.

Please see my comment on my post#5 above as well as the formula on the attached file to give you an insight on what I am trying to accomplish. I'm getting a result when I only use one criteria when using search but when I insert OR, I'm getting a #VALUE! error. It will be a big help if this can be rectified or if the conditions can be converted to countifs function and any other solutions similar to this.

[JeteMc]

As this thread has not received additional replies in a couple of days I thought that the following might be appropriate. Looking at the file attached to post #5, I'd suggest using only the first part of the formula in column H as in:

Please Login or Register  to view this content.

This would identify where the Vendor, Document Date and Amount are the same. From there you might be able to find those invoice numbers that are closely related using the Microsoft Excel add-on "Fuzzy Lookup".
Note: I have never used the Fuzzy Lookup add on, however I found it referenced in a thread, on another forum site, that seemed to be related.
I hope that this is helpful.