Identifying Invoices that net zero

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

Help

Fuzzy Match

Preparation

Accepted answers

atcodedog05

Hi @Gr4c3Sult

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

Workflow:

Hope this helps : )

net.yxmd

All comments

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 : )

net.yxmd

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

Hi @Gr4c3Sult

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

Workflow:

Hope this helps : )

net.yxmd

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!

FuzzyMatching_07.23.21.yxmd

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 🙂

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703