Identify invoices with same gross amount and similar invoice numbers

[byagelski]

Hello - I'm trying out an audit use case to identify potential duplicate paid invoices.  The pattern I'm looking for would be the same vendor name, same gross amount, similar invoice #.  Specifically with the invoice number, I sometimes see invoice numbers adjusted with 'A', 'B', or '-1' entered at the end of the invoice.  Thus invoice # 1234 would also show as 1234A or 1234-1.  My initial thought was to try and verify that for any vendor and gross amounts that match, compare the invoice number with "contains", thus 1234 is contained within 1234A and 1234-1. 

I am trying the WildCard Join macro available within the Community, but what I'm finding is that pushing a lot of data through that is quite time consuming for the workflow. 

Anyone have a different solution?

Here is some sample data

Vendor       Inv Date     Invoice #     Gross Amount

Abbot        2019-01-01   111             2000
Abbot        2019-01-01   111A            2000
Abbot        2019-01-02   111B            2500
Abbot        2019-01-01   1234            2000
Reynolds  2019-01-01    111               2000
Carrie       2019-02-02    7777            1500
Carrie       2019-02-02    7777-A         1500
Carrie       2019-02-03    7777-1         1500

[joshuaburkhow]

I just built this batch macro this morning that will do the append but on a row my row basis (therefore becoming more performant ;)). This might help you or at the least give you an idea of how to approach. The main idea here is to join the same table to each other and match based on your needs. 

[JosephSerpis · Accepted solution]

Hi @byagelski I mocked up a workflow that flags potential duplicates. Let me know what you think?

[byagelski · Accepted solution]

Pretty slick, thank you so much!  I've been a little shy to take on Reg Ex.  Looks like it identifies as a duplicate even if I put A in front of the invoice number.  Any recommendations on sites for learning more about the Reg Ex syntax and formulas?

[JosephSerpis]

Hi @byagelski Alteryx have some interactive lesson using Regex I would recommend.

https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons?lessons=all