How to filter for rows with same absolute values in column?

Hi everyone, how would I go about filtering for the data that have the same absolute value in the Amount column grouped by their Account number? For example, the first two rows would meet the criteria because they have the same account number, and their amounts are the same after we get their absolute value. However, for account 3 it wouldn't meet the criteria because both of the values are 300 before any absolute value conversion, Thanks!

Original:

Account	Amount
1	100
1	-100
1	200
2	200
2	-200
3	300
3	300

Desired result:

Account	Amount
1	100
1	-100
2	200
2	-200

Transformation

Help

Preparation

Accepted answers

ChrisTX

Try the attached workflow. A batch macro would probably be easier, but this could be a good use of the Make Group tool, as long as you eliminate duplicate matches.

I added a few more records to your test input to test for duplicates from the Join.

Chris

How to filter for rows with same absolute values in column.yxmd

All comments

ChrisTX

I thought I understood your question, but your Desired result doesn't seem to match the question.

If you "filter out" data with an opposite sign, for Account 1, wouldn't you be left with only Amount 200 in the Desired result?

And wouldn't you Keep both rows for Account 3, since both amounts are positive?

Chris

overhead_press

My apologies for the confusing wording, I have updated my description so hope that makes it more clearer!

ChrisTX

Even with your updated wording, is your example Desired result still incorrect?

For Account 1, why would the output contain 100 and -100? It seems like those two records would be filtered out, leaving only the 200 Amount.

overhead_press

Yep the desired output is still correct, I am looking for the same absolute values after conversion grouped by their Account number. 100 = ABS(-100). For account number 3 it wouldn't meet the criteria because both of the values are 300 before any conversion is being done.

ChrisTX

Try the attached workflow. A batch macro would probably be easier, but this could be a good use of the Make Group tool, as long as you eliminate duplicate matches.

I added a few more records to your test input to test for duplicates from the Join.

Chris

How to filter for rows with same absolute values in column.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703