Remove Offset entries in data set, negative and positive number

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

Datasets

Accepted answers

CoG

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!

Sample Workflow.yxmd

All comments

binu_acs

@MiroWEX one way of doing this with the join tool

exclude_negative_amt_04022025.yxmd

Luke_C

Hi @MiroWEX

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

New Workflow3.yxmd

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

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!

Sample Workflow.yxmd

Ritesh_Parmar

Hi, you can can try this, its simple.

Test1.yxmd

MiroWEX

Thanks everyone! CoGs solution worked perfectly!

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703