Loop that finds same amount but with inversed sign

[rafatomillero]

Hi all,

I have the following table

A	B
405	IIA123
2	IIA123
3	IIA999
-405	IIA123
3	IIA222
3	IIA123
6	IIA999
7	IIA999
-6	IIA999
9	IIA222
6	IIA999

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:

A	B
2	IIA123
3	IIA999
3	IIA222
3	IIA123
7	IIA999
9	IIA222
6	IIA999

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:

A	B
405	IIA123
-405	IIA123
2	IIA123
3	IIA999
3	IIA222
3	IIA123
6	IIA999
-6	IIA999
7	IIA999
9	IIA222
6	IIA999

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

Thanks in advance,

Rafa

[apathetichell]

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.

[binuacs]

@rafatomillero One way of doing this