Identifying Invoices that net zero

[Gr4c3Sult]

Hi All,

I need to identify all invoices that Net zero and was wondering if any of you had an idea on how to get this done in Alteryx.   I tried trimming CR and then sorting by invoice number, but that doesn't identify when they net zero.  There are over 500K+ rows of data, so eyeballing it would be awful.

An example:

CSPIN00602461  1000.00

CSPIN00602461CR -1000.00

Any help or suggestions is greatly appreciated.

Thanks!

Grace

[atcodedog05]

Hi @Gr4c3Sult 

Bit confused what your input data looks like. Guessing its table with 2 columns (order and amt). You can use summarize tool to get sum and add it back to main data.

Workflow:

1. Using formula to Trim "CR"

2. Using summarize groupby order, sum of amt to get net.

3. Using find and replace tool to add net column to main data.

If this is not as expected. Please provide sample input and expected output in a sample file.

Hope this helps : )

[Gr4c3Sult]

Forgot to add that I also need to confirm the vendors are the same.

Vendor     Invoice                     Amount

12345      CSPIN00602461        1000.00

12345      CSPIN00602461CR -1000.00

[atcodedog05 · Accepted solution]

Hi @Gr4c3Sult 

Just add vendor also in summarize groupby and they will also be considered.

Workflow:

Hope this helps : )

[christine_assaad]

Hi @Gr4c3Sult 

I took an assumption that both the invoice number and amount will be in the same cell. You can modify this if need be. 

Using Fuzzy Match with Make Groups you can achieve your end goal. Please see below. I added another account to test the logic. It seems to work for the invoices you provided.

Hope it helps. Cheers!

[Gr4c3Sult]

Thank you!  That works 🙂  Do you have any ideas on how to identify Invoices with different invoice numbers but Net zero?

Thanks Grace

[Gr4c3Sult]

@christine_assaad   Thank you for your help.  I ran this with my original data set and there are so many invoice numbers that are similar that the match returned results were enormous and hard to find the net zeros.  

The above solution works for most of the net zeros.  

Thank you again for your time and help 🙂