Loop that finds same amount but with inversed sign

Question

Hi all,

I have the following table

AB405IIA1232IIA1233IIA999-405IIA1233IIA2223IIA1236IIA9997IIA999-6IIA9999IIA2226IIA999

and I want to create a loop that if an amount in column A is positive, for example 405 and it finds the same amount but negative and with the same value as column B it gets deleted from the data, and vice versa, i.e.: if an amount is negative it needs to find the same amount but positive and with the same value as B and delete both lines, so the table would look like this:

AB  2IIA1233IIA999  3IIA2223IIA123  7IIA999  9IIA2226IIA999

Please note that I need to create a loop, and not use the Summarize tool.

Initially, I tried using a Multi-Row Formula:

IF [B] = [Row+1:B] && [A] + [Row+1:A] = 0 THEN 'Y'

ELSEIF [B] = [Row-1:B] && [A] + [Row-1:A] = 0 THEN 'Y'

ELSE 'N' ENDIF

Then I would filter by Equals 'N' to delete the 0 values.

That would have worked if the values were in order, like the table below:

AB405IIA123-405IIA1232IIA1233IIA9993IIA2223IIA1236IIA999-6IIA9997IIA9999IIA2226IIA999

but unfortunately, they are not. I would like to use a similar approach.

Thanks in advance,

Rafa

binu_acs · Answer

@rafatomillero One way of doing this

Filter_1110.yxmd

apathetichell · Answer

self-joins

basically create an offsetting field. Join the the data source twice (on the original field and the offsetting field). Look at what entries are from the right (or the left -but not the Join) anchor. Those are your unmatched entries.