Remove Offset entries in data set, negative and positive number

[MiroWEX]

I have a dataset with offsetting entries (e.g., -2400 and 2400), and I’d like to exclude them based on the Description, Entity Acct, and File Name. The data will be unsorted, and the +/- base amounts may appear in random rows.

In this case, I would only like to keep Description CC rows. Appreciate any help!

Description	Entity Acct	FileName	Base Amount
AA	54545	CCC	150
AA	54545	CCC	-150
BB	45444	ddd	180
BB	45444	ddd	-180
CC	52111	ffffff	2456
CC	52111	serfd	2365

[binuacs]

@MiroWEX one way of doing this with the join tool

[Luke_C]

Hi @MiroWEX 

You might try grouping the records and removing the ones that net to 0:

[MiroWEX]

Thank you both for your help! Since I have a large dataset, both of these solutions aren't providing the correct results (your solutions are great but mainly because I did not notice some nuisances with the data).

Binuacs, I am reviewing your workflow but since i have 40000+ rows the join tool is returning 4+ million rows... I think the issue is that I have many of the same Descriptions, Entity Acct, and File name records where the base amount is the the only difference. 

Luke, for your workflow, I missed the fact that my data has some entries share the same Description, Entity Acct, and File Name but have different amounts that are not offsetting. As a result, the Summarize tool doesn't bring the total to zero.

Example for 

Description	Entity Acct	FileName	Base Amount
AA	54545	CCC	150
AA	54545	CCC	-150
AA	54545	CCC	1300
BB	45444	ddd	180
BB	45444	ddd	-180
BB	45444	ddd	300
CC	52111	serfd	2365
CC	52111	ffffff	2456

so the results would look like this 

AA	54545	CCC	1300
BB	45444	ddd	300
CC	52111	serfd	2365
CC	52111	ffffff	2456

[Luke_C]

Hi @MiroWEX 

What would your expected outcome be in that scenario, then? I think it would still effectively remove the netting records. 

[CoG · Accepted solution]

Hopefully this matches what you're looking for. This works by identifying matching pairs of records with positive and negative base amounts and removing those matching pairs:

Hope this helps and Happy Solving!

[Ritesh_Parmar]

Hi, you can can try this, its simple.

[MiroWEX]

Thanks everyone! CoGs solution worked perfectly!